Readme
There are a lot of migrations that SQLite doesn't allow, such as adding a primary key on a table. The way to accomplish this is by creating a new table with the schema you desire and then copying the rows of the old table into it.
This example shows how to:
- Get the schema for the existing table
- Create the new table
- Copy all rows from old to new
- Rename the old table to an archive (just in case)
- Rename the new table to the original table name
This script shows me adding a primary key constraint to the Profile column of my DateMeDocs database. I would console and comment out various parts of it as I went. You can see everything I did in the version history. The main tricky part for me was removing the duplicate primary key entries before doing the migration step, which is a useful thing anyways, from a data cleaning perspective.
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
import { sqlite } from "https://esm.town/v/std/sqlite?v=6";
const tableName = "DateMeDocs";
const newTableName = tableName + "__new";
const archiveTableName = tableName + "__archive";
// 1. get old schema
const oldSchema = await sqlite.execute(`SELECT * FROM pragma_table_info('${tableName}')`);
const createTableStatement = `CREATE TABLE ${tableName} (
${oldSchema.rows.map((row) => `${row[1]} ${row[2]}`).join(",\n ")}
)`;
// console.log(createTableStatement);
/* CREATE TABLE DateMeDocs (
Name TEXT,
Profile TEXT, // below we will add primary key here (the point of the migration)
Gender TEXT,
InterestedIn TEXT,
Age INTEGER,
Location TEXT,
Style TEXT,
WantsKids TEXT,
LocationFlexibility TEXT,
Community TEXT,
Contact TEXT,
LastUpdated TEXT,
id TEXT
) */
// 2. create new schema
const newCreateTableStatement = `CREATE TABLE ${newTableName} (
Name TEXT,
Profile TEXT PRIMARY KEY,
Gender TEXT,
InterestedIn TEXT,
Age INTEGER,
Location TEXT,
Style TEXT,
WantsKids TEXT,
LocationFlexibility TEXT,
Community TEXT,
Contact TEXT,
LastUpdated TEXT,
id TEXT
)`;
// console.log(newCreateTableStatement);
// await sqlite.execute(newCreateTableStatement);
// 3. add the rows from the old table to the new table
// await sqlite.execute(`INSERT INTO ${newTableName} SELECT * FROM ${tableName}`);
// test out all is well
// console.log(await sqlite.execute(`SELECT * FROM ${newTableName} limit 2`));
// 4. archive the old table
// await sqlite.execute(`ALTER TABLE ${tableName} RENAME TO ${archiveTableName}`);
// 5. rename the new table to the old table
// await sqlite.execute(`ALTER TABLE ${newTableName} RENAME TO ${tableName}`);
👆 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.