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:
currentTable
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.
generateCreateStatement
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);
console.log(sqlStatement);
generateUpdateStatement
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 = dataObjects.map(data => {
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]);
console.log(jsonString);
const tableName = currentTable.value;
const primaryKey = changesetArrays.value[currentTable.value][1];
const sqlStatements = generateUpdateSQL(jsonString, tableName, primaryKey);
return sqlStatements;
createInTable
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.
DECLARE @CREATESTATEMENT nvarchar(4000)
SET @CREATESTATEMENT = {{generateCreateStatement.data}}
EXEC (@CREATESTATEMENT)
readTable
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}}
EXEC (@SELECTSTATEMENT+@CURRENTTABLE)
updateTable
Same thing as the createInTable, but using our previously defined generateUpdateStatement instead of the generateCreateStatement.
DECLARE @UPDATESTATEMENT nvarchar(4000)
SET @UPDATESTATEMENT = {{generateUpdateStatement.data}}
EXEC (@UPDATESTATEMENT)
deleteFromTable
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]}}
EXEC ('DELETE from '+@CURRENTTABLE+' WHERE ['+@PRIMARYKEY+'] = '+''''+@SELECTEDROWKEY+'''')
changesetArrays
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", {{laborForm.data}}, {{laborTable.selectedRowKey}}],
"dbo.tblClassCodes": [{{classCodesTable.changesetArray}}, "CLASS", {{classCodesForm.data}}, {{classCodesTable.selectedRowKey}}],
"dbo.tblUnionCodes": [{{unionCodesTable.changesetArray}}, "UnionCode", {{unionCodesForm.data}}, {{unionCodesTable.selectedRowKey}}],
"dbo.tblTimekeeping": [{{timekeepingTable.changesetArray}}, "PrimaryKeyValue", {{timekeepingForm.data}}, {{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.