SQLite Dump Util

A utility function that generates SQL statements to dump the data and schema of tables in a SQLite database.

Usage

This example specifically dumps the users table and logs the output:

import { 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.

import { 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 Signature

function sqliteDump(tables?: string[], callback?: ((result: string) => string | void | Promise<void>) | undefined): Promise<string | void>

Parameters

  • 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.
Readme
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
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} where id = 89`);
const insertStatements = generateInsertStatements(table, columns, rows);
statements.push(createStatement, ...insertStatements);
}
if (callback === undefined) {
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.