Build SQL query with parameters in Script Kit and display a link for Datasette

associateQueryWithParams.ts

// Name: Associate SQL query with params

import "@johnlindquist/kit"

const DATASETTE_URL = "http://localhost:8001/xyz"

// Example usage : call `associateQueryWithParams` with the following log message:
// Query: select "id", "cout_abonnement", "cout_maintenance", "facture_energetique", "cout_isolation_enveloppe", "cout_solution_15", "aides_solution_15" from "bdd_eco" where "bdd_eco"."typologie" = ? and "bdd_eco"."zone_climatique" = ? and "bdd_eco"."type_CH" = ? and "bdd_eco"."type_ECS" = ? and "bdd_eco"."CH" = ? and "bdd_eco"."ECS" = ? and "bdd_eco"."scenario_renovation_enveloppe" = ? and "bdd_eco"."scenario_renovation_systeme" = ? limit ? -- params: ["Petit collectif (2001-2005)", "75 - Paris", "FIOUL", "FIOUL", "COL", "COL", "INIT", "CH + ECS Hybride : PAC + Chaudière", 1]
function associateQueryWithParams(logMessage: string): string {
    // Extract query and params from the log message
    const [queryPart, paramsPart] = logMessage.split(' -- params: ');
    const query = queryPart.replace('Query: ', '').trim();
    const params = JSON.parse(paramsPart);

    // Split the query into parts
    const queryParts = query.split('?');

    // Combine query parts with params
    let result = '';
    for (let i = 0; i < queryParts.length; i++) {
        result += queryParts[i];
        if (i < params.length) {
            result += `'${params[i]}'`; // Wrap param in quotes
        }
    }

    return result;
}

let inputs = await arg()

const associatedQuery = associateQueryWithParams(inputs);

const sqlCode = await highlight(`
\`\`\`sql
${associatedQuery.replace("where", "\nwhere").replace("from", "\nfrom").replace("limit", "\nlimit")}
\`\`\`
`)
    

await div(md(`
# Résultat

[Tester la requête sur Datasette](${DATASETTE_URL}?sql=${encodeURIComponent(associatedQuery)})
    
${sqlCode}
`))

I use Drizzle to access a SQLite db.

I also use Datasette to display the data.

Drizzle is so handy to show the queries that are made to the db. (with this config export const db = drizzle(client, { logger: true });).

Unfortunately, the log are not instantly usable, with the query in one part and the params in another, like this :

Query: select "id", "cout_abonnement", "cout_maintenance", "facture_energetique", "cout_isolation_enveloppe", "cout_solution_15", "aides_solution_15" from "bdd_eco" where "bdd_eco"."typologie" = ? and "bdd_eco"."zone_climatique" = ? and "bdd_eco"."type_CH" = ? and "bdd_eco"."type_ECS" = ? and "bdd_eco"."CH" = ? and "bdd_eco"."ECS" = ? and "bdd_eco"."scenario_renovation_enveloppe" = ? and "bdd_eco"."scenario_renovation_systeme" = ? limit ? -- params: ["Petit collectif (2001-2005)", "75 - Paris", "FIOUL", "FIOUL", "COL", "COL", "INIT", "CH + ECS Hybride : PAC + Chaudière", 1]

This little script in Script Kit, take this input and make a usable SQL query.
It also add a link to your Datasette instance !

Easy debug 🚀 ! Enjoy !