Val Town is a social website to write and deploy JavaScript.
Build APIs and schedule functions from your browser.
Readme

The beginnings of a val town implementation of the approach to declarative sqlite migrations laid out in this post: Simple declarative schema migration for SQLite

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
import { createSqlite } from "https://esm.town/v/postpostscript/sqliteWasm?v=148";
import { sqlite } from "https://esm.town/v/std/sqlite?v=6";
import _ from "npm:lodash";
// The schema that we want to end up with
const schema = `
create table sales (product TEXT, year INTEGER, income INTEGER, type TEXT);
create table inventory (product TEXT, count INTEGER);
`;
// Create a new db and create the "pristine" tables from the schema above
const pristine = createSqlite();
await pristine.batch(schema.trim().split("\n"));
// Create an object that contains the table names and sql of our target state
const pristineTables = Object.fromEntries(
(await pristine.execute(`SELECT name, sql FROM sqlite_schema
WHERE type = 'table' AND name != 'sqlite_sequence'`)).rows as unknown as string[][],
);
// Create an object that gets the names and sql for the tables in our current db state
const currentTables = Object.fromEntries(
(await sqlite.execute(`SELECT name, sql FROM sqlite_schema
WHERE type = 'table' AND name != 'sqlite_sequence'`)).rows as unknown as string[][],
);
// Figure out which tables are changed and which tables are new
const newTables = _.difference(_.keys(pristineTables), _.keys(currentTables));
const changedTables = _.intersection(_.keys(pristineTables), _.keys(currentTables));
// Log the create statement for the new tables
for (const table of newTables) {
const createTableSql = pristineTables[table];
console.log(createTableSql);
}
// Log the create statement for the changed table with the suffix _new
for (const table of changedTables) {
const regex = new RegExp(` ${table} `);
const createTableSql = pristineTables[table].replace(regex, ` ${table}_new `);
console.log(createTableSql);
}
v72
April 16, 2024