Overview

Use the SQL plugin to write, save, and automate SQL query jobs in Mitto. Mitto can send these queries to any database it has connectivity to.

Typical use cases include creating schemas, tables, views, and stored procedures.

Create a SQL job

Click +Add Job.

Choose "SQL job".

Fill in the form.

  • Title - title of the job - Best practice naming convention [SQL] <schema>.<table>
  • Sql - the SQL query to be sent to the database

To send queries to a different database than Mitto's internal PostgreSQL database, uncheck "Use default database URI"

Click "Done".

SQL Syntax and Validation

SQL queries in SQL jobs need to be written in the syntax of the database they are being sent to.

Mitto does not validate SQL queries when creating a SQL job. Best practice is to write your SQL queries in another tool (e.g. DataGrip) to test the SQL, and then when you are ready to save and automate the SQL, copy the SQL into a Mitto job.

Example SQL job:

This SQL drops and recreates the normal_sql table in the test schema. This is one method for creating new tables in the database.

Commenting in SQL

Mitto only accepts multi-line comments.

Don't use this comment syntax:

Use this comment syntax:

Parameterized SQL

Parameters can be passed to SQL in Mitto jobs. This is useful when users need to make small changes to SQL jobs frequently (e.g. changing a date range in a WHERE clause).

Rather than the user having to comb through tons of SQL, they can simply change a parameter in the SQL jobs JSON config.

Below is an example of using a parameter (:number) in a SQL job.

Instead of hardcoding the value in the SQL itself, the value is defined in the job's config.

Key/value pairs can be added to the parameters of the JSON config.