1

I'm stumped by a very weird problem and to be honest I'm not even sure what to search for on google.

This is the situation: I am using AdonisJS and it's migration/seeder functionality. I have a seeder that creates data in a table:

import BaseSchema from '@ioc:Adonis/Lucid/Schema'

export default class extends BaseSchema {
  protected tableName = 'roll_table_tags'

  public async up() {
    this.schema.createTable(this.tableName, (table) => {
      table.increments('id')
      table
        .uuid('roll_table_id')
        .references('roll_tables.id')
        .onDelete('cascade')
        .onUpdate('cascade')
      table.string('tag_id').references('tags.id').onDelete('cascade').onUpdate('cascade')
      table.unique(['roll_table_id', 'tag_id'])
      table.index(['roll_table_id', 'tag_id'])

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

  public async down() {
    this.schema.dropTable(this.tableName)
  }
}

To develop and on production I am using a Postgres Database. For integration tests I am using an SQLite DB.

Now the problem:

If I execute the seeder for the Postgres the id is correctly saved as a UUID field (which is filled in the seeder) If I execute the seeder for the SQLite DB the seeder is trying to save the id as an integer (starting at 1) and I get this error for the subsequent call:

insert into `roll_table_tags` (`created_at`, `roll_table_id`, `tag_id`, `updated_at`) values ('2023-03-24 12:56:59', 1, 'alignment', '2023-03-24 12:56:59') - SQLITE_CONSTRAINT: FOREIGN KEY constraint failed

This is the loop for the seeder

for (const table of data) {
  // Tag.query().
  await Tag.updateOrCreateMany(
    "id",
    table.tags.map((tag) => ({ id: tag }))
  );

  const created = await RollTable.updateOrCreate(
    { name: table.name },
    {
      id: randomUUID(),
      name: table.name,
      description: table.description,
    }
  );
  console.log("ID", created.id); // <- This prints a uuid for postgres and an integer for sqlite

  await created
    .related("tags")
    .updateOrCreateMany(
      table.tags.map((tag) => ({ rollTableId: created.id, tagId: tag }))
    );
}

Why is this happening and how can I prevent it.

EDIT:

In my SQLite editor I can see that the uuid type is transformed to a CHAR(36) type.

user2037559
  • 77
  • 1
  • 2
  • 15
  • 2
    The problem is not the seeder process. The problem is the use of SQLite for testing. If you do not use the same database manager for integration testing as the production database manager then **you have not done integration testing**. – Belayer Mar 24 '23 at 17:05
  • @That's not at all correct... It also doesn't answer the question – user2037559 Mar 25 '23 at 07:24
  • It does not answer the question, but it explains the root cause. You may be able to plug this hole (I don't know how), but it is only a question of time until you hit the next difference between the two databases. Yours is no useful integration test. – Laurenz Albe Mar 28 '23 at 12:17
  • Me 3, do not use two different databases unless you have high pain level. The basis for this particular issue( there will be more) is that `SQLITE` does not have a `UUID` type see [Data types](https://sqlite.org/datatype3.html). – Adrian Klaver Mar 29 '23 at 22:13

0 Answers0