Lucid cascade delete not working with .onDelete('CASCADE')

[akatora28]

I'm trying to setup a cascade delete between two related models, Video which has a many to 1 association to Versions. I'm able to successfully create the Video record and an associated Version record for the video, but when calling video.delete() only the video record is deleted, but the associated version record still exists in the database.

Here is how I'm calling create and delete in the controller:

controllers/VideosController.ts

import { HttpContextContract } from '@ioc:Adonis/Core/HttpContext'
import Video from 'App/Models/Video'

export default class VideosController {
    public async store({ request }: HttpContextContract) {
        const name = request.input("name")

        let video = await Video.create({name: name})
        let version = await video.related('versions').create({})

        return version
    }

    public async destroy({ params }: HttpContextContract) {
        const videoId = params.videoId
        let video = await Video.findOrFail(videoId)
        await video.delete()

        return {"message":"Video Deleted"}
    }
}

migrations/videos.ts

...
  public async up () {
    this.schema.createTable(this.tableName, (table) => {
      table.increments('id')

      table.string('name')
      /**
       * Uses timestamptz for PostgreSQL and DATETIME2 for MSSQL
       */
      table.timestamp('created_at', { useTz: true })
      table.timestamp('updated_at', { useTz: true })
    })
  }
...

migrations/versions.ts

...
public async up () {
    this.schema.createTable(this.tableName, (table) => {
      table.increments('id')

      table.integer('video_id')
      table.foreign('video_id').references('id').inTable('videos').onDelete('CASCADE')
      /**
       * Uses timestamptz for PostgreSQL and DATETIME2 for MSSQL
       */
      table.timestamp('created_at', { useTz: true })
      table.timestamp('updated_at', { useTz: true })
    })
  }
...

models/Video.ts

export default class Video extends BaseModel {
  @column({ isPrimary: true })
  public id: number

  @column()
  public name: string

  @hasMany(() => Version)
  public versions: HasMany<typeof Version>

  @column.dateTime({ autoCreate: true })
  public createdAt: DateTime

  @column.dateTime({ autoCreate: true, autoUpdate: true })
  public updatedAt: DateTime
}

models/Version.ts

export default class Version extends BaseModel {
  @column({ isPrimary: true })
  public id: number

  @column()
  public videoId: number

  @belongsTo(() => Video, {
    foreignKey: 'videoId'
  })
  public video: BelongsTo<typeof Video>

  @column.dateTime({ autoCreate: true })
  public createdAt: DateTime

  @column.dateTime({ autoCreate: true, autoUpdate: true })
  public updatedAt: DateTime
}

[akatora28]

After some digging, found that the issue is related to SQLITE and not in the code, by default SQLITE opens with foreign_keys off, so the solution to turn that on is here: adonisjs/lucid#121

I'm inclined to agree with the poster of that original issue though, that I would expect that configuration option to be included by default in the SQLITE connection settings

[thetutlage]

Can you create a PR for the same?

[akatora28]

sure thing, just submitted pr on the lucid repo: adonisjs/lucid#745