Dittofi
  • Tutorials
    • Tutorial Index
    • Quick Start
    • Dittofi Essentials
      • Part 1: Frontend terms and concepts
      • Part 2: Backend terms and concepts
      • Part 3: Connecting frontend and backend
      • Part 4: Dittofi app structure
    • Dittofi App Design
      • 🏗️App architecture 101
      • ◼️Backend app development 101
      • 🟪Frontend app development 101
      • 🔰(optional) Web developer beginner series
  • Learn Frontend
    • 👨‍💻Getting Started
      • Welcome to Dittofi
      • Intro to the Dittofi Design Studio (Frontend)
    • 🟩Pages
    • 🟪Elements
      • Types
        • Button Element
        • Div Block Element
        • Form Block Element
        • Link Block Element
        • List Element
        • Modal Box Element
        • Tabs Element
        • Columns Element
        • Image Element
      • Partials
      • Prefabs
      • Custom
    • 🟧Variables
      • Array
      • Checkbox
      • Collection
      • Collection (Custom Model)
      • Collection (System Custom Model)
      • Custom Model
      • Date
      • Datetime
      • File
      • Json
      • Number
      • Number with decimal
      • Model
      • System Custom Model
      • Text
      • UUID
    • 🟨Events
      • 🟨External
        • HTTP Request
        • Run Endpoint
      • 🟨Flow Control
        • Condition
        • Run Action
        • Loop
        • Sleep
      • 🟨Maths
        • Add
        • Divide
        • Modulus
        • Multiply
        • Subtract
      • 🟨Navigation
        • Open Page
      • 🟨Notifications
        • Alert
        • Confirm
      • 🟨Other
        • Copy To Clipboard
        • Run Custom
      • 🟨Text
        • Concat
        • Contains
        • Ends With
        • I Contains
        • I Ends With
        • I Starts With
        • LTrim
        • RTrim
        • Text To Upper
        • Text To Lower
        • Trim
        • Starts With
      • 🟨Variables
        • Set Field
        • Remove Field
      • Validate
    • 🟦CSS Layouts
      • Holy Grail Layout
      • Side Bar Layout
      • Sticky Footer Layout
      • Sticky Header Layout
      • CSS Frameworks
    • 🔲CSS Frameworks
    • Component Libraries
    • Meta Attributes
  • Learn Backend
    • 🖥️Getting Started
      • Intro to Data
      • Intro to APIs
      • Build your first custom API
      • Actions & Events
    • 🟫Back-End Actions
    • 🟩Custom Models
      • 🟩Attributes
        • Array
        • Checkbox
        • Custom Model
        • Collection (Custom Model)
        • Collection (System Custom Model)
        • Date
        • Datetime
        • File
        • Number
        • Number With Decimal
        • Json
        • Text
    • ◼️Data Models
      • Database Fundamentals
        • Database relationships
      • ◼️Columns
        • Attachment
        • Auto Increment
        • Checkbox
        • Date
        • Datetime
        • Duration
        • Email
        • Long text
        • Link to another record field
        • Number
        • Number with decimal
        • Password
        • Phone number
        • Percent
        • Price
        • Rating
        • Single select
        • Single line text
        • URL
    • 🟥Events
      • 🟦Arrays
        • Append
        • Array Pop
        • At
        • Length
        • Prepend
        • Range
      • 🟦Conversions
        • Base64 Encode
        • Base64 Decode
        • Json Decode
        • Json Encode
        • Path Escape
        • Path Unescape
        • Query Escape
        • Query String Parse
        • Query Unescape
        • To Bool
        • To Float
        • To Int
        • To Str
        • Yaml Encode
        • Yaml Decode
      • 🟦Database Requests
        • Create
        • Create Or Update
        • Delete
        • Has Record
        • Read Event
        • Update
      • 🟦External
        • HTTP Request
        • SFTP Download
        • SFTP Upload
      • 🟦Files
        • CSV Reader Event
        • CSV Writer Event
        • File Read
        • File Save
        • File Size
        • File Type
        • JSON Reader
        • PDF Generator
      • 🟦Flow Control
        • Condition
        • Loop
        • Return
        • Run Action
        • Sleep
      • 🟦Notifications
        • Send Mail
        • Connect Websocket
        • Message Websocket
      • 🟦Maths
        • Add
        • Acos
        • Acosh
        • Asin
        • Asinh
        • Atan
        • Atanh
        • Cbrt
        • Ceil
        • Divide
        • Modulus
        • Multiply
        • Subtract
      • 🟦Security
        • Compare Password
        • JWT Decode
        • JWT Encode
        • Login
        • Logout Web User
        • Password Generator
        • Random Number
      • 🟦Time
        • Add Time
        • Day
        • Format Time
        • Minute
        • Nanosecond
        • Parse Time
        • Second
        • Time In
        • Year
        • Year Day
      • 🟦Text
        • Concat
        • Contains
        • Ends With
        • Execute Template
        • I Contains
        • I Ends With
        • I Starts With
        • Starts With
        • LTrim
        • RTrim
        • Replace
        • Regex Find All String
        • Regex Find String
        • Regex Match String
        • Trim
        • Text To Lower
        • Text To Upper
        • Text To Title
        • Url Getarg
      • 🟦Other
        • Assign
        • Run Custom
        • Redirect Web Page Event
        • Render Web Template Event
    • 🟧Variables
      • Array
      • Checkbox
      • Collection
      • Collection (Custom Model)
      • Collection (System Custom Model)
      • Custom Model
      • Date
      • Datetime
      • File
      • Json
      • Number
      • Number with decimal
      • Model
      • System Custom Model
      • Text
      • UUID
    • 🟪Webservices
  • Templates
    • Two sided marketplace template
      • Introduction
        • Part I: Installing and setting up the marketplace template
        • Part II: Understanding what features are included in the marketplace template
        • Part III: Customizing your marketplace frontend without coding
        • Exploring Dittofi's marketplace API
      • Listings
        • How to create a marketplace listing
    • How to build a CRUD app
  • Third Party APIs
    • OAuth 2.0 APIs
      • Introduction To OAuth 2.0
      • Google OAuth 2.0 - PART I
      • Google OAuth 2.0 - PART II
  • Dittofi Admin
    • Your Account
      • Workspace Admin
      • Manage Billing
      • Connect a custom domain
      • How to use a discount code to Dittofi Pro
  • FAQ
    • API Generator
    • How to write custom SQL?
    • Why does my image not display?
    • How can I set up Google Analytics?
    • Exporting and deploying my frontend code
    • What is a partial and what is a prefab?
    • How to set up a basic mulit tenant app on Dittofi
    • How to write a custom endpoint in Dittofi
    • How to integrate Font Awesome into Dittofi
    • How can I set up Dittofi to run on my own private server?
    • How to add roles such as Super Admin, Admin, Manager & User
    • How to prevent duplicate records being added to the database
    • What to do when relation cannot be created because their is violating data in the referenced table
  • Change Logs
    • Dittofi Change Logs
Powered by GitBook
On this page
  • SQL (Structured Query Language)
  • Custom SQL inside Dittofi
  • Common user errors
  • SQL Best Practices
  • How to get help writing SQL?

Was this helpful?

  1. FAQ

How to write custom SQL?

In this tutorial we will look at how to include custom SQL in your events.

PreviousAPI GeneratorNextWhy does my image not display?

Last updated 1 year ago

Was this helpful?

If you've come this far, we are sure you're by now aware that building an app is all about putting data into a database, manipulating data inside a database or fetching data from a database and performing some transformation on that data.

Whilst all visual programming editors have some limitations in terms of what transformations they can perform from a visual editor, writing custom SQL opens up the potential to perform any transformation on the data or database.

In this tutorial we are going to look at:

  • SQL (Structured Query Language)

  • Custom SQL inside Dittofi

  • Common user errors

  • SQL best practices

  • Getting help with custom SQL

SQL (Structured Query Language)

SQL stands for Structed Query Language. It lets you access and manipulate both data stored in your database and also the structure of the database itself. For development purposes SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987.

If you've used any of the actions and events, or built a data model inside Dittofi you will have used SQL. We have just wrapped the technical syntax behind our visual editor.

Custom SQL inside Dittofi

To see how to create custom query inside Dittofi, let's look two examples:

  • Return data from our data model for a variable id

  • Return a count of the number of rows

For the purpose of these examples, we will be using the following users table.

Note, right now Dittofi only provide support for SELECT queries.

Return data from our data model for a variable id

To start with, let's see how we can write the following query inside Dittofi.

SELECT * FROM Users WHERE id = $1;

Inside Dittofi, the easiest way to set this up is to first create a GET Endpoint. To see how to do this check out the reference doc, "Create Records". Below we show you the complete configuration for the endpoint.

Notice, you'll need to add a query variable called "id" to your endpoint. This will be the id that we use in our query. Notice that it is of kind number, which matches the kind of your column in the users data model. You can see this example below.

Next, you'll need to create a corresponding action. To do this, go to the actions tab and press "+New Action".

Next, rename the action to something sensible and link it to your endpoint.

Next you need to create a Custom Model. This Custom Model will hold the value that is returned by the action when we execute our custom query.

Note, our response variable must be a Custom Model because, as we will see in our next example, the Custom Model can return different types of information on our data for example counts, sums and so on.

To configure the custom model, head over to the Custom Models tab and press (A) "+ New Custom Model", (B) "Generate Model From Table" and (C) select your users data model.

Next, let's head back over to the actions tab where we can add a variable to our action and set it up as below.

Notice that we choose the Type "Custom" and write the Query SELECT FROM users WHERE id = $1. The $1 is standard SQL syntax meaning that this value is variable. The variable value in this case is "id" which is added under the Variables section.

Ditto Tip: note, if your query has multiple variables, the order of the $1, $2, $3 etc. must match with the order of the variables inside your action variable.

Lastly save this variable, build your code and return to your endpoint to test the custom query.

Note that we enter the id "7" that is the id for one of the rows in our users data model. This returns the JSON as below.

If you experience any errors at this point, scroll down to the Common User Errors section of this article.

Return a count of the number of rows

Let's see how we can write the following query in Dittofi.

SELECT COUNT(*) FROM USERS WHERE id = $1

To do this, we can use the endpoint and action from the previous example. We will however need a new Custom Model with the field "Count" of Kind "Number".

Next we can update the query and custom model inside our action to the following:

Lastly, you can build your code and head on over to the endpoint to test it. Entering the id = 7 inside our endpoint returns the JSON as below:

{
  "data": {
    "count": 1
  },
  "message": "success",
  "error": ""
}

Common user errors

When building the above SQL statements you can get the following common user error that is visible in the JSON that is returned to your endpoint.

{
  "data": null,
  "message": "missing destination name id in *main.UsersConstruct",
  "error": "e_internal_error"
}

The error message here is "e_internal_error" and the message is "missing destination name id in *main.UsersConstruct".

The reason for this error is that the headers in the Users table do not match the column names in your Custom Model. To fix this, go over to the Data Models tab and expand the traditional relationship view of your Users data model.

Click on the "Id" column. The Name field must match the Tag column inside your Custom Model. There is a technical reason why this needs to be the case, but we will not bore you with the details for now.

Notice that the Name and Tag fields must be exactly the same in both cases and are case sensitive.

SQL Best Practices

If you are new to SQL here are some useful documents that can help you write custom SQL.

How to get help writing SQL?

In the immoral words of Benjimin Parker "with great power comes great responsibility".

Writing custom SQL opens up powerful possibilities to build apps that are increasingly complex, custom and far outstretch what is possible with traditional no-code platforms. However, the syntax takes some getting used to, is very nuanced and if you use it incorrectly can introduce slowness and other funky behavior into your app.

Ditto Tip: please be aware that there are people out there with many years of experience writing SQL so don't expect to be a super SQL hero overnight.

If you think you have what it takes to help our clients write really high powered, clean an custom SQL that conforms to all best practices, contact idea@dittofi.com and we will bestow upon you the title of "Dittofi Expert".

If you're building apps for a client or for yourself and you have not used SQL professionally or have not taken a course in SQL outside Dittofi, we recommend that you contact one of the who will be able to help you achieve great things using custom SQL.

💻To contact a Dittofi Expert, fill in our and someone will reach out.

W3 Schools Introduction to SQL
10 best practices to write readable and maintainable SQL code
Dittofi Experts
help form
How to use custom queries in Dittofi
Users Data Model
Get Endpoint Configuration
Create new action
Adding action variable