Readme

sqliteBuilder: Opinionated safe(r) query builder using tagged templates

import { Statement } from "https://esm.town/v/postpostscript/sqliteBuilder"; const unsafeId = "1234 or TRUE" console.log(Statement` SELECT * FROM table WHERE id = ${unsafeId} ${Statement`AND otherCondition`} `) // StatementInstance { // sql: "\nSELECT *\nFROM table\nWHERE id = ?\nAND otherCondition\n", // args: [ "1234 or TRUE" ], // log: false // } const results = await Statement`SELECT ...`.execute() // [ { key: "value", anotherKey: "anotherValue" }, ... ]

Or you can pass it directly to @std/sqlite.execute:

import { sqlite } from "https://esm.town/v/std/sqlite" await sqlite.execute(Statement`Select ...`)

You can combine multiple statements using Statement.prototype.combineWith:

Statement`...`.combineWith(Statement`...`, " AND ") [ Statement`fieldA`, Statement`fieldB`, Statement`fieldC`, ].reduce((a, b) => a.combineWith(b, ", "))
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
import { type SqliteInterface } from "https://esm.town/v/postpostscript/sqliteTypes";
import { MaybePromise } from "https://esm.town/v/postpostscript/typeUtils";
import { type InStatement, type InValue, sqlite } from "https://esm.town/v/std/sqlite";
import { LibsqlError, type Row } from "npm:@libsql/client";
export { LibsqlError } from "npm:@libsql/client";
export type ExecuteOptions<T extends Record<string, any>, I extends SqliteInterface> = {
fallback?: (
stmt: StatementInstance,
options: ExecuteOptions<T, I>,
error: Error,
) => MaybePromise<T[]>;
sqlite?: I;
};
export class StatementInstance {
public sql: string;
public args: InValue[];
public log: boolean;
constructor(sql: string, args: InValue[] = []) {
this.sql = sql;
this.args = args;
this.log = false;
}
combineWith(instance: StatementInstance, sep = "") {
return new StatementInstance(this.sql + sep + instance.sql, this.args.concat(instance.args));
}
execute<T extends Record<string, any>, I extends SqliteInterface>(
options: ExecuteOptions<T, I> = {},
): MaybePromise<T[]> {
if (this.log) {
console.log("executing", {
sql: this.sql,
args: this.args,
});
}
const callback = ({ columns, rows }) => {
return rows.map(row => {
return Object.fromEntries(columns.map((key, i) => {
return [key, row[i]];
}));
});
};
const errorHandle = (e: Error) => {
if (options.fallback) {
return options.fallback(this, {
...options,
fallback: undefined,
}, e);
}
throw e;
};
try {
const queryResult = (options.sqlite ?? sqlite).execute(this);
if (!(queryResult instanceof Promise)) {
return callback(queryResult);
}
return queryResult.then(callback).catch(errorHandle);
} catch (e) {
return errorHandle(e);
}
}
toJSON() {
return {
sql: this.sql,
args: this.args,
};
}
async debug() {
const result = await this.execute();
const { default: Table } = await import("npm:easy-table");
console.debug(Table.print(result));
return result;
}
}
export function Statement(
strings: TemplateStringsArray,
...replacements: (InValue | StatementInstance)[]
) {
return strings.reduce((statement, string, index) => {
const stringInstance = new StatementInstance(string);
if (replacements.length >= index + 1) {
const replacement = replacements[index];
return statement
.combineWith(stringInstance)
.combineWith(
replacement instanceof StatementInstance ? replacement : new StatementInstance("?", [replacement]),
);
}
return statement.combineWith(stringInstance);
Val Town is a social website to write and deploy JavaScript.
Build APIs and schedule functions from your browser.
Comments
5
stevekrouse avatar

This is AWESOME! I hate having to do it with with the ?s

nbbaier avatar

@postpostscript, would there be a way to make Statement type safe in a way where we could do something like this:

interface User {
  name: string;
  age: number;
}

Statement<User>`insert into user values(${name}, ${age})`

Where it would type check if the write types were provided in replacements?

postpostscript avatar
nbbaier avatar

@postpostscript Awesome! Really cool, thanks for implementing this

postpostscript avatar

@nbbaier thanks, and no problem!

v36
March 17, 2024