Tiny migrations "framework" that makes using sqlite in vals a little easier.

Not great yet, so far can only run "up" for migrations you haven't run yet or down for all the ones you have run.

See https://www.val.town/v/saolsen/sqlite_migrations_example for usage.

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 { sqlite } from "https://esm.town/v/std/sqlite";
export type Migration = {
name: string;
up: string;
down: string;
};
export type Migrations = {
name: string;
migrations: Migration[];
};
export async function migrate(migrations) {
console.log("Running Migrations");
await sqlite.execute(`
create table if not exists migrations (
id integer primary key autoincrement,
name text not null,
step integer not null
) strict;
`);
let rs = await sqlite.execute({
sql: "select step from migrations where name = :name",
args: { name: migrations.name },
});
let step: number = 0;
if (rs.rows.length === 0) {
await sqlite.execute({
sql: "insert into migrations(name, step) values (:name, :step)",
args: { name: migrations.name, step: 0 },
});
} else {
step = Number(rs.rows[0][0]);
}
let batch = [];
let i = 0;
for (i = 0; i < migrations.migrations.length; i++) {
let migration = migrations.migrations[i];
let migration_step = Number(i) + 1;
if (migration_step > step) {
batch.push(migration.up);
console.log(" ", migration_step, migration.name);
}
}
batch.push({
sql: `update migrations set step = :step where name = :name`,
args: { name: migrations.name, step: i },
});
await sqlite.batch(batch);
👆 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.
Comments
pomdtr avatar

Nice val!

A pattern I like is to set the table name as the val name (see https://www.val.town/v/pomdtr/test_migrations).

pomdtr avatar

I wonder if it would make sense to be able to set the migration index as a second argument in the migrate function (defaulting to migrations.length - 1). The migrate function would then apply the up or down migrations depending on the current table state.

saolsen avatar

Nice, yeah using name as a val name is def a nice pattern.

Saying the index or how many migrations to do makes sense. I think it'd be especially helpful to say how many migrations to undo, since I expect when you're working on a new migration you might get it wrong a few times and only want to undo the one you're working on so you can try again.