Skip to content

Commit 8d840e0

Browse files
fix(server): upgrades of a large database answer requests while migrations run
Startup migrations ran on the main connection, whose node:sqlite calls are synchronous, so a long CREATE INDEX blocked the event loop and every request to the already-bound port hung until all migrations finished. Pending migrations on an existing database now run on a read-write worker-thread connection; the listener answers 503 with Retry-After at once until the server is ready. Refs #35421 AI-assisted; verified with the tests and tools listed above.
1 parent fdbca07 commit 8d840e0

3 files changed

Lines changed: 107 additions & 7 deletions

File tree

‎apps/server/src/persistence/Sqlite.test.ts‎

Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -24,13 +24,15 @@ import { migrationManifest, runMigrations } from "./Migrations.ts";
2424
import * as SqlitePersistence from "./Sqlite.ts";
2525

2626
const workers = vi.hoisted((): Array<NodeWorkerThreads.Worker> => []);
27+
const workerData = vi.hoisted((): Array<unknown> => []);
2728

2829
vi.mock("node:worker_threads", async (importOriginal) => {
2930
const actual = await importOriginal<typeof NodeWorkerThreads>();
3031
class RecordedWorker extends actual.Worker {
3132
constructor(...args: ConstructorParameters<typeof actual.Worker>) {
3233
super(...args);
3334
workers.push(this);
35+
workerData.push(args[1]?.workerData);
3436
}
3537
}
3638
return { ...actual, Worker: RecordedWorker };
@@ -192,6 +194,43 @@ it.effect("drops planner statistics a database already has", () => {
192194
);
193195
});
194196

197+
it.effect("upgrades an existing database on a read-write worker connection", () => {
198+
const tempDir = NodeFS.mkdtempSync(NodePath.join(NodeOS.tmpdir(), "t3-sqlite-upgrade-"));
199+
const dbPath = NodePath.join(tempDir, "state.sqlite");
200+
201+
return Effect.gen(function* () {
202+
yield* Effect.gen(function* () {
203+
const sql = yield* SqlClient.SqlClient;
204+
yield* sql`PRAGMA journal_mode = WAL;`;
205+
yield* runMigrations({ toMigrationInclusive: 81 });
206+
}).pipe(Effect.provide(NodeSqliteClient.layer({ filename: dbPath })));
207+
const startedBefore = workerData.length;
208+
209+
yield* Effect.gen(function* () {
210+
const sql = yield* SqlClient.SqlClient;
211+
const rows = yield* sql<{ readonly migration_id: number; readonly name: string }>`
212+
SELECT migration_id, name FROM effect_sql_migrations ORDER BY migration_id
213+
`;
214+
assert.deepStrictEqual(
215+
rows.map((row) => [row.migration_id, row.name] as const),
216+
migrationManifest,
217+
);
218+
}).pipe(
219+
Effect.provide(
220+
SqlitePersistence.layerFromPath(dbPath).pipe(Layer.provide(NodeServices.layer)),
221+
),
222+
);
223+
assert.deepInclude(workerData.slice(startedBefore), {
224+
filename: dbPath,
225+
readOnly: false,
226+
allowExtension: false,
227+
prepareCacheSize: 200,
228+
});
229+
}).pipe(
230+
Effect.ensuring(Effect.sync(() => NodeFS.rmSync(tempDir, { recursive: true, force: true }))),
231+
);
232+
});
233+
195234
it.effect("names a database file that is not SQLite", () => {
196235
const tempDir = NodeFS.mkdtempSync(NodePath.join(NodeOS.tmpdir(), "t3-sqlite-notadb-"));
197236
const dbPath = NodePath.join(tempDir, "state.sqlite");

‎apps/server/src/persistence/Sqlite.ts‎

Lines changed: 26 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -64,6 +64,26 @@ const runMigrationsAsOwner = Effect.fn("SqlitePersistence.runMigrationsAsOwner")
6464
yield* runMigrations();
6565
}, Effect.scoped);
6666

67+
// Index builds over large tables take minutes; on the main connection they block the event loop,
68+
// so requests hang instead of getting the listener's 503. A fresh database stays on the main
69+
// connection, which must create the first table for its auto_vacuum setting to apply.
70+
const runMigrationsOffMainThread = Effect.fn("SqlitePersistence.runMigrationsOffMainThread")(
71+
function* (dbPath: string) {
72+
const sql = yield* SqlClient.SqlClient;
73+
const tables = yield* sql<{
74+
readonly count: number;
75+
}>`SELECT COUNT(*) AS count FROM sqlite_schema WHERE type = 'table'`;
76+
if (!tables[0]?.count || !(yield* hasPendingMigrations())) return yield* runMigrations();
77+
return yield* Effect.gen(function* () {
78+
const worker = yield* SqlClient.SqlClient;
79+
yield* worker`PRAGMA foreign_keys = ON;`;
80+
yield* worker`PRAGMA synchronous = NORMAL;`;
81+
yield* worker`PRAGMA temp_store = MEMORY;`;
82+
return yield* runMigrations();
83+
}).pipe(Effect.provide(NodeSqliteClient.layerWorker(dbPath)));
84+
},
85+
);
86+
6787
const makeLayerSetup = <E, R>(migrate: Effect.Effect<unknown, E, R>) =>
6888
Layer.effectDiscard(
6989
Effect.gen(function* () {
@@ -100,14 +120,14 @@ const makeLayerSetup = <E, R>(migrate: Effect.Effect<unknown, E, R>) =>
100120

101121
const layerSetup = makeLayerSetup(runMigrations());
102122

103-
const makeLayerFromPath = <E, R>(setup: Layer.Layer<never, E, R>) =>
123+
const makeLayerFromPath = <E, R>(makeSetup: (dbPath: string) => Layer.Layer<never, E, R>) =>
104124
Effect.fn("makeSqlitePersistenceLive")(function* (dbPath: string) {
105125
const fs = yield* FileSystem.FileSystem;
106126
const path = yield* Path.Path;
107127
yield* fs.makeDirectory(path.dirname(dbPath), { recursive: true });
108128

109129
return Layer.provideMerge(
110-
setup,
130+
makeSetup(dbPath),
111131
NodeSqliteClient.layer({
112132
filename: dbPath,
113133
readerWorker: true,
@@ -132,7 +152,9 @@ const makeLayerFromPath = <E, R>(setup: Layer.Layer<never, E, R>) =>
132152
);
133153
}, Layer.unwrap);
134154

135-
export const layerFromPath = makeLayerFromPath(layerSetup);
155+
export const layerFromPath = makeLayerFromPath((dbPath) =>
156+
makeLayerSetup(runMigrationsOffMainThread(dbPath)),
157+
);
136158

137159
export const layerMemory = Layer.provideMerge(
138160
layerSetup,
@@ -154,5 +176,5 @@ export const layerConfig = makeLayerConfig(layerFromPath);
154176

155177
/** For CLI commands: pending migrations run only while no server owns the T3 home. */
156178
export const layerCliConfig = makeLayerConfig(
157-
makeLayerFromPath(makeLayerSetup(runMigrationsAsOwner())),
179+
makeLayerFromPath(() => makeLayerSetup(runMigrationsAsOwner())),
158180
);

‎packages/shared/src/nodeSqliteClient.ts‎

Lines changed: 42 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -201,7 +201,7 @@ let db;
201201
try {
202202
// A read-only open cannot be undone by SQL, unlike PRAGMA query_only.
203203
const next = new DatabaseSync(workerData.filename, {
204-
readOnly: true,
204+
readOnly: workerData.readOnly,
205205
allowExtension: workerData.allowExtension,
206206
});
207207
next.exec("PRAGMA busy_timeout = 5000;");
@@ -300,6 +300,7 @@ const READER_POOL_SIZE = 4;
300300
const makeReader = Effect.fnUntraced(function* (
301301
options: SqliteClientConfig,
302302
pool: { unavailable: boolean },
303+
readOnly = true,
303304
) {
304305
let current:
305306
| {
@@ -315,6 +316,7 @@ const makeReader = Effect.fnUntraced(function* (
315316
eval: true,
316317
workerData: {
317318
filename: options.filename,
319+
readOnly,
318320
allowExtension: false,
319321
prepareCacheSize: 200,
320322
},
@@ -358,7 +360,10 @@ const makeReader = Effect.fnUntraced(function* (
358360

359361
const request = (input: ReaderRequest) =>
360362
Effect.withFiber<ReadonlyArray<any>, SqlError>((fiber) => {
361-
const endsTransaction = endsReadTransaction(input.sql);
363+
// A COMMIT that never reached a writer must fail; only its ROLLBACK is moot.
364+
const endsTransaction = readOnly
365+
? endsReadTransaction(input.sql)
366+
: /^\s*ROLLBACK\b/i.test(input.sql);
362367
if (stoppedMessage !== undefined) {
363368
return endsTransaction
364369
? Effect.succeed([])
@@ -411,7 +416,7 @@ const makeReader = Effect.fnUntraced(function* (
411416
},
412417
executeUnprepared(sql, params, rowTransform) {
413418
// Read snapshots need deferred BEGIN; readonly connections reject IMMEDIATE and EXCLUSIVE.
414-
const statement = /^\s*BEGIN\b/i.test(sql) ? "BEGIN" : sql;
419+
const statement = readOnly && /^\s*BEGIN\b/i.test(sql) ? "BEGIN" : sql;
415420
const effect = request({
416421
sql: statement,
417422
params: params ?? [],
@@ -1288,5 +1293,39 @@ const make = Effect.fn("makeWithDatabase")(function* (
12881293
});
12891294
});
12901295

1296+
const makeWorker = Effect.fn("makeWorkerClient")(function* (filename: string) {
1297+
yield* checkNodeSqliteCompat();
1298+
const acquire = yield* makeReader({ filename }, { unavailable: false }, false);
1299+
const connection = yield* acquire;
1300+
if (connection === undefined) {
1301+
return yield* failed(
1302+
new Error("database worker is unavailable"),
1303+
readerFailureMessages.open,
1304+
"open",
1305+
);
1306+
}
1307+
const { acquirer, borrower, onCommitFailure, transactionAcquirer } = Client.makeSqliteAcquirers({
1308+
connection: Effect.succeed(connection),
1309+
semaphore: yield* Semaphore.make(1),
1310+
});
1311+
return yield* Client.make({
1312+
driver: "node:sqlite",
1313+
acquirer,
1314+
borrower,
1315+
compiler: Statement.makeCompilerSqlite(),
1316+
transactionAcquirer,
1317+
onCommitFailure,
1318+
beginTransaction: "BEGIN IMMEDIATE",
1319+
spanAttributes: [[ATTR_DB_SYSTEM_NAME, "sqlite"]],
1320+
});
1321+
});
1322+
1323+
/**
1324+
* A read-write client whose one connection runs in a worker thread, so long statements such as
1325+
* index builds leave the event loop free. Requires a file database already in WAL mode.
1326+
*/
1327+
export const layerWorker = (filename: string): Layer.Layer<Client.SqlClient, SqlError> =>
1328+
Layer.effect(Client.SqlClient, makeWorker(filename)).pipe(Layer.provide(Reactivity.layer));
1329+
12911330
export const layer = (config: SqliteClientConfig): Layer.Layer<Client.SqlClient, SqlError> =>
12921331
Layer.effect(Client.SqlClient, make(config)).pipe(Layer.provide(Reactivity.layer));

0 commit comments

Comments
 (0)