"No Such Column" Error

I'm using TypeScript, DrizzleORM, and SQLite (better-sqlite-3)

export function searchTodosAndShares(listIDs: string[]) {
  if (listIDs.length === 0) return { shareMeta: [], todoMeta: [] }

  const shareStatementQuery = db
    .select({
      id: share.id,
      rowVersion: share.rowVersion,
      type: sql<string>`share`,
    })
    .from(share)
    .innerJoin(list, eq(share.listID, list.id))
    .where(inArray(list.id, listIDs))

  const todoStatementQuery = db
    .select({
      id: item.id,
      rowVersion: item.rowVersion,
      type: sql<string>`todo`,
    })
    .from(item)
    .where(inArray(list.id, listIDs))

  const sharesAndTodos = unionAll(
    shareStatementQuery,
    todoStatementQuery,
  )
    .prepare()
    .all()

  const result: {
    shareMeta: { id: string; rowVersion: number; }[],
    todoMeta: { id: string; rowVersion: number; }[] } = {
    shareMeta: [],
    todoMeta: [],
  }

  sharesAndTodos.forEach((row) => {
    const { id, rowVersion, type } = row
    result[type as 'shareMeta' | 'todoMeta'].push({ id, rowVersion })
  })

  return result
}

export function searchTodosAndShares(listIDs: string[]) {
  if (listIDs.length === 0) return { shareMeta: [], todoMeta: [] }

  const shareStatementQuery = db
    .select({
      id: share.id,
      rowVersion: share.rowVersion,
      type: sql<string>`share`,
    })
    .from(share)
    .innerJoin(list, eq(share.listID, list.id))
    .where(inArray(list.id, listIDs))

  const todoStatementQuery = db
    .select({
      id: item.id,
      rowVersion: item.rowVersion,
      type: sql<string>`todo`,
    })
    .from(item)
    .where(inArray(list.id, listIDs))

  const sharesAndTodos = unionAll(
    shareStatementQuery,
    todoStatementQuery,
  )
    .prepare()
    .all()

  const result: {
    shareMeta: { id: string; rowVersion: number; }[],
    todoMeta: { id: string; rowVersion: number; }[] } = {
    shareMeta: [],
    todoMeta: [],
  }

  sharesAndTodos.forEach((row) => {
    const { id, rowVersion, type } = row
    result[type as 'shareMeta' | 'todoMeta'].push({ id, rowVersion })
  })

  return result
}

How come I get error of no such column: share or no such column: todo? Thanks in advance for the help 🙂 🙏

12 Replies

Mykhailo•12mo ago

Hello @christrading! Could you please provide your schema code? This error occurs when you don't have these columns in your schema.

Angelelz•12mo ago

Your typescript schema might not be in sync with your database You didn't forget to push or migrate?

christradingOP•12mo ago

This is my drizzle/schema.ts file

import type { InferSelectModel, InferInsertModel } from 'drizzle-orm'
import {
  sqliteTable, text, integer, blob,
} from 'drizzle-orm/sqlite-core'

export const replicacheMeta = sqliteTable('replicache_meta', {
  key: text('key').primaryKey(),
  value: blob('value', { mode: 'json' }),
})

export type ReplicacheMeta = InferSelectModel<typeof replicacheMeta>
export type InsertReplicacheMeta = InferInsertModel<typeof replicacheMeta>

// Stores last mutationID processed for each Replicache client.
export const replicacheClient = sqliteTable('replicache_client', {
  id: text('id').primaryKey().notNull(),
  clientGroupID: text('client_group_id').notNull(),
  lastMutationID: integer('last_mutation_id').notNull(),
  clientVersion: integer('version').notNull(),
  lastModified: integer('last_modified', { mode: 'timestamp_ms' }).notNull(),
})

export type ReplicacheClient = InferSelectModel<typeof replicacheClient>
export type InsertReplicacheClient = InferInsertModel<typeof replicacheClient>

// cvrversion is null until first pull initializes it.
export const replicacheClientGroup = sqliteTable('replicache_client_group', {
  id: text('id').primaryKey().notNull(),
  cvrVersion: integer('cvr_version'),
  clientGroupVersion: integer('client_group_version').notNull(),
  lastModified: integer('last_modified', { mode: 'timestamp_ms' }).notNull(),
})

export type ReplicacheClientGroup = InferSelectModel<typeof replicacheClientGroup>
export type InsertReplicacheClientGroup = InferInsertModel<typeof replicacheClientGroup>

import type { InferSelectModel, InferInsertModel } from 'drizzle-orm'
import {
  sqliteTable, text, integer, blob,
} from 'drizzle-orm/sqlite-core'

export const replicacheMeta = sqliteTable('replicache_meta', {
  key: text('key').primaryKey(),
  value: blob('value', { mode: 'json' }),
})

export type ReplicacheMeta = InferSelectModel<typeof replicacheMeta>
export type InsertReplicacheMeta = InferInsertModel<typeof replicacheMeta>

// Stores last mutationID processed for each Replicache client.
export const replicacheClient = sqliteTable('replicache_client', {
  id: text('id').primaryKey().notNull(),
  clientGroupID: text('client_group_id').notNull(),
  lastMutationID: integer('last_mutation_id').notNull(),
  clientVersion: integer('version').notNull(),
  lastModified: integer('last_modified', { mode: 'timestamp_ms' }).notNull(),
})

export type ReplicacheClient = InferSelectModel<typeof replicacheClient>
export type InsertReplicacheClient = InferInsertModel<typeof replicacheClient>

// cvrversion is null until first pull initializes it.
export const replicacheClientGroup = sqliteTable('replicache_client_group', {
  id: text('id').primaryKey().notNull(),
  cvrVersion: integer('cvr_version'),
  clientGroupVersion: integer('client_group_version').notNull(),
  lastModified: integer('last_modified', { mode: 'timestamp_ms' }).notNull(),
})

export type ReplicacheClientGroup = InferSelectModel<typeof replicacheClientGroup>
export type InsertReplicacheClientGroup = InferInsertModel<typeof replicacheClientGroup>

continued

// Application domain entities

export const list = sqliteTable('list', {
  id: text('id').primaryKey().notNull(),
  ownerID: text('owner_id').notNull(),
  name: text('name').notNull(),
  rowVersion: integer('row_version').notNull(),
  lastModified: integer('last_modified', { mode: 'timestamp_ms' }).notNull(),
})

export type List = InferSelectModel<typeof list>
export type InsertList = InferInsertModel<typeof list>

export const share = sqliteTable('share', {
  id: text('id').primaryKey().notNull(),
  listID: text('list_id').notNull(),
  userID: text('user_id').notNull(),
  rowVersion: integer('row_version').notNull(),
  lastModified: integer('last_modified', { mode: 'timestamp_ms' }).notNull(),
})

export type Share = InferSelectModel<typeof share>
export type InsertShare = InferInsertModel<typeof share>

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 type InsertItem = InferInsertModel<typeof item>

// Application domain entities

export const list = sqliteTable('list', {
  id: text('id').primaryKey().notNull(),
  ownerID: text('owner_id').notNull(),
  name: text('name').notNull(),
  rowVersion: integer('row_version').notNull(),
  lastModified: integer('last_modified', { mode: 'timestamp_ms' }).notNull(),
})

export type List = InferSelectModel<typeof list>
export type InsertList = InferInsertModel<typeof list>

export const share = sqliteTable('share', {
  id: text('id').primaryKey().notNull(),
  listID: text('list_id').notNull(),
  userID: text('user_id').notNull(),
  rowVersion: integer('row_version').notNull(),
  lastModified: integer('last_modified', { mode: 'timestamp_ms' }).notNull(),
})

export type Share = InferSelectModel<typeof share>
export type InsertShare = InferInsertModel<typeof share>

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 type InsertItem = InferInsertModel<typeof item>

christradingOP•12mo ago

Drizzle Studio shows that I have these tables created

Angelelz•12mo ago

Dirzzle studio is based on your schema in typescript You could run raw queries to see what's on your tables

Mykhailo•12mo ago

Perhaps I am mistaken, but in the select statement, you are specifying the column share from the share table. But you don't have such a column. The same goes for todo

Angelelz•12mo ago

Oh yeah, you're right @solo , Are you trying to select a constant or a column??

christradingOP•12mo ago

I am trying to select the columns id and rowVersion from the share table and adding a column to the select result of key type value share. And then similarly for the todo table. This is so I can distinguish the returned select results between the share and todo tables I am adding the column to the return result in the way that Dan Kochetov suggested https://discord.com/channels/1043890932593987624/1043890932593987627/1172440868544860192

Angelelz•12mo ago

I think that for SQLite you need to wrap the string in double quotes " or single quotes ', you'll have to test

  const shareStatementQuery = db
    .select({
      id: share.id,
      rowVersion: share.rowVersion,
      type: sql<string>`"share"`,
    })
    .from(share)
    .innerJoin(list, eq(share.listID, list.id))
    .where(inArray(list.id, listIDs))

  const shareStatementQuery = db
    .select({
      id: share.id,
      rowVersion: share.rowVersion,
      type: sql<string>`"share"`,
    })
    .from(share)
    .innerJoin(list, eq(share.listID, list.id))
    .where(inArray(list.id, listIDs))

I don't have a way to test right now but I believe that should work. Or the single quotes

christradingOP•12mo ago

Ah, yes. Wrapping the string with single-quotes on the inside worked

christradingOP•12mo ago

🙂 🙏 Thank you, Solo and Angelez! This wasn't explicit in the documentation (among a number of other things I've worked through figuring out). Or is there another source of docs that I'm not aware of? Are the docs able to be expanded to explain a bit more comprehensively? Is it open to open-source contributions?

Angelelz•12mo ago

The team is working on updating the docs and guides, yes

Gaming

Programming

"No Such Column" Error