1 reply

Problem inserting into sqlite db: UNIQUE constraint failed: users.email

I'm having a problem getting Drizzle to seed my db programatically.

My table schema:

export const users = sqliteTable('users', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  name: text('name').notNull(),
  username: text('username').notNull().unique(),
  email: text('email').notNull().unique(),
  role: text('role').default('user'),
  password: text('password').notNull(),
  ...
})

export const users = sqliteTable('users', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  name: text('name').notNull(),
  username: text('username').notNull().unique(),
  email: text('email').notNull().unique(),
  role: text('role').default('user'),
  password: text('password').notNull(),
  ...
})

My seeding function looks like this:

const fakeUsers = [
  {
    name: 'Admin User',
    username: 'admin',
    email: 'admin@example.com',
    role: 'admin',
    password: 'notasecurepassword'
  }
]
const db = drizzle(process.env.DATABASE_URL!, { schema })
const { result } = await db.insert(schema.users).values(fakeUsers)

const fakeUsers = [
  {
    name: 'Admin User',
    username: 'admin',
    email: 'admin@example.com',
    role: 'admin',
    password: 'notasecurepassword'
  }
]
const db = drizzle(process.env.DATABASE_URL!, { schema })
const { result } = await db.insert(schema.users).values(fakeUsers)

But when I run the task I get the following error:

Error: Failed query: insert into "users" ("id", "name", "username", "email", "role", "password", "created_at", "updated_at") values (null, ?, ?, ?, ?, ?, (CURRENT_TIMESTAMP), (CURRENT_TIMESTAMP)))
params: Admin User,admin,admin@example.com,admin,notasecurepassword
...
[CAUSE]
LibsqlError {
 ...
  cause: SqliteError {
    message: 'UNIQUE constraint failed: users.email',
    ...
  }
  ...
}

Error: Failed query: insert into "users" ("id", "name", "username", "email", "role", "password", "created_at", "updated_at") values (null, ?, ?, ?, ?, ?, (CURRENT_TIMESTAMP), (CURRENT_TIMESTAMP)))
params: Admin User,admin,admin@example.com,admin,notasecurepassword
...
[CAUSE]
LibsqlError {
 ...
  cause: SqliteError {
    message: 'UNIQUE constraint failed: users.email',
    ...
  }
  ...
}

I'm using a fresh sqlite db file with no data. How can it violate a unique constraint?
I've also tried eliminating the

unique()

unique()

constraint on the column entirely and I'm still getting the same error???
...yes, I ran

drizzle-kit push

drizzle-kit push

first and confirmed that the table had no unique constraints defined on email column

This is my first try using drizzle (and sqlite, for that matter)...not sure if the problem is in drizzle or libsql, but If I manually enter the query into a sqlite console (replacing the param placeholders with the actual values) it works fine.

On MacOS 15.6.1, using drizzle-orm 0.44.7, drizzle-kit 0.31.6, libsql 0.5.22, @libsql/client 0.15.15

Thanks!

Problem inserting into sqlite db: UNIQUE constraint failed: users.email

Similar Threads