DT
Drizzle Team•3w ago
partap

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() constraint on the column entirely and I'm still getting the same error??? ...yes, I ran 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!
1 Reply
partap
partapOP•3w ago
I changed the driver from libsql to better-sqlite3 and it works now. ...would still be interested to find out what I did wrong with libsql though...

Did you find this page helpful?