[BUG]:ON DELETE cascade not working

[faribauc]

What version of drizzle-orm are you using?

^0.30.5

What version of drizzle-kit are you using?

^0.20.14

Describe the Bug

I have the following config and schemas:

config.ts:

import Database from 'better-sqlite3'
import { DatabaseConfig } from './config'
import { drizzle } from 'drizzle-orm/better-sqlite3'
import * as schema from '../../database/schema'
import { migrate } from 'drizzle-orm/better-sqlite3/migrator'
import path from 'path'

const client = new Database(DatabaseConfig.databaseName)
export const dbClient = drizzle(client, { schema })
client.pragma('foreign_keys = ON')

migrate(dbClient, { migrationsFolder: path.join(__dirname, '..', '..', 'database', 'migrations') })

schema.ts

export const BaseTableSchema = {
  id: integer('id', { mode: 'number' }).primaryKey({ autoIncrement: true })
}

export const FileAttachmentTableSchema = sqliteTable('file-attachments', {
  ...BaseTableSchema,
  fileName: text('fileName').notNull(),
  filePath: text('filePath').notNull(),
  contentType: text('contentType').notNull(),
  uploadDate: integer('uploadDate', { mode: 'timestamp' }).notNull(),
  content: blob('content').notNull()
})

export const ElevationTableSchemaNew = sqliteTable('test-table', {
  ...BaseTableSchema,
  file_id: integer('file_id').references(() => FileAttachmentTableSchema.id, {
    onDelete: 'cascade'
  })
})

Resulting migration SQL:

CREATE TABLE `file-attachments` (
    `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
    `fileName` text NOT NULL,
    `filePath` text NOT NULL,
    `contentType` text NOT NULL,
    `uploadDate` integer NOT NULL,
    `content` blob NOT NULL
);

CREATE TABLE `test-table` (
    `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
    `file_id` integer,
    FOREIGN KEY (`file_id`) REFERENCES `file-attachments`(`id`) ON UPDATE no action ON DELETE cascade
);

When I run dbClient.delete(TestTableSchemaNew).where(eq(TestTableSchemaNew.id, <id>)) the row from test-table is deleted but the row from file-attachments is left behind and orphaned.

I've also tried client.exec('PRAGMA foreign_keys = ON') but it doesn't make any difference.

Expected behavior

Referenced row should be deleted

Environment & setup

better-sqlite3: ^11.0.0

[richardpasquiou]

Did you try to set client.pragma('foreign_keys = ON'); before creating your dbClient = drizzle(client, { schema })

[faribauc]

@richardpasquiou Wow, that was a rookie mistake :facepalm: :laughing:

Thanks for pointing that out (the error, not the "rookie" part :smiley:)! That being fixed, it's still not working. Here's more details:

I'm adding a migration where I add a foreign key on a table

CREATE TABLE `elevations-2` (
    `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
    `group` text NOT NULL,
    `description` text NOT NULL,
    `vcg` real NOT NULL,
    `is_default` integer NOT NULL,
    `file_id` integer,
    FOREIGN KEY (`file_id`) REFERENCES `file-attachments`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
INSERT INTO `elevations-2` SELECT * FROM `elevations`;--> statement-breakpoint
DROP TABLE `elevations`;

Drizzle migration tool generated the first and last SQL commands and I added the data copy in-between.

The cascading delete doesn't seem to work even when I manually delete a row from elevations in my SQL editor (dBeaver).

PRAGMA foreign_keys;
PRAGMA foreign_keys = OFF; /* to show the difference between on and off)*/
PRAGMA foreign_keys;
PRAGMA foreign_keys = ON;
PRAGMA foreign_keys;
DELETE FROM "elevations-2"  WHERE id=6;

The file-attachments entry still exists.

Is there something wrong with the new table?

[richardpasquiou]

elevations-2 is your baby table. file-attachments is your mama table. You can't drop the baby and recursively, delete the mama entry. It works in one way : drop the mama entry, and it will cascade on baby table

[faribauc]

@richardpasquiou Oh ok, I see. The relation field should be on file-attachments with an elevation_id field instead of having the file_id field on elevations. I was hoping not to have to do that as file-attachments links to multiple tables for a variety of entities. I guess I'll have to rethink my schema. Maybe with pivot tables... Thanks for the insight!