Readme
A utility function that generates SQL statements to dump the data and schema of tables in a SQLite database.
This example specifically dumps the users table and logs the output:
Create valimport { sqliteDump } from "https://esm.town/v/nbbaier/sqliteDump";
const dump = await sqliteDump(["users"]);
console.log(dump)
You can optionally specify a callback to handle the result. The example below dumps the users table and emails it using std/email.
Create valimport { email } from "https://esm.town/v/std/email";
import { sqliteDump } from "https://esm.town/v/nbbaier/sqliteDump";
await sqliteDump(["users"], async (res) => {
await email({ text: res });
});
function sqliteDump(tables?: string[], callback?: ((result: string) => string | void | Promise<void>) | undefined): Promise<string | void>
tables: (Optional) Array of table names to include in the dump. If not provided, all tables will be included.callback: (Optional) An (potentially async) callback function to process the dump result. The callback receives the dump string as its argument.
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
import { email } from "https://esm.town/v/std/email";
import { sqlite } from "https://esm.town/v/std/sqlite";
function generateInsertStatements(
tableName: string,
columns: string[],
rows: string[][],
): string[] {
const columnNames = columns.join(", ");
let insertStatements = [];
for (const row of rows) {
const values = row.map(v => `'${typeof v === "string" ? v.replace(/'/g, "%27") : v}'`).join(", ");
const insertStatement = `INSERT INTO ${tableName} (${columnNames}) VALUES (${values});`;
insertStatements.push(insertStatement);
}
return insertStatements;
}
export async function sqliteDump(
tables?: string[],
callback?: (result: string) => string | void | Promise<void>,
) {
const dumpTables = tables !== undefined
? tables
: ((
await sqlite.execute(
`select name, type from sqlite_schema where type = 'table'`,
)
).rows.map((row) => row[0]) as string[]);
let statements: string[] = [];
for (const table of dumpTables) {
const schemaQuery = await sqlite.execute(
`SELECT name, sql FROM sqlite_schema WHERE name = '${table}'`,
);
const tableSchema = schemaQuery.rows[0][1] as string;
const createStatement = tableSchema.replace("CREATE TABLE", "CREATE TABLE IF NOT EXISTS");
const { columns, rows } = await sqlite.execute(`select * from ${table}`);
const insertStatements = generateInsertStatements(table, columns, rows);
statements.push(createStatement, ...insertStatements);
}
if (callback === undefined) {
callback = (result) => {
return result;
};
}
const result = `BEGIN TRANSACTION;
${statements.join(";\n").replace(";;", ";")}
COMMIT;`;
return callback(result);
}
👆 This is a val. Vals are TypeScript snippets of code, written in the browser and run on our servers. Create scheduled functions, email yourself, and persist small pieces of data — all from the browser.