parameterized insert and update

How does one do parameterized inserts and updates? When I try and do something like this:

const preparedUpdate = db
    .update(TaskTable)
    .set({
        title: eq(TaskTable.id, sql.placeholder('title')),
    })
    .where(eq(TaskTable.id, sql.placeholder('id')))
    .returning()
    .prepare('Task.update');

const preparedUpdate = db
    .update(TaskTable)
    .set({
        title: eq(TaskTable.id, sql.placeholder('title')),
    })
    .where(eq(TaskTable.id, sql.placeholder('id')))
    .returning()
    .prepare('Task.update');

I get "expression is of type boolean" for all the columns.

ERR PostgresError: column "title" is of type status but expression is of type boolean
   ...

ERR PostgresError: column "title" is of type status but expression is of type boolean
   ...

Is there another syntax necessary for placeholder in set? Thanks for your help.

8 Replies

enalmadaOP•16mo ago

It actually looks like this might be working:

const preparedUpdate = db
    .update(TaskTable)
    .set({
        title: sql`${sql.placeholder('title')}`,
    })
    .where(eq(TaskTable.id, sql.placeholder('id')))
    .returning()
    .prepare('Task.update');

const preparedUpdate = db
    .update(TaskTable)
    .set({
        title: sql`${sql.placeholder('title')}`,
    })
    .where(eq(TaskTable.id, sql.placeholder('id')))
    .returning()
    .prepare('Task.update');

Is that the recommended syntax or is there a more official way to avoid wrapping the placeholder in sql...?

micaww•16mo ago

are you trying to set the title column to the string "title" in your example? you're using eq inside of set. that translates to column = 'title' which is a boolean expression, which is invalid. if you're just trying to set a value, just pass the value directly: title: 'title' it will get parameterized for you you don't need to use the sql.placeholder in your "where" either, just eq(TaskTable.id, 'id')

enalmadaOP•16mo ago

Thanks @micaww for your help. I do believe the placeholder is necessary for prepared statements. When I remove sql.placeholder from the where of a prepared statement it doesn't seem to work. I was trying to follow this as close as possible. https://orm.drizzle.team/docs/performance#placeholder I assume this should be the right syntax for Insert:

const preparedInsert = db
  .insert(TaskTable)
  .values({
    dueDate: sql.placeholder('dueDate'),
  })
  .returning()
  .prepare('Task.create');

const preparedInsert = db
  .insert(TaskTable)
  .values({
    dueDate: sql.placeholder('dueDate'),
  })
  .returning()
  .prepare('Task.create');

But I get

- error Error [TypeError]: value.toISOString is not a function
    at PgTimestamp.mapToDriverValue (webpack-internal:///(rsc)/./node_modules/.pnpm/[email protected]_@[email protected][email protected][email protected]/node_modules/drizzle-orm/alias-a0c6a0a1.mjs:1363:68)

- error Error [TypeError]: value.toISOString is not a function
    at PgTimestamp.mapToDriverValue (webpack-internal:///(rsc)/./node_modules/.pnpm/[email protected]_@[email protected][email protected][email protected]/node_modules/drizzle-orm/alias-a0c6a0a1.mjs:1363:68)

And when doing update I get a typescript error on the set key dueDate.

const preparedUpdate = db
  .update(TaskTable)
  .set({
    dueDate: sql.placeholder('dueDate'),
  })
  .where(eq(TaskTable.id, sql.placeholder('id')))
  .returning()
  .prepare('Task.update');

const preparedUpdate = db
  .update(TaskTable)
  .set({
    dueDate: sql.placeholder('dueDate'),
  })
  .where(eq(TaskTable.id, sql.placeholder('id')))
  .returning()
  .prepare('Task.update');

TS2322: Type Placeholder<"dueDate", any> is not assignable to type SQL<unknown> | Date | null | undefined Type Placeholder<"dueDate", any> is missing the following properties from type SQL<unknown> : queryChunks, _, shouldInlineParams, append , and 6 more. So I think I am having two separate problems with insert and update parameterization that the following just happens to get around

sql`${sql.placeholder('dueDate')}`

sql`${sql.placeholder('dueDate')}`

Performance – DrizzleORM

Drizzle ORM | %s

Angelelz•16mo ago

I'm not sure prepared statements are supported in insert/update queries.

Angelelz•16mo ago

Actually it is: https://github.com/drizzle-team/drizzle-orm/blob/15cf2c7e9b397cc8b32a6d769866718a0fe3d789/drizzle-orm/src/pg-core/query-builders/insert.ts#L29

GitHub

drizzle-orm/drizzle-orm/src/pg-core/query-builders/insert.ts at 15c...

TypeScript ORM that feels like writing SQL. Contribute to drizzle-team/drizzle-orm development by creating an account on GitHub.

enalmadaOP•16mo ago

I was able to get around my value.toISOString is not a function problem with insert paramaterized timestamps by switching schema of timestamp to { mode: 'string' }

    
dueDate: timestamp('due_date', { mode: 'string' }).defaultNow().notNull(),

    
dueDate: timestamp('due_date', { mode: 'string' }).defaultNow().notNull(),

micaww•16mo ago

ah apologies, you're right. are you passing a Date object for that param when executing? string also works, requires you to pass a string in though. by default timestamp expects a Date instance (that it then calls toISOString on before sending to db)

enalmadaOP•16mo ago

@micaww the value.toISOString is not a function error is actually happening on page load when the statement is being parameterized before any data. I do believe I am ultimately passing in a Date object on execution but I need to confim. FYI I created this bug to track the { mode: 'Date' } issue with timestamp parameters https://github.com/drizzle-team/drizzle-orm/issues/1113 With that out of the way I think I am finding that parameterized insert doesn't respect the new default id logic (not too surprising as it is very new https://github.com/drizzle-team/drizzle-orm/releases/tag/0.28.3). I believe I can work around that by just passing my own id in.

  id: varchar('id').$defaultFn(() => `usr_${nanoid()}`).primaryKey();

  id: varchar('id').$defaultFn(() => `usr_${nanoid()}`).primaryKey();

GitHub

[BUG]: · Issue #1113 · drizzle-team/drizzle-orm

What version of drizzle-orm are you using? 0.28.5 What version of drizzle-kit are you using? 0.19.13 Describe the Bug Trying to do a sql.placeholder of a timestamp mode 'date' returns value...

Gaming

Programming

parameterized insert and update