Last active
July 24, 2024 22:24
-
-
Save pom421/b735ce37dfa11c6246e6b3887f57894d to your computer and use it in GitHub Desktop.
Build SQL query with parameters in Script Kit and display a link for Datasette
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| // 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} | |
| `)) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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 !