20473576077

Committed 23 Dec 2025 11:05PM UTC coverage: 93.117% (+0.02%) from 93.095%

Build # 20473576077

Build Type

push

github

Committed by

web-flow

Commit Message

Refactor SQLite transactions to allow setting the foreign_keys pragma for a transaction (#6315)

Run Details

3623 of 4273 branches covered (84.79%)

Branch coverage included in aggregate %.

191 of 203 new or added lines in 16 files covered. (94.09%)

1 existing line in 1 file now uncovered.

14910 of 15630 relevant lines covered (95.39%)

2356.88 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

94.66

/lib/dialects/sqlite3/schema/ddl.js

// SQLite3_DDL
//
// All of the SQLite3 specific DDL helpers for renaming/dropping
// columns and changing datatypes.
// -------

const identity = require('lodash/identity');
const { nanonum } = require('../../../util/nanoid');
const {
  copyData,
  dropOriginal,
  renameTable,
  getTableSql,
  isForeignCheckEnabled,
  setForeignCheck,
  executeForeignCheck,
} = require('./internal/sqlite-ddl-operations');
const { parseCreateTable, parseCreateIndex } = require('./internal/parser');
const {
  compileCreateTable,
  compileCreateIndex,
} = require('./internal/compiler');
const { isEqualId, includesId } = require('./internal/utils');

// So altering the schema in SQLite3 is a major pain.
// We have our own object to deal with the renaming and altering the types
// for sqlite3 things.
class SQLite3_DDL {
  constructor(client, tableCompiler, pragma, connection) {
    this.client = client;
    this.tableCompiler = tableCompiler;
    this.pragma = pragma;
    this.tableNameRaw = this.tableCompiler.tableNameRaw;
    this.alteredName = `_knex_temp_alter${nanonum(3)}`;
    this.connection = connection;
    this.formatter = (value) =>
      this.client.customWrapIdentifier(value, identity);
    this.wrap = (value) => this.client.wrapIdentifierImpl(value);
  }

  tableName() {
    return this.formatter(this.tableNameRaw);
  }

  getTableSql() {
    const tableName = this.tableName();

    return this.client.transaction(
      async (trx) => {
        trx.disableProcessing();
        const result = await trx.raw(getTableSql(tableName));
        trx.enableProcessing();

        return {
          createTable: result.filter((create) => create.type === 'table')[0]
            .sql,
          createIndices: result
            .filter((create) => create.type === 'index')
            .map((create) => create.sql),
        };
      },
      { connection: this.connection, enforceForeignCheck: null }
    );
  }

  async isForeignCheckEnabled() {
    const result = await this.client
      .raw(isForeignCheckEnabled())
      .connection(this.connection);

    return result[0].foreign_keys === 1;
  }

  async setForeignCheck(enable) {
    await this.client.raw(setForeignCheck(enable)).connection(this.connection);
  }

  renameTable(trx) {
    return trx.raw(renameTable(this.alteredName, this.tableName()));
  }

  dropOriginal(trx) {
    return trx.raw(dropOriginal(this.tableName()));
  }

  copyData(trx, columns) {
    return trx.raw(copyData(this.tableName(), this.alteredName, columns));
  }

  async alterColumn(columns) {
    const { createTable, createIndices } = await this.getTableSql();

    const parsedTable = parseCreateTable(createTable);
    parsedTable.table = this.alteredName;

    parsedTable.columns = parsedTable.columns.map((column) => {
      const newColumnInfo = columns.find((c) => isEqualId(c.name, column.name));

      if (newColumnInfo) {
        column.type = newColumnInfo.type;

        column.constraints.default =
          newColumnInfo.defaultTo !== null
            ? {
                name: null,
                value: newColumnInfo.defaultTo,
                expression: false,
              }
            : null;

        column.constraints.notnull = newColumnInfo.notNull
          ? { name: null, conflict: null }
          : null;

        column.constraints.null = newColumnInfo.notNull
          ? null
          : column.constraints.null;
      }

      return column;
    });

    const newTable = compileCreateTable(parsedTable, this.wrap);

    return this.generateAlterCommands(newTable, createIndices);
  }

  async dropColumn(columns) {
    const { createTable, createIndices } = await this.getTableSql();

    const parsedTable = parseCreateTable(createTable);
    parsedTable.table = this.alteredName;

    parsedTable.columns = parsedTable.columns.filter(
      (parsedColumn) =>
        parsedColumn.expression || !includesId(columns, parsedColumn.name)
    );

    if (parsedTable.columns.length === 0) {
      throw new Error('Unable to drop last column from table');
    }

    parsedTable.constraints = parsedTable.constraints.filter((constraint) => {
      if (constraint.type === 'PRIMARY KEY' || constraint.type === 'UNIQUE') {
        return constraint.columns.every(
          (constraintColumn) =>
            constraintColumn.expression ||
            !includesId(columns, constraintColumn.name)
        );
      } else if (constraint.type === 'FOREIGN KEY') {
        return (
          constraint.columns.every(
            (constraintColumnName) => !includesId(columns, constraintColumnName)
          ) &&
          (constraint.references.table !== parsedTable.table ||
            constraint.references.columns.every(
              (referenceColumnName) => !includesId(columns, referenceColumnName)
            ))
        );
      } else {
        return true;
      }
    });

    const newColumns = parsedTable.columns.map((column) => column.name);

    const newTable = compileCreateTable(parsedTable, this.wrap);

    const newIndices = [];
    for (const createIndex of createIndices) {
      const parsedIndex = parseCreateIndex(createIndex);

      parsedIndex.columns = parsedIndex.columns.filter(
        (parsedColumn) =>
          parsedColumn.expression || !includesId(columns, parsedColumn.name)
      );

      if (parsedIndex.columns.length > 0) {
        newIndices.push(compileCreateIndex(parsedIndex, this.wrap));
      }
    }

    return this.alter(newTable, newIndices, newColumns);
  }

  async dropForeign(columns, foreignKeyName) {
    const { createTable, createIndices } = await this.getTableSql();

    const parsedTable = parseCreateTable(createTable);
    parsedTable.table = this.alteredName;

    if (!foreignKeyName) {
      parsedTable.columns = parsedTable.columns.map((column) => ({
        ...column,
        references: includesId(columns, column.name) ? null : column.references,
      }));
    }

    parsedTable.constraints = parsedTable.constraints.filter((constraint) => {
      if (constraint.type === 'FOREIGN KEY') {
        if (foreignKeyName) {
          return (
            !constraint.name || !isEqualId(constraint.name, foreignKeyName)
          );
        }

        return constraint.columns.every(
          (constraintColumnName) => !includesId(columns, constraintColumnName)
        );
      } else {
        return true;
      }
    });

    const newTable = compileCreateTable(parsedTable, this.wrap);

    return this.alter(newTable, createIndices);
  }

  async dropPrimary(constraintName) {
    const { createTable, createIndices } = await this.getTableSql();

    const parsedTable = parseCreateTable(createTable);
    parsedTable.table = this.alteredName;

    parsedTable.columns = parsedTable.columns.map((column) => ({
      ...column,
      primary: null,
    }));

    parsedTable.constraints = parsedTable.constraints.filter((constraint) => {
      if (constraint.type === 'PRIMARY KEY') {
        if (constraintName) {
          return (
            !constraint.name || !isEqualId(constraint.name, constraintName)
          );
        } else {
          return false;
        }
      } else {
        return true;
      }
    });

    const newTable = compileCreateTable(parsedTable, this.wrap);

    return this.alter(newTable, createIndices);
  }

  async primary(columns, constraintName) {
    const { createTable, createIndices } = await this.getTableSql();

    const parsedTable = parseCreateTable(createTable);
    parsedTable.table = this.alteredName;

    parsedTable.columns = parsedTable.columns.map((column) => ({
      ...column,
      primary: null,
    }));

    parsedTable.constraints = parsedTable.constraints.filter(
      (constraint) => constraint.type !== 'PRIMARY KEY'
    );

    parsedTable.constraints.push({
      type: 'PRIMARY KEY',
      name: constraintName || null,
      columns: columns.map((column) => ({
        name: column,
        expression: false,
        collation: null,
        order: null,
      })),
      conflict: null,
    });

    const newTable = compileCreateTable(parsedTable, this.wrap);

    return this.alter(newTable, createIndices);
  }

  async foreign(foreignInfo) {
    const { createTable, createIndices } = await this.getTableSql();

    const parsedTable = parseCreateTable(createTable);
    parsedTable.table = this.alteredName;

    parsedTable.constraints.push({
      type: 'FOREIGN KEY',
      name: foreignInfo.keyName || null,
      columns: foreignInfo.column,
      references: {
        table: foreignInfo.inTable,
        columns: foreignInfo.references,
        delete: foreignInfo.onDelete || null,
        update: foreignInfo.onUpdate || null,
        match: null,
        deferrable: null,
      },
    });

    const newTable = compileCreateTable(parsedTable, this.wrap);

    return this.generateAlterCommands(newTable, createIndices);
  }

  async setNullable(column, isNullable) {
    const { createTable, createIndices } = await this.getTableSql();

    const parsedTable = parseCreateTable(createTable);
    parsedTable.table = this.alteredName;

    const parsedColumn = parsedTable.columns.find((c) =>
      isEqualId(column, c.name)
    );

    if (!parsedColumn) {
      throw new Error(
        `.setNullable: Column ${column} does not exist in table ${this.tableName()}.`
      );
    }

    parsedColumn.constraints.notnull = isNullable
      ? null
      : { name: null, conflict: null };

    parsedColumn.constraints.null = isNullable
      ? parsedColumn.constraints.null
      : null;

    const newTable = compileCreateTable(parsedTable, this.wrap);

    return this.generateAlterCommands(newTable, createIndices);
  }

  async alter(newSql, createIndices, columns) {
    await this.client.transaction(
      async (trx) => {
        await trx.raw(newSql);
        await this.copyData(trx, columns);
        await this.dropOriginal(trx);
        await this.renameTable(trx);

        for (const createIndex of createIndices) {
          await trx.raw(createIndex);
        }
      },
      { connection: this.connection, enforceForeignCheck: false }
    );
  }

  async generateAlterCommands(newSql, createIndices, columns) {
    const sql = [];
    const pre = [];
    const post = [];
    let check = null;

    sql.push(newSql);
    sql.push(copyData(this.tableName(), this.alteredName, columns));
    sql.push(dropOriginal(this.tableName()));
    sql.push(renameTable(this.alteredName, this.tableName()));

    for (const createIndex of createIndices) {
      sql.push(createIndex);
    }

    const isForeignCheckEnabled = await this.isForeignCheckEnabled();

    if (isForeignCheckEnabled) {
      pre.push(setForeignCheck(false));
      post.push(setForeignCheck(true));

      check = executeForeignCheck();
    }

    return { pre, sql, check, post };
  }
}

module.exports = SQLite3_DDL;

1	// SQLite3_DDL
2	//
3	// All of the SQLite3 specific DDL helpers for renaming/dropping
4	// columns and changing datatypes.
5	// -------
6
7	const identity = require('lodash/identity');	70✔
8	const { nanonum } = require('../../../util/nanoid');	70✔
9	const {
10	copyData,
11	dropOriginal,
12	renameTable,
13	getTableSql,
14	isForeignCheckEnabled,
15	setForeignCheck,
16	executeForeignCheck,
17	} = require('./internal/sqlite-ddl-operations');	70✔
18	const { parseCreateTable, parseCreateIndex } = require('./internal/parser');	70✔
19	const {
20	compileCreateTable,
21	compileCreateIndex,
22	} = require('./internal/compiler');	70✔
23	const { isEqualId, includesId } = require('./internal/utils');	70✔
24
25	// So altering the schema in SQLite3 is a major pain.
26	// We have our own object to deal with the renaming and altering the types
27	// for sqlite3 things.
28	class SQLite3_DDL {
29	constructor(client, tableCompiler, pragma, connection) {
30	this.client = client;	68✔
31	this.tableCompiler = tableCompiler;	68✔
32	this.pragma = pragma;	68✔
33	this.tableNameRaw = this.tableCompiler.tableNameRaw;	68✔
34	this.alteredName = `_knex_temp_alter${nanonum(3)}`;	68✔
35	this.connection = connection;	68✔
36	this.formatter = (value) =>	68✔
37	this.client.customWrapIdentifier(value, identity);	272✔
38	this.wrap = (value) => this.client.wrapIdentifierImpl(value);	466✔
39	}
40
41	tableName() {
42	return this.formatter(this.tableNameRaw);	272✔
43	}
44
45	getTableSql() {
46	const tableName = this.tableName();	68✔
47
48	return this.client.transaction(	68✔
49	async (trx) => {
50	trx.disableProcessing();	68✔
51	const result = await trx.raw(getTableSql(tableName));	68✔
52	trx.enableProcessing();	68✔
53
54	return {	68✔
55	createTable: result.filter((create) => create.type === 'table')[0]	103✔
56	.sql,
57	createIndices: result
58	.filter((create) => create.type === 'index')	103✔
59	.map((create) => create.sql),	35✔
60	};
61	},
62	{ connection: this.connection, enforceForeignCheck: null }
63	);
64	}
65
66	async isForeignCheckEnabled() {
67	const result = await this.client	20✔
68	.raw(isForeignCheckEnabled())
69	.connection(this.connection);
70
71	return result[0].foreign_keys === 1;	20✔
72	}
73
74	async setForeignCheck(enable) {
UNCOV 75	await this.client.raw(setForeignCheck(enable)).connection(this.connection);	×
76	}
77
78	renameTable(trx) {
79	return trx.raw(renameTable(this.alteredName, this.tableName()));	48✔
80	}
81
82	dropOriginal(trx) {
83	return trx.raw(dropOriginal(this.tableName()));	48✔
84	}
85
86	copyData(trx, columns) {
87	return trx.raw(copyData(this.tableName(), this.alteredName, columns));	48✔
88	}
89
90	async alterColumn(columns) {
91	const { createTable, createIndices } = await this.getTableSql();	8✔
92
93	const parsedTable = parseCreateTable(createTable);	8✔
94	parsedTable.table = this.alteredName;	8✔
95
96	parsedTable.columns = parsedTable.columns.map((column) => {	8✔
97	const newColumnInfo = columns.find((c) => isEqualId(c.name, column.name));	99✔
98
99	if (newColumnInfo) {	55✔
100	column.type = newColumnInfo.type;	15✔
101
102	column.constraints.default =	15✔
103	newColumnInfo.defaultTo !== null	15✔
104	? {
105	name: null,
106	value: newColumnInfo.defaultTo,
107	expression: false,
108	}
109	: null;
110
111	column.constraints.notnull = newColumnInfo.notNull	15✔
112	? { name: null, conflict: null }
113	: null;
114
115	column.constraints.null = newColumnInfo.notNull	15✔
116	? null
117	: column.constraints.null;
118	}
119
120	return column;	55✔
121	});
122
123	const newTable = compileCreateTable(parsedTable, this.wrap);	8✔
124
125	return this.generateAlterCommands(newTable, createIndices);	8✔
126	}
127
128	async dropColumn(columns) {
129	const { createTable, createIndices } = await this.getTableSql();	33✔
130
131	const parsedTable = parseCreateTable(createTable);	33✔
132	parsedTable.table = this.alteredName;	33✔
133
134	parsedTable.columns = parsedTable.columns.filter(	33✔
135	(parsedColumn) =>
136	parsedColumn.expression \|\| !includesId(columns, parsedColumn.name)	141✔
137	);
138
139	if (parsedTable.columns.length === 0) {	33!
140	throw new Error('Unable to drop last column from table');	×
141	}
142
143	parsedTable.constraints = parsedTable.constraints.filter((constraint) => {	33✔
144	if (constraint.type === 'PRIMARY KEY' \|\| constraint.type === 'UNIQUE') {	6✔
145	return constraint.columns.every(	3✔
146	(constraintColumn) =>
147	constraintColumn.expression \|\|	3✔
148	!includesId(columns, constraintColumn.name)
149	);
150	} else if (constraint.type === 'FOREIGN KEY') {	3!
151	return (	3✔
152	constraint.columns.every(	6!
153	(constraintColumnName) => !includesId(columns, constraintColumnName)	3✔
154	) &&
155	(constraint.references.table !== parsedTable.table \|\|
156	constraint.references.columns.every(
157	(referenceColumnName) => !includesId(columns, referenceColumnName)	×
158	))
159	);
160	} else {
161	return true;	×
162	}
163	});
164
165	const newColumns = parsedTable.columns.map((column) => column.name);	108✔
166
167	const newTable = compileCreateTable(parsedTable, this.wrap);	33✔
168
169	const newIndices = [];	33✔
170	for (const createIndex of createIndices) {	33✔
171	const parsedIndex = parseCreateIndex(createIndex);	20✔
172
173	parsedIndex.columns = parsedIndex.columns.filter(	20✔
174	(parsedColumn) =>
175	parsedColumn.expression \|\| !includesId(columns, parsedColumn.name)	26✔
176	);
177
178	if (parsedIndex.columns.length > 0) {	20✔
179	newIndices.push(compileCreateIndex(parsedIndex, this.wrap));	16✔
180	}
181	}
182
183	return this.alter(newTable, newIndices, newColumns);	33✔
184	}
185
186	async dropForeign(columns, foreignKeyName) {
187	const { createTable, createIndices } = await this.getTableSql();	5✔
188
189	const parsedTable = parseCreateTable(createTable);	5✔
190	parsedTable.table = this.alteredName;	5✔
191
192	if (!foreignKeyName) {	5✔
193	parsedTable.columns = parsedTable.columns.map((column) => ({	19✔
194	...column,
195	references: includesId(columns, column.name) ? null : column.references,	19✔
196	}));
197	}
198
199	parsedTable.constraints = parsedTable.constraints.filter((constraint) => {	5✔
200	if (constraint.type === 'FOREIGN KEY') {	10✔
201	if (foreignKeyName) {	9✔
202	return (	2✔
203	!constraint.name \|\| !isEqualId(constraint.name, foreignKeyName)	3✔
204	);
205	}
206
207	return constraint.columns.every(	7✔
208	(constraintColumnName) => !includesId(columns, constraintColumnName)	8✔
209	);
210	} else {
211	return true;	1✔
212	}
213	});
214
215	const newTable = compileCreateTable(parsedTable, this.wrap);	5✔
216
217	return this.alter(newTable, createIndices);	5✔
218	}
219
220	async dropPrimary(constraintName) {
221	const { createTable, createIndices } = await this.getTableSql();	4✔
222
223	const parsedTable = parseCreateTable(createTable);	4✔
224	parsedTable.table = this.alteredName;	4✔
225
226	parsedTable.columns = parsedTable.columns.map((column) => ({	16✔
227	...column,
228	primary: null,
229	}));
230
231	parsedTable.constraints = parsedTable.constraints.filter((constraint) => {	4✔
232	if (constraint.type === 'PRIMARY KEY') {	5✔
233	if (constraintName) {	4✔
234	return (	3✔
235	!constraint.name \|\| !isEqualId(constraint.name, constraintName)	6✔
236	);
237	} else {
238	return false;	1✔
239	}
240	} else {
241	return true;	1✔
242	}
243	});
244
245	const newTable = compileCreateTable(parsedTable, this.wrap);	4✔
246
247	return this.alter(newTable, createIndices);	4✔
248	}
249
250	async primary(columns, constraintName) {
251	const { createTable, createIndices } = await this.getTableSql();	6✔
252
253	const parsedTable = parseCreateTable(createTable);	6✔
254	parsedTable.table = this.alteredName;	6✔
255
256	parsedTable.columns = parsedTable.columns.map((column) => ({	23✔
257	...column,
258	primary: null,
259	}));
260
261	parsedTable.constraints = parsedTable.constraints.filter(	6✔
262	(constraint) => constraint.type !== 'PRIMARY KEY'	×
263	);
264
265	parsedTable.constraints.push({	6✔
266	type: 'PRIMARY KEY',
267	name: constraintName \|\| null,	9✔
268	columns: columns.map((column) => ({	9✔
269	name: column,
270	expression: false,
271	collation: null,
272	order: null,
273	})),
274	conflict: null,
275	});
276
277	const newTable = compileCreateTable(parsedTable, this.wrap);	6✔
278
279	return this.alter(newTable, createIndices);	6✔
280	}
281
282	async foreign(foreignInfo) {
283	const { createTable, createIndices } = await this.getTableSql();	9✔
284
285	const parsedTable = parseCreateTable(createTable);	9✔
286	parsedTable.table = this.alteredName;	9✔
287
288	parsedTable.constraints.push({	9✔
289	type: 'FOREIGN KEY',
290	name: foreignInfo.keyName \|\| null,	14✔
291	columns: foreignInfo.column,
292	references: {
293	table: foreignInfo.inTable,
294	columns: foreignInfo.references,
295	delete: foreignInfo.onDelete \|\| null,	17✔
296	update: foreignInfo.onUpdate \|\| null,	17✔
297	match: null,
298	deferrable: null,
299	},
300	});
301
302	const newTable = compileCreateTable(parsedTable, this.wrap);	9✔
303
304	return this.generateAlterCommands(newTable, createIndices);	9✔
305	}
306
307	async setNullable(column, isNullable) {
308	const { createTable, createIndices } = await this.getTableSql();	3✔
309
310	const parsedTable = parseCreateTable(createTable);	3✔
311	parsedTable.table = this.alteredName;	3✔
312
313	const parsedColumn = parsedTable.columns.find((c) =>	3✔
314	isEqualId(column, c.name)	5✔
315	);
316
317	if (!parsedColumn) {	3!
318	throw new Error(	×
319	`.setNullable: Column ${column} does not exist in table ${this.tableName()}.`
320	);
321	}
322
323	parsedColumn.constraints.notnull = isNullable	3✔
324	? null
325	: { name: null, conflict: null };
326
327	parsedColumn.constraints.null = isNullable	3✔
328	? parsedColumn.constraints.null
329	: null;
330
331	const newTable = compileCreateTable(parsedTable, this.wrap);	3✔
332
333	return this.generateAlterCommands(newTable, createIndices);	3✔
334	}
335
336	async alter(newSql, createIndices, columns) {
337	await this.client.transaction(	48✔
338	async (trx) => {
339	await trx.raw(newSql);	48✔
340	await this.copyData(trx, columns);	48✔
341	await this.dropOriginal(trx);	48✔
342	await this.renameTable(trx);	48✔
343
344	for (const createIndex of createIndices) {	48✔
345	await trx.raw(createIndex);	25✔
346	}
347	},
348	{ connection: this.connection, enforceForeignCheck: false }
349	);
350	}
351
352	async generateAlterCommands(newSql, createIndices, columns) {
353	const sql = [];	20✔
354	const pre = [];	20✔
355	const post = [];	20✔
356	let check = null;	20✔
357
358	sql.push(newSql);	20✔
359	sql.push(copyData(this.tableName(), this.alteredName, columns));	20✔
360	sql.push(dropOriginal(this.tableName()));	20✔
361	sql.push(renameTable(this.alteredName, this.tableName()));	20✔
362
363	for (const createIndex of createIndices) {	20✔
364	sql.push(createIndex);	6✔
365	}
366
367	const isForeignCheckEnabled = await this.isForeignCheckEnabled();	20✔
368
369	if (isForeignCheckEnabled) {	20!
370	pre.push(setForeignCheck(false));	20✔
371	post.push(setForeignCheck(true));	20✔
372
373	check = executeForeignCheck();	20✔
374	}
375
376	return { pre, sql, check, post };	20✔
377	}
378	}
379
380	module.exports = SQLite3_DDL;	70✔

knex / knex / 20473576077

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous