[BUG]: Prepared statements always send the same default value

[buzinas]

What version of drizzle-orm are you using?

0.29.0

What version of drizzle-kit are you using?

0.20.4

Describe the Bug

I'm not 100% if this is a bug or if it's by design, but for me it was quite unexpected:

If I have a prepared statement with some placeholders, and one column has a $default fn, Drizzle runs the fn only once when creating the SQL binary, instead of creating a placeholder for the column and executing the function every time run, get, all etc are called.

It seems to me that $default / $defaultFn should behave like placeholders, otherwise I can't really use prepared statements for eg creating new ids, unless I manually use a placeholder and pass the value.

1. Create a schema with $default, eg:

   import { createId } from '@paralleldrive/cuid2'
   import { sqliteTable, text } from 'drizzle-orm/sqlite-core'
   
   export const user = sqliteTable('user', {
    id: text('id').notNull().$default(createId).primaryKey(),
    email: text('email').notNull().unique(),
    name: text('name').notNull(),
    createdAt: text('created_at').notNull().$default(() => new Date().toISOString()),
    updatedAt: text('updated_at').notNull().$default(() => new Date().toISOString()),
   })

2. Create a prepared statement for inserting a row, eg:

   const upsertUserStatement = db
    .insert(user)
    .values({
      email: sql.placeholder('email'),
      name: sql.placeholder('name'),
    })
    .prepare()

3. Call this statement with different values, eg:

   upsertUserStatement.run({ email: 'john.doe@email.com', name: 'John Doe' })
   upsertUserStatement.run({ email: 'jane.doe@email.com', name: 'Jane Doe' })

Expected behavior

My expected behavior: two users should be created with different ids and different dates. What actually happened: first user is created and second one gives a UNIQUE CONSTRAINT error, user.id.

Environment & setup

No response

[rdzidziguri]

What is the advantage of having prepared statements for inserts or updates? It is powerful when one uses it to select where it could play well with query optimization and statistics on DB. Am I missing the point here?

[Angelelz]

I don't think there is a way to solve this. When you call prepare on a query builder, the actual query gets created and is stored as a ready-to-be-sent object. There is no way for the query to know how many values you will call it with to attach that many to the query.

[buzinas]

What is the advantage of having prepared statements for inserts or updates?

By using a prepared statement, my inserts go from a few milliseconds to sub-milliseconds. Sometimes I have to insert multiple tables in sequence, so these numbers sum up.

Also, I'm using SQLite, so the less amount of time my queries lock the database, the better.

I do understand that my setup is not the most common though.

[cellulosa]

Encountered the same issue, ended up generating the id when calling the statement. Using your example would be:

upsertUserStatement.run({ id: createId(), email: 'john.doe@email.com', name: 'John Doe' })

const upsertUserStatement = db
  .insert(user)
  .values({
    id: sql.placeholder('id'),
    email: sql.placeholder('email'),
    name: sql.placeholder('name'),
  })
  .prepare()