Do I need separate queries for each CRUD function?

For simplicity, it would be nice to leverage the same query to, say, update and delete records. The ActionType attribute suggests this might be possible, but I don't know if I can change that in code, at runtime.

So, two questions:

  1. Is this an acceptable design pattern? Or do people create a separate query for each CRUD operation, each table, each time? Sounds like a lot of queries....

  2. Where would I find the formal list of constants that can be used with a property like ActionType? The GUI query builder displays full text like "Update an existing record", which the state inspector renders as "UPDATE_BY". I can play with the GUI and state inspector and hack through the list of possible values, but where in general do the Retool docs list this type of info? ActionType is not even shown as a property of the query object in the online documentation.

Interesting question. Would love to hear what others think. I don't think Action Type is scriptable. I don't see a function to set it either.

I currently use a query for each operation on each table. Maybe you could build a CRUD workflow for each table, have a js query build dynamic json like below, then send it to the workflow w/ additionalScope. In the workflow branch on operation. Execute operation. Respond to the app accordingly.

{"operation":"create","name":"john doe"}
{"operation":"update","id":5,"name":"john doe"}


You would have one query in your app and branch the workflow for each operation. I haven't tried this, feels like it just moves the complexity somewhere else.

1 Like

Wow, that's clever. I think I agree, though, that it would be no more maintainable than using queries created in the IDE. I think I'd miss the GUI. I'll stick with multiple queries, I guess. Just wanted to make sure it wasn't doing things the hard way, being new to Retool.

This is going to be a long post but I thought I'd share my own custom solution.
This works for the specific use case that you are using tables (and forms generated from those tables) to update your own database. SQL might need to be adjusted depending on what platform you're using.

The solution involves these queries below, I'll go through them one by one:

This is just a Retool variable (or state if you prefer). Its value should be the name of the table you are currently wanting to reference in your CRUD statements. You can set this variable each time you click onto a new screen for example or open a new modal that has your table/form.

This is a Javascript query. I use this bit of javascript code to generate an insert sql statement which we'll use in the later queries. It's somewhat self explanatory. It covers strings, numbers, dates, and boolean values. Can be adjusted if you need more options than that. You can see that it's referencing the table name and also some values from the transformer which I'll show later.

function generateInsertSQL(jsonString, tableName) {
    const data = JSON.parse(jsonString);

    // Extract column names and values
    const columns = Object.keys(data).map(column => `[${column}]`);
    const values = Object.values(data).map(value => {
        if (value === null || value === '') {
            return 'NULL';
        if (typeof value === 'boolean') {
            return value ? '1' : '0';  // Assuming boolean is represented as 1/0 in SQL
        // Check if the value matches the date-time format
        const dateTimeRegex = /^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}\.\d{3}-\d{4}$/;
        if (typeof value === 'string' && dateTimeRegex.test(value)) {
            // Convert to a format that SQL Server can understand
            return `'${value.substring(0, 19).replace("T", " ")}'`;
        return `'${value}'`;

    // Create the SQL INSERT statement
    const sql = `INSERT INTO ${tableName} (${columns.join(', ')}) VALUES (${values.join(', ')});`;

    return sql;

// Usage:
const jsonString = JSON.stringify(changesetArrays.value[currentTable.value][2]);  // your full JSON string here
const tableName = currentTable.value;
return sqlStatement = generateInsertSQL(jsonString, tableName);

The same idea here as the last one but for when you are updating a table instead of inserting a new row. That makes the sql a little more complex because you can update multiple rows at a time, but luckily we can translate the changesetArray from our Retool table pretty easily (again, I'll go over where it gets this changesetArray based on the table name once we get to the transformer).

function generateUpdateSQL(jsonString, tableName, primaryKey) {
    let dataObjects = JSON.parse(jsonString);

    // If it's a single object, wrap it in an array
    if (!Array.isArray(dataObjects)) {
        dataObjects = [dataObjects];

    const updates = => {
        const setStatements = Object.keys(data).map(key => {
            if (key !== primaryKey) {
                if (data[key] === null || data[key] === '') {
                    return `[${key}] = NULL`;
                if (typeof data[key] === 'boolean') {
                    return `[${key}] = ${data[key] ? '1' : '0'}`;
                const dateTimeRegex = /^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}\.\d{3}-\d{4}$/;
                if (typeof data[key] === 'string' && dateTimeRegex.test(data[key])) {
                    return `[${key}] = '${data[key].substring(0, 19).replace("T", " ")}'`;
                return `[${key}] = '${data[key]}'`;
        }).filter(Boolean).join(', ');

        return `UPDATE ${tableName} SET ${setStatements} WHERE [${primaryKey}] = '${data[primaryKey]}';`;

    return updates.join('\n');

const jsonString = JSON.stringify(changesetArrays.value[currentTable.value][0]);
const tableName = currentTable.value;
const primaryKey = changesetArrays.value[currentTable.value][1];
const sqlStatements = generateUpdateSQL(jsonString, tableName, primaryKey);
return sqlStatements;

In order to make use of Retool's javascript variables inside my sql queries, I use dynamic SQL to "inject" my previously prepared statements. For this one, I just use the generateCreateStatement's value, and then I call this query as soon as generateCreateStatement succeeds. Then from a form I can just call "generateCreateStatement" on submit and it will run through both of these queries to insert the new row.


For this one we don't even need previously prepared sql from a javascript query, all we need to know is the table name. The downside is that we're stuck getting everything from a table, but for most purposes I've found I can filter it in a Retool table or a transformer afterwards just fine.

DECLARE @SELECTSTATEMENT nvarchar(4000), @CURRENTTABLE nvarchar(4000)
SET @SELECTSTATEMENT = 'select * from '
SET @CURRENTTABLE = {{currentTable.value}}

Same thing as the createInTable, but using our previously defined generateUpdateStatement instead of the generateCreateStatement.


This is similar to the readTable query, just with a few more variables to know which row to delete. This requires that your table has a primary key, just like with any updates you make using Retool's SQL GUI editor (meaning compound keys won't work, you'll have to convert it to a single column). Then, inside your Retool table, just add a "Delete" action that calls this query and it should work. You'll see how it's getting the primary key when I get to the transformer.

DECLARE @CURRENTTABLE nvarchar(4000), @PRIMARYKEY nvarchar(4000), @SELECTEDROWKEY nvarchar(4000)
SET @CURRENTTABLE = {{currentTable.value}}
SET @PRIMARYKEY = {{changesetArrays.value[currentTable.value][1]}}
SET @SELECTEDROWKEY = {{changesetArrays.value[currentTable.value][3]}}

This is maybe not the most aptly named, but this is the transformer where your queries above get all their information from. Here's an example of what it could look like:

//Add all the tables you need here, along with their respective changesetArray, Primary Key Column Name (as a string), the data from the "Create New" Form, and the selectedRowKey

return {
  "dbo.tblLaborRates": [{{laborTable.changesetArray}}, "CraftCode", {{}}, {{laborTable.selectedRowKey}}],
  "dbo.tblClassCodes": [{{classCodesTable.changesetArray}}, "CLASS", {{}}, {{classCodesTable.selectedRowKey}}],
  "dbo.tblUnionCodes": [{{unionCodesTable.changesetArray}}, "UnionCode", {{}}, {{unionCodesTable.selectedRowKey}}],
  "dbo.tblTimekeeping": [{{timekeepingTable.changesetArray}}, "PrimaryKeyValue", {{}}, {{timekeepingTable.selectedRowKey}}],

The comment at the top of the code explains how it works. For each table in your database that you want to use these CRUD functions for, you add a line to this transformer's return statement specifying that table's name and then you give it, in order, the changesetArray from your matching Retool table, the name (as a string) of the PrimaryKey of that table (this needs to match the PrimaryKey in your equivalent Retool table too), the data from the form you want to use to insert a new line, and the selectedRowKey for when you click the Delete action button.

Hope this makes sense and is helpful! It's been a big timesaver for me already. All I have to do now is, for example, create a modal with a table inside of it. When the modal opens, I set the currentTable variable to whichever table I want to reference in my db and then on the Retool table inside the modal, I just click "regenerate columns" as now it will have automatically run the readTable query and be able to create a table out of it. Set the primary key, generate a form from the table and set it to trigger the generateCreateStatement. Add a delete action that runs deleteFromTable. And finally add a Save event that runs generateUpdateStatement. Then fill out the changesetArrays transformer with your new table values and you're done. Now I can build tables with all the CRUD functions in about 3 minutes as opposed to 15 or 20 before.


Thanks for this amazingly generous response! The implementation is beyond my current Retool knowledge (e.g. I haven't even looked at transformers), but since you've provided the code I now have something very concrete to work from. I'm going to plug it in and start experimenting, which is sometimes the best way to understand the code IMHO.

I so appreciate your taking the time to lay all this out, as well as sharing your source code. Thank you!

1 Like

My advice on this would be to keep it simple. Have queries for each type of action. Your future self will thank you for this, especially when you're trying to test or debug it or when you come to make a change in 3 months time.