[BUG]: using placeholders for inserts in sqlite doesn't work for types that transform values

[gmaclennan]

What version of drizzle-orm are you using?

0.27.2

What version of drizzle-kit are you using?

0.19.12

Describe the Bug

If I try to use a placeholder to insert values for a custom type, the value inserted is incorrect.

The same custom type works fine when used in a (non-prepared) insert.

e.g. given this schema:

import { integer, sqliteTable, customType } from 'drizzle-orm/sqlite-core'

const textArray = customType({
    dataType() { return 'text' },
    fromDriver(value) { return JSON.parse(value) },
    toDriver(value) { return JSON.stringify(value) },
})

export const table = sqliteTable('table', {
  list: textArray('list')
})

The following returns { list: { name: 'list' } } instead of { list: [] }:

const stmt = db
  .insert(table)
  .values({ list: placeholder('list') })
  .prepare()

stmt.run({ list: [] })
console.log(db.select().from(table).get())

However a normal insert (without prepare and placeholders) works fine:

db.insert(table).values({ list: [] }).run()
console.log(db.select().from(table).get())
// works as expected
// { list: [] }

What I can see is happening is that when valued() is called, the toDriver() function of my customType is called with a Placeholder instance. JSON.stringify() is being called on the Placeholder instance, which returns { name: 'list' }, which is what is being written to the database.

Minimal reproduction code: https://github.com/gmaclennan/drizzle-placeholder-bug/blob/main/index.js

Expected behavior

I would expect placeholders to work with custom fields in sqlite

Environment & setup

Running on MacOS, M2, Node v16.20.0, using better-sqlite3 as the driver.

[gmaclennan]

This same issue also applies when using a type blob('name', { mode: 'json' | 'bigint' }), or type integer('name', { mode: 'timestamp'). The issue is the same as above - toDriver() is being called with a Placeholder instance, and trying to act on it (resulting in incorrect data or an error).

[filipsobol]

I encountered the same problem when trying to create a prepared statement for the timestamp_ms column.

// Schema
export const sessions = sqliteTable('sessions', {
  // Skipped
  expiresAt: integer('expiresAt', { mode: 'timestamp_ms' }).notNull(),
});

// Prepared statement
export const queryInsertSession = db
  .insert(sessions)
  .values({
    // Skipped
    expiresAt: placeholder('expiresAt'),
  })
  .returning()
  .prepare();

This results in the following error:

TypeError: value.getTime is not a function
    at SQLiteTimestamp.mapToDriverValue (<PATH>/src/sqlite-core/columns/integer.ts:151:19)

Direct query works fine:

const session = db
  .insert(sessions)
  .values({
    // Skipped
    expiresAt: new Date(Date.now() + MILLISECONDS_IN_A_YEAR)
  })
  .returning()
  .get();

[Angelelz]

I discussed this briefly with a member of the team, an easy workaround is to do the transformation yourself in the execute function:

const stmt = db
  .insert(table)
  .values({ list: placeholder('list') })
  .prepare()

stmt.run({ list: table.list.mapToDriverValue([]) })

[ntriledev]

This workaround does not work for me

[Acorn221]

Hey, the workaround has worked for me but it would be amazing if this the prepared statements just worked as expected so I could use them in my code without having to worry about transforming