Fail to cast boolean column: SQLite3 can only bind numbers, strings, bigints, buffers, and null

[leosuncin]

I got that error because one table has a date boolean column, the following is the code that I'm using

import type { ColumnType, Generated, Insertable, Selectable } from 'kysely';

export interface TaskTable {
  id: Generated<string>;
  title: string;
  completed: boolean;
  createdAt: ColumnType<Date, string | undefined, never>;
}

export type Task = Selectable<TaskTable>;

export type CreateTask = Pick<Insertable<TaskTable>, 'title'>

import Database from 'better-sqlite3';
import { Kysely, SqliteDialect } from 'kysely';

export interface DB {
  tasks: TaskTable;
}

export const db = new Kysely<DB>({
  dialect: new SqliteDialect({
    database: new Database('kysely.db'),
  }),
});

import { randomUUID } from 'node:crypto';

export class TaskService {
  constructor(private readonly db: Kysely<DB>) {}

  async create(newTask: CreateTask): Promise<Task> {
    const task = await this.db /* The error was thrown here */
      .insertInto('tasks')
      .values({
        id: randomUUID(),
        title: newTask.title,
        completed: false,
      })
      .returningAll()
      .executeTakeFirstOrThrow();

    return task;
  }

  async findAll(): Promise<Task[]> {
    return this.db.selectFrom('tasks').selectAll().execute();
  }
}

-- The DDL
CREATE TABLE tasks (
    id VARCHAR (36) PRIMARY KEY NOT NULL,
    title TEXT CHECK (ifnull(length(title), 0) > 0) NOT NULL,
    completed BOOLEAN DEFAULT (false) NOT NULL,
    createdAt DATETIME DEFAULT (datetime('now', 'localtime')) NOT NULL
);

Stacktrace

TypeError: SQLite3 can only bind numbers, strings, bigints, buffers, and null
    at SqliteConnection.executeQuery (/home/user/experimenting-with-kysely/node_modules/.pnpm/kysely@0.19.12/node_modules/kysely/dist/cjs/dialect/sqlite/sqlite-driver.js:56:28)
    at /home/user/experimenting-with-kysely/node_modules/.pnpm/kysely@0.19.12/node_modules/kysely/dist/cjs/query-executor/query-executor-base.js:36:45
    at DefaultConnectionProvider.provideConnection (/home/user/experimenting-with-kysely/node_modules/.pnpm/kysely@0.19.12/node_modules/kysely/dist/cjs/driver/default-connection-provider.js:12:26)
    at processTicksAndRejections (node:internal/process/task_queues:96:5)
    at async DefaultQueryExecutor.executeQuery (/home/user/experimenting-with-kysely/node_modules/.pnpm/kysely@0.19.12/node_modules/kysely/dist/cjs/query-executor/query-executor-base.js:35:16)
    at async InsertQueryBuilder.execute (/home/user/experimenting-with-kysely/node_modules/.pnpm/kysely@0.19.12/node_modules/kysely/dist/cjs/query-builder/insert-query-builder.js:418:24)
    at async InsertQueryBuilder.executeTakeFirst (/home/user/experimenting-with-kysely/node_modules/.pnpm/kysely@0.19.12/node_modules/kysely/dist/cjs/query-builder/insert-query-builder.js:431:26)
    at async InsertQueryBuilder.executeTakeFirstOrThrow (/home/user/experimenting-with-kysely/node_modules/.pnpm/kysely@0.19.12/node_modules/kysely/dist/cjs/query-builder/insert-query-builder.js:443:24)
    at async TaskService.create (/home/user/experimenting-with-kysely/src/services/TaskService.ts:12:18)
    at async TaskController.create (/home/user/experimenting-with-kysely/src/controllers/TaskController.ts:10:18)

Is this error caused by a limitation of the driver?

• Yes

how can I cast the boolean to number and back to boolean using Kysely?

Dependency versions

better-sqlite3 v7.6.2
kysely v0.19.12
typescript v4.7.4

OS

Manjaro 21.3.4 x86_64
Node.js 16.15.1

[leosuncin]

Update: it wasn't the date column, it was the boolean column.

The error is related to WiseLibs/better-sqlite3#258

Could be possible to implemented an automatic casting mechanism inside Kysely?

[koskimas]

It's possible to some extent but shouldn't be done inside Kysely. The reason is the solution involves loading the database metadata (to know which columns are bool) and a query builder shouldn't run unexpected queries in the background.

We could provide an optional plugin, but implementing it in a generic way is hard. Consider this query:

select aa.x as y from b inner join a as aa on aa.id = b.id

The plugin would need to figure out where the y column in the result comes from to be able to search the metadata for its data type. This is a simple example, but the plugin would need to work with all possible queries (joined nested subqueries etc.).

The plugin also wouldn't work in all cases. Any raw SQL in a select/from/join statement would break the data type inference since kysely doesn't have an SQL parser.

[igalklebanov]

Had similar concerns with in-and-out casting.

Was looking into adding a plugin, saw it'll require too much work.

Ended up using zod to validate, omit unknown/unwanted keys and transform inputs and results. Fed kysely with the inferred types of what the database driver expects as values.

import { Kysely } from 'kysely'
import { z } from 'zod'

const taskWriteSchema = z
    .object({ /* define your javascript columns here, without generated columns */ })
    .transform(value => { /* do your casting here so the driver won't yell at you */ })

const taskReadSchema = taskWriteSchema
    .extend({ /* define columns you've casted in write schema and generated columns */ })
    .transform(value => { /* do your casting here so you can work with what the driver returned with ease */ })

export type WritableTask = z.input<typeof taskWriteSchema>
export type PersistedTask = z.input<typeof taskReadSchema>
export type Task = z.output<typeof taskReadSchema>

export type Database = {
  tasks: PersistedTask
}

const db = new Kysely<Database>(...)

async function insert(tasks: WriteableTask[]): Promise<void> {
    await db.insertInto('tasks').values(tasks.map(task => taskWriteSchema.parse(task))).execute()
}

async function readOne(id: Task['id']): Promise<void> {
    const result = await db.selectFrom('tasks').where('id', '=', id).selectAll().executeTakeFirst()

   return taskReadSchema.parse(result);
}

Transforming the entire object is not required, you can also transform per column.

[koskimas]

I considered adding the plugin, but I have to agree with @igalklebanov that it's better to do the mapping outside kysely. A generic plugin would require too much work and have too many broken corner cases.

You could create your own plugin that could work nicely in your use cases. The plugin interface is simple and there are a couple of plugins you can use as a starting point here.

[koskimas]

This already does the conversion in the other direction:

export class SqliteBooleanPlugin implements KyselyPlugin {
  readonly #transformer = new SqliteBooleanTransformer()

  transformQuery(args: PluginTransformQueryArgs): RootOperationNode {
    return this.#transformer.transformNode(args.node)
  }

  transformResult(
    args: PluginTransformResultArgs
  ): Promise<QueryResult<UnknownRow>> {
    return Promise.resolve(args.result)
  }
}

class SqliteBooleanTransformer extends OperationNodeTransformer {
  transformValue(node: ValueNode): ValueNode {
    return {
      ...super.transformValue(node),
      value: typeof node.value === 'boolean' ? (node.value ? 1 : 0) : node.value
    }
  }
}

The other direction is the tricky one.

[steida]

@igalklebanov I use branded types SqliteBoolean and SqliteDateTime with cast helper.

/**
 * SQLite has no Boolean datatype. Use cast(true|false|SqliteBoolean).
 * https://www.sqlite.org/quirks.html#no_separate_boolean_datatype
 */
export const SqliteBoolean = z
  .number()
  .refine(brand("SqliteBoolean", (n) => n === 0 || n === 1));
export type SqliteBoolean = z.infer<typeof SqliteBoolean>;

/**
 * SQLite has no DateTime datatype. Use cast(new Date()|SqliteDateTime).
 * https://www.sqlite.org/quirks.html#no_separate_datetime_datatype
 */
export const SqliteDateTime = z
  .string()
  .refine(brand("SqliteDateTime", (s) => !isNaN(new Date(s).getTime())));
export type SqliteDateTime = z.infer<typeof SqliteDateTime>;

export function cast(value: boolean): SqliteBoolean;
export function cast(value: SqliteBoolean): boolean;
export function cast(value: Date): SqliteDateTime;
export function cast(value: SqliteDateTime): Date;
export function cast(
  value: boolean | SqliteBoolean | Date | SqliteDateTime
): boolean | SqliteBoolean | Date | SqliteDateTime {
  if (typeof value === "boolean")
    return (value === true ? 1 : 0) as SqliteBoolean;
  if (typeof value === "number") return value === 1;
  if (value instanceof Date) return value.toISOString() as SqliteDateTime;
  return new Date(value);
}

[nikeee]

I'm facing similar issues with querying against a Date field in an SQLite DB. Anyone has a solution for that?
As I'm running Kysely on two different DBMs (Sqlite and PG), using the suggested branded types won't work for me. Conditionally including a plugin based on the used dialect seems the right thing to do.
Does the posted plugin still serve as a reference for one side of the conversion?

[mphill]

@koskimas It would be great to hear your opinion on this approach: https://github.com/mphill/kysely-expo#date-and-boolean-support

It's a little bit of magic, but it's also straightforward.

@nikeee you may be interested too.

[voinik]

I ran into this issue just now as well since I swapped from PlanetScale to Turso (which uses libSQL, which is a fork of SQLite).
They have their own Kysely driver called @libsql/kysely-libsql. Luckily, libSQL has column data of the result data set, including the respective type of the selected columns in the database. However, Kysely only allows a few specific properties to be passed down through a Dialect (determined by QueryResult<O>, which contains the properties rows, numAffectedRows, numChangedRows and insertId).

I decided to fork their driver and secretly add another property: columnDecltypes. This is the column type information that libSQL provides. Here's the fork I made: @voinik/kysely-libsql. Check it out for instructions on how to use it.

I then wrote an interface override to allow that property in QueryResult. I could then finally write a plugin that does the translation. It looks like this:

import { type KyselyPlugin } from 'kysely';

export const sqliteBooleanPlugin: KyselyPlugin = {
    transformQuery: args => args.node,
    // eslint-disable-next-line @typescript-eslint/require-await
    transformResult: async args => {
        const indices = [];
        for (let i = 0; i < args.result.columnDecltypes.length; i++) {
            if (args.result.columnDecltypes[i] === 'boolean') {
                indices.push(i);
            }
        }

        if (indices.length === 0) {
            return args.result;
        }

        for (const row of args.result.rows) {
            const keys = Object.keys(row);
            for (const index of indices) {
                row[keys[index]!] = Boolean(row[keys[index]!]);
            }
        }

        return args.result;
    },
};

And it seems to be working! The fork README includes instructions on how to accomplish it from scratch.
I wrote the plugin to have as little overhead as I could manage. Perhaps someone can improve its performance somehow.

Hope this helps someone!