How to write custom SQL?

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

How to use custom queries in Dittofi

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.

Users Data Model

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.

Get Endpoint Configuration

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".

Create 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.

Adding action variable

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.

• W3 Schools Introduction to SQL

• 10 best practices to write readable and maintainable SQL code

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'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 Dittofi Experts who will be able to help you achieve great things using custom SQL.

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

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 [e-mail] and we will bestow upon you the title of "Dittofi Expert".