DT
Drizzle Team•12mo ago
christrading

SQLite Query

export function updateTodo(
  userID: string,
  todoToUpdate: TodoUpdate,
): Affected {
  const { listID } = mustGetTodo(todoToUpdate.id)
  requireAccessToList(listID, userID)
  const {
    text, complete, sort, id,
  } = todoToUpdate
  const updateItemStatementQuery = db
    .update(item)
    .set({
      title: sql<string>`'coalesce(${text}, title)'`,
      complete: sql<boolean>`'coalesce(${complete}, complete)'`,
      ord: sql<number>`'coalesce(${sort}, ord)'`,
      rowVersion: sql<number>`'row_version + 1)'`,
      lastModified: new Date(),
    })
    .where(eq(item.id, id))
    .prepare()

  updateItemStatementQuery.run()

  return {
    listIDs: [listID],
    userIDs: [],
  }
}
export function updateTodo(
  userID: string,
  todoToUpdate: TodoUpdate,
): Affected {
  const { listID } = mustGetTodo(todoToUpdate.id)
  requireAccessToList(listID, userID)
  const {
    text, complete, sort, id,
  } = todoToUpdate
  const updateItemStatementQuery = db
    .update(item)
    .set({
      title: sql<string>`'coalesce(${text}, title)'`,
      complete: sql<boolean>`'coalesce(${complete}, complete)'`,
      ord: sql<number>`'coalesce(${sort}, ord)'`,
      rowVersion: sql<number>`'row_version + 1)'`,
      lastModified: new Date(),
    })
    .where(eq(item.id, id))
    .prepare()

  updateItemStatementQuery.run()

  return {
    listIDs: [listID],
    userIDs: [],
  }
}
My Drizzle + TypeScript + SQLite (better-sqlite-3) fails on updateItemStatementQuery.run(). Why is that? I have updated my fields to contain double quotes with '' as suggested before from a previous help thread.
3 Replies
christrading
christradingOP•12mo ago
I am using coalesce so it either takes the value in ${} if it's non-null value, being the string value, or the second value which is the current value for no update if it's undefined i also tried without the double ` and ' quotes
Angelelz
Angelelz•12mo ago
what is the error? There's a couple observations, that might not be related to your issue You don't need to prepare the query if you're not reusing it You don't need to wrap coalesce with quote ' You don't need to set the type of the sql, because you are not selecting, drizzle is just ignoring those types sql<string> If complete is a boolean, I think that's where it might be giving you problems, SQLite doesn't have boolean values If complete is a boolean, that database update is going to fail. Booleans are probably save to the database as numbers 0 ir 1. Make sure complete is a number if you are using sql like this
christrading
christradingOP•12mo ago
The error is 
error==
SqliteError: near ")": syntax error
    at Database.prepare (/Users/chrisren/Development/replicache-introduction-demo/node_modules/.pnpm/[email protected]/node_modules/better-sqlite3/lib/methods/wrappers.js:5:21)
    at BetterSQLiteSession.prepareQuery (webpack-internal:///(rsc)/./node_modules/.pnpm/[email protected]_@[email protected][email protected]/node_modules/drizzle-orm/better-sqlite3/session.js:29:34)
    at QueryPromise.prepare (webpack-internal:///(rsc)/./node_modules/.pnpm/[email protected]_@[email protected][email protected]/node_modules/drizzle-orm/sqlite-core/query-builders/update.js:68:85)
    at updateTodo (webpack-internal:///(rsc)/./src/app/appActions.ts:283:124)
    at mutate (webpack-internal:///(rsc)/./src/app/pushActions.ts:71:75)
    at processMutation (webpack-internal:///(rsc)/./src/app/pushActions.ts:132:24)
    at Function.eval (webpack-internal:///(rsc)/./src/app/api/replicache-push/route.ts:27:162)
error==
SqliteError: near ")": syntax error
    at Database.prepare (/Users/chrisren/Development/replicache-introduction-demo/node_modules/.pnpm/[email protected]/node_modules/better-sqlite3/lib/methods/wrappers.js:5:21)
    at BetterSQLiteSession.prepareQuery (webpack-internal:///(rsc)/./node_modules/.pnpm/[email protected]_@[email protected][email protected]/node_modules/drizzle-orm/better-sqlite3/session.js:29:34)
    at QueryPromise.prepare (webpack-internal:///(rsc)/./node_modules/.pnpm/[email protected]_@[email protected][email protected]/node_modules/drizzle-orm/sqlite-core/query-builders/update.js:68:85)
    at updateTodo (webpack-internal:///(rsc)/./src/app/appActions.ts:283:124)
    at mutate (webpack-internal:///(rsc)/./src/app/pushActions.ts:71:75)
    at processMutation (webpack-internal:///(rsc)/./src/app/pushActions.ts:132:24)
    at Function.eval (webpack-internal:///(rsc)/./src/app/api/replicache-push/route.ts:27:162)
    at Function.sqliteTransaction (/Users/chrisren/Development/replicache-introduction-demo/node_modules/.pnpm/[email protected]/node_modules/better-sqlite3/lib/methods/transaction.js:65:24)
    at BetterSQLiteSession.transaction (webpack-internal:///(rsc)/./node_modules/.pnpm/[email protected]_@[email protected][email protected]/node_modules/drizzle-orm/better-sqlite3/session.js:35:55)
    at BaseSQLiteDatabase.transaction (webpack-internal:///(rsc)/./node_modules/.pnpm/[email protected]_@[email protected][email protected]/node_modules/drizzle-orm/sqlite-core/db.js:135:29)
    at eval (webpack-internal:///(rsc)/./src/app/api/replicache-push/route.ts:27:81)
    at Array.forEach (<anonymous>)
    at POST (webpack-internal:///(rsc)/./src/app/api/replicache-push/route.ts:24:24)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async /Users/chrisren/Development/replicache-introduction-demo/node_modules/.pnpm/[email protected]_@[email protected][email protected][email protected]/node_modules/next/dist/compiled/next-server/app-route.runtime.dev.js:6:63251 {
  code: 'SQLITE_ERROR'
}
    at Function.sqliteTransaction (/Users/chrisren/Development/replicache-introduction-demo/node_modules/.pnpm/[email protected]/node_modules/better-sqlite3/lib/methods/transaction.js:65:24)
    at BetterSQLiteSession.transaction (webpack-internal:///(rsc)/./node_modules/.pnpm/[email protected]_@[email protected][email protected]/node_modules/drizzle-orm/better-sqlite3/session.js:35:55)
    at BaseSQLiteDatabase.transaction (webpack-internal:///(rsc)/./node_modules/.pnpm/[email protected]_@[email protected][email protected]/node_modules/drizzle-orm/sqlite-core/db.js:135:29)
    at eval (webpack-internal:///(rsc)/./src/app/api/replicache-push/route.ts:27:81)
    at Array.forEach (<anonymous>)
    at POST (webpack-internal:///(rsc)/./src/app/api/replicache-push/route.ts:24:24)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async /Users/chrisren/Development/replicache-introduction-demo/node_modules/.pnpm/[email protected]_@[email protected][email protected][email protected]/node_modules/next/dist/compiled/next-server/app-route.runtime.dev.js:6:63251 {
  code: 'SQLITE_ERROR'
}
complete is a boolean. this is my schema for the table 
export const item = sqliteTable('item', {
  id: text('id').primaryKey().notNull(),
  listID: text('list_id').notNull(),
  title: text('title').notNull(),
  complete: integer('complete', { mode: 'boolean' }).notNull(),
  ord: integer('ord').notNull(),
  rowVersion: integer('row_version').notNull(),
  lastModified: integer('last_modified', { mode: 'timestamp_ms' }).notNull(),
})
export const item = sqliteTable('item', {
  id: text('id').primaryKey().notNull(),
  listID: text('list_id').notNull(),
  title: text('title').notNull(),
  complete: integer('complete', { mode: 'boolean' }).notNull(),
  ord: integer('ord').notNull(),
  rowVersion: integer('row_version').notNull(),
  lastModified: integer('last_modified', { mode: 'timestamp_ms' }).notNull(),
})
I tried each set field separately. 
complete: sql<boolean>`'coalesce(${complete}, complete)'`,
complete: sql<boolean>`'coalesce(${complete}, complete)'`,
and 
rowVersion: sql<number>`'row_version + 1)'`,
rowVersion: sql<number>`'row_version + 1)'`,
both fail and give that same error In the schema the exported inferred type still has it as a boolean 
export const item = sqliteTable('item', {
  id: text('id').primaryKey().notNull(),
  listID: text('list_id').notNull(),
  title: text('title').notNull(),
  complete: integer('complete', { mode: 'boolean' }).notNull(),
  ord: integer('ord').notNull(),
  rowVersion: integer('row_version').notNull(),
  lastModified: integer('last_modified', { mode: 'timestamp_ms' }).notNull(),
})

export type Item = InferSelectModel<typeof item>
export const item = sqliteTable('item', {
  id: text('id').primaryKey().notNull(),
  listID: text('list_id').notNull(),
  title: text('title').notNull(),
  complete: integer('complete', { mode: 'boolean' }).notNull(),
  ord: integer('ord').notNull(),
  rowVersion: integer('row_version').notNull(),
  lastModified: integer('last_modified', { mode: 'timestamp_ms' }).notNull(),
})

export type Item = InferSelectModel<typeof item>
Inspecting type Item type Item = { id: string; rowVersion: number; lastModified: Date; listID: string; title: string; complete: boolean; ord: number; } So how do I have complete be inserted as a boolean to satisfiy the typescript types but insert it as a number to satisfy SQLite taking booleans as integers?
Want results from more Discord servers?
Add your server