KnexJs - Sqlite3 On Delete cascade doesn't work

Question

i'm trying to make onDelete('CASCADE') work on knex js but it doesn't seem to work at all. Here's my knexjs migrations :

const tableName = 'users'

exports.up = function(knex) {
  return knex.schema.createTable(tableName, table => {
      table.increments('id').primary();
      table.string('username');
      table.string('email');
      table.string('password');
      table.timestamps(true,true);
  })
};

exports.down = function(knex) {
  knex.schema.dropTableIfExists(tableName);
};

const tableName = 'todolists';

exports.up = function(knex) {
  return knex.schema.createTable(tableName, table => {
      table.increments('id').primary();
      table.string('title');
      table.boolean('completed').defaultTo(false);
      table.timestamps(true,true);

      table.integer('users_id').unsigned().notNullable();
      table.foreign('users_id').references('users.id').onDelete('CASCADE');
  })
};

exports.down = function(knex) {
  return knex.schema.dropTableIfExists(tableName);
};

When i try to delete users table, their respective todolists aren't removed and still reference the deleted user's ids.

Here's the Sqlite3 CLI schema definition and it seems the onDelete('CASCADE') is ON.

sqlite> .schema users
CREATE TABLE `users` (`id` integer not null primary key autoincrement, `username` varchar(255), `email` varchar(255), `password` varchar(255), `created_at` datetime not null default CURRENT_TIMESTAMP, `updated_at` datetime not null default CURRENT_TIMESTAMP);

sqlite> .schema todolists
CREATE TABLE `todolists` (`id` integer not null primary key autoincrement, `title` varchar(255), `completed` boolean default '0', `created_at` datetime not null default CURRENT_TIMESTAMP, `updated_at` datetime not null default CURRENT_TIMESTAMP, `users_id` integer not null, foreign key(`users_id`) references `users`(`id`) on delete CASCADE);

Am I missing something ?

thanks for the help.

EDIT : Turns out i needed to first foreign key support on the Sqlite side (it's an SQlite related issue, not related to knex/objection js) I ran this line code in my program just after initializing knex js :

await knex.raw('PRAGMA foreign_keys = ON');

This enables foreign key support and takes into account the "on delete CASCADE" constraint in my Schema definition.

Did you turn on foreign key enforcement? https://www.sqlite.org/foreignkeys.html#fk_enable — Shawn, Jan 19 '20 at 18:31
Hi, i was just about to answer my own question as after doing some research this time concentrating on the Sqlite side rathern thatn on knexjs i found out that this was indeed the problem, i'll edit my question and post the response. Thanks for the help anyway :) — user7616817, Jan 19 '20 at 19:54
recently, same functionality was brooked and fixed - https://github.com/knex/knex/pull/4225 — kmmbvnr, Jan 14 '21 at 09:19

score 3 · Answer 1 · answered Jan 19 '20 at 19:59