[BUG]: Cannot add an index to SQLite table if it has a primary key

[fforres]

What version of drizzle-orm are you using?

0.27.2

What version of drizzle-kit are you using?

0.19.5

Describe the Bug

I am not able to create a new index in a table where the is already a default index created by a primaryKey() definition.

I had the following definition for my turso db:

export const userSchema = sqliteTable(
  "user",
  {
    id: text("id").primaryKey().notNull(),
    name: text("name"),
    email: text("email"),
    externalId: text("external_id").notNull(),
    createdAt: integer("created_at", { mode: "timestamp_ms" }).default(NOW),
    updatedAt: integer("updated_at", { mode: "timestamp_ms" }),
  }
);

And migrations run without issues.

Then I changed it to the following

export const userSchema = sqliteTable(
  "user",
  {
    id: text("id").primaryKey().notNull(),
    name: text("name"),
    email: text("email"),
    externalId: text("external_id").notNull(),
    createdAt: integer("created_at", { mode: "timestamp_ms" }).default(NOW),
    updatedAt: integer("updated_at", { mode: "timestamp_ms" }),
  },
  (table) => {
    return {
      nameIdx: index("user_name_idx").on(table.name),
    };
  },
);

This is the migration it generated

DROP INDEX IF EXISTS `sqlite_autoindex_user_1`;
CREATE INDEX `user_email_idx` ON `user` (`email`);
ALTER TABLE `user` RENAME TO `__old_push_user`;
CREATE TABLE `user` (
    `id` text PRIMARY KEY NOT NULL,
    `name` text,
    `email` text,
    `external_id` text NOT NULL,
    `created_at` integer DEFAULT (strftime('%s', 'now')),
    `updated_at` integer
);

INSERT INTO "user" SELECT * FROM "__old_push_user";
DROP TABLE `__old_push_user`;
CREATE INDEX `user_name_idx` ON `user` (`name`);
CREATE INDEX `user_email_idx` ON `user` (`email`);

However, when trying to push it to my database, I encountered the following error:

LibsqlError: SQLITE_UNKNOWN: SQLite error: index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped

Expected behavior

I understand where the error is originated, but it seems to be that adding a new index should not be destructive with the previously existing ones.

Especially if those are automatically defined.

(I tried also deleting the table, and manually setting the sqlite_autoindex_user_1 index, assuming than then adding a new index would not destruct the previous one, but as it is a reserved name, it caused issues)

Environment & setup

No response

[fforres]

So, I did an instrospection of the current schema and this is what popped out

export const usersSchema = sqliteTable(
  "users",
  {
    id: text("id").primaryKey().notNull(),
    name: text("name"),
    email: text("email"),
    externalId: text("external_id").notNull(),
    createdAt: integer("created_at", { mode: "timestamp_ms" }).default(NOW),
    updatedAt: integer("updated_at", { mode: "timestamp_ms" }),
  },
  (table) => {
    return {
      nameIdx: index("user_name_idx").on(table.name),
      sqlite_autoindex_user_1: uniqueIndex("sqlite_autoindex_user_1").on(
        table.id,
      ),
    };
  },
);

Seems I had to setup the sqlite index specifically as uniqueIndex and name it sqlite_autoindex_user_1 🙏

However... while it unblocks us moving forward, feels its still an issue. I don't think we should need to create automated indexes.

Moreover, when running migrations onto local sqlite database (for running tests), we get the same issue of

LibsqlError: SQLITE_ERROR: object name reserved for internal use: sqlite_autoindex_users_1

---

Is there a way I can create new indexes on a table. schema, without having to deal with the issues of a primary key? what's the recommendation there? 🤔

[michaeldebetaz]

EDIT: I don't have the problem in drizzle-kit@0.19.12 🎉

Same problem here (drizzle-kit@0.19.11). I can't prototype with drizzle push:sqlite because it wants to drop something when reading the schema.

SqliteError: index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped
    at Database.prepare (/home/dem/projects/datafame/cvaj/ciaf/node_modules/better-sqlite3/lib/methods/wrappers.js:5:21)
    at BetterSqlite.run (/home/dem/projects/datafame/cvaj/ciaf/node_modules/drizzle-kit/index.cjs:42844:17)
    at Command.<anonymous> (/home/dem/projects/datafame/cvaj/ciaf/node_modules/drizzle-kit/index.cjs:53387:33) {
  code: 'SQLITE_ERROR'

[AndriiSherman]

@michaeldebetaz just released a fix for it in drizzle-kit@0.19.2, can you check, please? https://github.com/drizzle-team/drizzle-kit-mirror/releases/tag/v0.19.12

[AndriiSherman]

@fforres should work for introspection as well in drizzle-kit@0.19.2

[michaeldebetaz]

Amazing, I can push like a beast now ! What a luck, I just updated to drizzle-kit@0.19.11 a moment ago 😄

Thank you very much for this great tool 👍

[AndriiSherman]

I guess I'll close this issue, it should fix all the problems. Feel free to reopen if something new appears

[michaeldebetaz]

Sorry for being a bummer, but I encounter new issues with drizzle-kit push:sqlite 😅

// schema.ts
export const users = sqliteTable("users", {
  id: text("id").primaryKey().notNull(),
  username: text("username").notNull(),
  role: text("role").notNull().default("staff"),
  email: text("email").notNull().unique(),
});

If I push, it creates the database and everything is fine. If I comment out role and push again, it gives me the error SqliteError: table users has 8 columns but 9 values were supplied.

If I push again without doing nothing, it works.

Is there a environment I can use to help you reproduce my issues? I tried StackBlitz, but I doesn't seem to be possible to execute command lines in it.