[BUG]: drizzle-kit push with SQLite and check constraints always applies a change and errors if theres an index

[itsjxck]

Report hasn't been filed before.

• I have verified that the bug I'm about to report hasn't been filed before.

What version of drizzle-orm are you using?

0.43.1

What version of drizzle-kit are you using?

0.31.1

Other packages

@libsql/client@0.15.7

Describe the Bug

When using SQLite, running drizzle-kit push with schemas that include check constraints, drizzle-kit always seems to try and apply a change the database. This causes an error when indexes exist.

Minimal repro: https://codesandbox.io/p/devbox/stupefied-dubinsky-sx6zvs

Example schema:

export const demo_with_check = sqliteTable(
  "demo_with_check",
  {
    id: integer().primaryKey({ autoIncrement: true }),
    test: integer(),
  },
  (table) => [check("test_positive", sql`0 < ${table.test}`)]
);

export const demo_without_check = sqliteTable("demo_without_check", {
  id: integer().primaryKey({ autoIncrement: true }),
  unique: text().unique().notNull(),
});

First time running push --verbose:

> drizzle-kit push "--verbose"

No config path provided, using default 'drizzle.config.ts'
Reading config file '/project/workspace/drizzle.config.ts'
[✓] Pulling schema from database...

 Warning  You are about to execute current statements:

CREATE TABLE `demo_with_check` (
        `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
        `test` integer,
        CONSTRAINT "test_positive" CHECK(0 < "demo_with_check"."test")
);

CREATE TABLE `demo_without_check` (
        `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
        `unique` text NOT NULL
);

CREATE UNIQUE INDEX `demo_without_check_unique_unique` ON `demo_without_check` (`unique`);

[✓] Changes applied

Running push --verbose again with no changes:

> drizzle-kit push "--verbose"

No config path provided, using default 'drizzle.config.ts'
Reading config file '/project/workspace/drizzle.config.ts'
[✓] Pulling schema from database...

 Warning  You are about to execute current statements:

CREATE TABLE `__new_demo_without_check` (
        `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
        `unique` text NOT NULL
);

INSERT INTO `__new_demo_without_check`("id", "unique") SELECT "id", "unique" FROM `demo_without_check`;
DROP TABLE `demo_without_check`;
ALTER TABLE `__new_demo_without_check` RENAME TO `demo_without_check`;
CREATE UNIQUE INDEX `demo_without_check_unique_unique` ON `demo_without_check` (`unique`);
CREATE UNIQUE INDEX `demo_without_check_unique_unique` ON `demo_without_check` (`unique`);

LibsqlError: SQLITE_ERROR: index demo_without_check_unique_unique already exists
    at mapSqliteError (file:///project/workspace/node_modules/.pnpm/@libsql+client@0.15.7/node_modules/@libsql/client/lib-esm/sqlite3.js:380:16)
    at executeStmt (file:///project/workspace/node_modules/.pnpm/@libsql+client@0.15.7/node_modules/@libsql/client/lib-esm/sqlite3.js:285:15)
    at Sqlite3Client.execute (file:///project/workspace/node_modules/.pnpm/@libsql+client@0.15.7/node_modules/@libsql/client/lib-esm/sqlite3.js:81:16)
    ... 3 lines matching cause stack trace ...
    at async run (/project/workspace/node_modules/.pnpm/drizzle-kit@0.31.1/node_modules/drizzle-kit/bin.cjs:91903:7) {
  code: 'SQLITE_ERROR',
  rawCode: 1,
  [cause]: SqliteError: index demo_without_check_unique_unique already exists
      at convertError (/project/workspace/node_modules/.pnpm/libsql@0.5.11/node_modules/libsql/index.js:59:12)
      at Database.prepare (/project/workspace/node_modules/.pnpm/libsql@0.5.11/node_modules/libsql/index.js:129:13)
      at executeStmt (file:///project/workspace/node_modules/.pnpm/@libsql+client@0.15.7/node_modules/@libsql/client/lib-esm/sqlite3.js:256:28)
      at Sqlite3Client.execute (file:///project/workspace/node_modules/.pnpm/@libsql+client@0.15.7/node_modules/@libsql/client/lib-esm/sqlite3.js:81:16)
      at Object.run (/project/workspace/node_modules/.pnpm/drizzle-kit@0.31.1/node_modules/drizzle-kit/bin.cjs:79279:26)
      at sqlitePush (/project/workspace/node_modules/.pnpm/drizzle-kit@0.31.1/node_modules/drizzle-kit/bin.cjs:82484:24)
      at async Object.handler (/project/workspace/node_modules/.pnpm/drizzle-kit@0.31.1/node_modules/drizzle-kit/bin.cjs:92670:9)
      at async run (/project/workspace/node_modules/.pnpm/drizzle-kit@0.31.1/node_modules/drizzle-kit/bin.cjs:91903:7) {
    code: 'SQLITE_ERROR',
    rawCode: 1
  }
}

Removing the .unique() and running push --verbose:

> drizzle-kit push "--verbose"

No config path provided, using default 'drizzle.config.ts'
Reading config file '/project/workspace/drizzle.config.ts'
[✓] Pulling schema from database...

 Warning  You are about to execute current statements:

CREATE TABLE `__new_demo_without_check` (
        `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
        `unique` text NOT NULL
);

INSERT INTO `__new_demo_without_check`("id", "unique") SELECT "id", "unique" FROM `demo_without_check`;
DROP TABLE `demo_without_check`;
ALTER TABLE `__new_demo_without_check` RENAME TO `demo_without_check`;

[✓] Changes applied

Running push --verbose again with no additional changes (this can be run N times and always does exactly the same thing):

> drizzle-kit push "--verbose"

No config path provided, using default 'drizzle.config.ts'
Reading config file '/project/workspace/drizzle.config.ts'
[✓] Pulling schema from database...

 Warning  You are about to execute current statements:

CREATE TABLE `__new_demo_without_check` (
        `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
        `unique` text NOT NULL
);

INSERT INTO `__new_demo_without_check`("id", "unique") SELECT "id", "unique" FROM `demo_without_check`;
DROP TABLE `demo_without_check`;
ALTER TABLE `__new_demo_without_check` RENAME TO `demo_without_check`;

[✓] Changes applied

Removing the check constraint and running push --verbose then seemingly recreates everything:

> drizzle-kit push "--verbose"

No config path provided, using default 'drizzle.config.ts'
Reading config file '/project/workspace/drizzle.config.ts'
[✓] Pulling schema from database...

 Warning  You are about to execute current statements:

CREATE TABLE `__new_demo_with_check` (
        `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
        `test` integer
);

INSERT INTO `__new_demo_with_check`("id", "test") SELECT "id", "test" FROM `demo_with_check`;
DROP TABLE `demo_with_check`;
ALTER TABLE `__new_demo_with_check` RENAME TO `demo_with_check`;
CREATE TABLE `__new_demo_without_check` (
        `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
        `unique` text NOT NULL
);

INSERT INTO `__new_demo_without_check`("id", "unique") SELECT "id", "unique" FROM `demo_without_check`;
DROP TABLE `demo_without_check`;
ALTER TABLE `__new_demo_without_check` RENAME TO `demo_without_check`;

[✓] Changes applied

Running push --verbose from here correctly detects that there are no changes required:

> drizzle-kit push "--verbose"

No config path provided, using default 'drizzle.config.ts'
Reading config file '/project/workspace/drizzle.config.ts'
[✓] Pulling schema from database...

[i] No changes detected

[hichemfantar]

moved my comment to drizzle-team/drizzle-sqljs#1

i guess some better error messages would be nice because the terminal error doesn't show the table causing the issue, culprit table only shows in the ui

[hichemfantar]

this issue is also relevant to drizzle kit and sqlite #4582