Drizzle Team•8mo ago

Issue Querying Table via Drizzle ORM with SQLite

Hello Drizzle Community, I'm currently facing an issue with querying the tbl_workshops table in a SQLite database using Drizzle ORM. Despite confirming that the table exists and is properly structured (I can query it directly using SQLite tools), attempts to query it through Drizzle result in an error indicating that the table doesn't exist. Error Message:

SQLITE_UNKNOWN: SQLite error: no such table: tbl_workshops

SQLITE_UNKNOWN: SQLite error: no such table: tbl_workshops

Environment: - ORM Version: Drizzle ORM v0.29.5 - Database: SQLite - Bun Version: Bun v1.1.0 - Operating System: win11 Code Snippet: Here's a simplified version of how I'm setting up Drizzle and attempting to query the tbl_workshops table:

// db.ts
import { createClient } from "@libsql/client/http";
import { drizzle } from "drizzle-orm/libsql";
import { env } from "../env.mjs";
import * as schema from "./schema";

const client = createClient({
  url: env.DATABASE_URL,
  authToken: env.DATABASE_AUTH_TOKEN,
});

export const db = drizzle(client, { schema, logger: false });

// Query attempt
const classData = await db.query.tbl_workshops.findMany({});

// db.ts
import { createClient } from "@libsql/client/http";
import { drizzle } from "drizzle-orm/libsql";
import { env } from "../env.mjs";
import * as schema from "./schema";

const client = createClient({
  url: env.DATABASE_URL,
  authToken: env.DATABASE_AUTH_TOKEN,
});

export const db = drizzle(client, { schema, logger: false });

// Query attempt
const classData = await db.query.tbl_workshops.findMany({});

I've already verified the following: - The DATABASE_URL environment variable correctly points to the SQLite database file. - The table name in the schema matches the actual table name in the database. - I can manually query the tbl_workshops table using SQLite command-line tools or GUIs. I'm puzzled about what could be causing this issue and would greatly appreciate any insights or suggestions from the community. Has anyone encountered a similar problem, or does anyone have ideas on what might be going wrong or how to troubleshoot this further? Thank you in advance for your time and help!

9 Replies

Mykhailo•8mo ago

Hello, @Jackson Kasi! Thank you very much for detailed description of your issue! Did it work with select() syntax? Could you please provide your raw sql query that you execute against SQLite database with cli and get sql query from your actual query?

db.query.tbl_workshops.findMany({}).toSQL() // like this

db.query.tbl_workshops.findMany({}).toSQL() // like this

Jackson KasiOP•8mo ago

Hello, @Mykhailo and thank you for the response! I've tried querying the tbl_workshops table in two different ways using Drizzle ORM, but unfortunately, I'm still facing the same issue. 1. Using a more direct select method provided by Drizzle ORM:

const classData = await db
  .select()
  .from(tbl_workshops)
  .limit(input.limit)
  .offset(input.page * input.limit)
  .execute();

const classData = await db
  .select()
  .from(tbl_workshops)
  .limit(input.limit)
  .offset(input.page * input.limit)
  .execute();

2. Using the findMany method with limit and offset options:

const classData = await db.query.tbl_workshops.findMany({
  limit: input.limit,
  offset: input.page * input.limit,
});

const classData = await db.query.tbl_workshops.findMany({
  limit: input.limit,
  offset: input.page * input.limit,
});

Both methods result in an error message indicating that the tbl_workshops table doesn't exist Here's how I'm calling the .toSQL() to inspect the generated SQL query

const query = db
  .select()
  .from(tbl_workshops)
  .limit(input.limit)
  .offset(input.page * input.limit)
  .toSQL();

console.log(query);

const query = db
  .select()
  .from(tbl_workshops)
  .limit(input.limit)
  .offset(input.page * input.limit)
  .toSQL();

console.log(query);

I've inspected the SQL query generated by Drizzle ORM, and here are the details:

{
  "sql": "select \"id\", \"workshop_id\", \"workshop_title\", \"description\", \"start_date\", \"start_and_end_time\", \"timezone\", \"trainer\", \"thumbnail\", \"created_at\", \"updated_at\" from \"tbl_workshops\" limit ?",
  "params": [ 10 ]
}

{
  "sql": "select \"id\", \"workshop_id\", \"workshop_title\", \"description\", \"start_date\", \"start_and_end_time\", \"timezone\", \"trainer\", \"thumbnail\", \"created_at\", \"updated_at\" from \"tbl_workshops\" limit ?",
  "params": [ 10 ]
}

import { db, eq } from "@repo/db";
import { tbl_workshops } from "@repo/db/src/schema";
import { createInsertSchema, createSelectSchema } from "drizzle-zod";
import { z } from "zod";
import { createTRPCRouter, publicProcedure } from "../trpc";

export const workshopRouter = createTRPCRouter({


  get: publicProcedure
    .input(z.object({ limit: z.number(), page: z.number() }))
    .query(async ({ ctx, input }) => {
      try {
        const classData = await db
          .select()
          .from(tbl_workshops)
          .limit(input.limit)
          .offset(input.page * input.limit)
          .execute();

        // const classData = await db.query.tbl_workshops.findMany({
        //   limit: input.limit,
        //   offset: input.page * input.limit,
        // });

        console.log(classData);

        return {
          success: true,
          message: "Successfully fetched workshop",
          data: classData,
        };
      } catch (error: any) {
        console.log(error.message);
        return {
          success: false,
          message: `Error to fetch API
        ${process.env.NODE_ENV !== "production" ? `${error.message}` : ""} `,
        };
      }
    }),
});

import { db, eq } from "@repo/db";
import { tbl_workshops } from "@repo/db/src/schema";
import { createInsertSchema, createSelectSchema } from "drizzle-zod";
import { z } from "zod";
import { createTRPCRouter, publicProcedure } from "../trpc";

export const workshopRouter = createTRPCRouter({


  get: publicProcedure
    .input(z.object({ limit: z.number(), page: z.number() }))
    .query(async ({ ctx, input }) => {
      try {
        const classData = await db
          .select()
          .from(tbl_workshops)
          .limit(input.limit)
          .offset(input.page * input.limit)
          .execute();

        // const classData = await db.query.tbl_workshops.findMany({
        //   limit: input.limit,
        //   offset: input.page * input.limit,
        // });

        console.log(classData);

        return {
          success: true,
          message: "Successfully fetched workshop",
          data: classData,
        };
      } catch (error: any) {
        console.log(error.message);
        return {
          success: false,
          message: `Error to fetch API
        ${process.env.NODE_ENV !== "production" ? `${error.message}` : ""} `,
        };
      }
    }),
});

Hi @Mykhailo, Can I set up the base code, make it, and then publish it in a public repository? I will share it with you to help me with this issue.

Mykhailo•8mo ago

Would be great! Do you use turso db?

Jackson KasiOP•8mo ago

yes Even the database key, I will add it in the code! Later, I will remove the Turso DB from my account.

Mykhailo•8mo ago

Are these the correct steps to reproduce the issue, or did I miss something? 1. Create turso db 2. Create drizzle schema 3. Push changes 4. Connect to turso db with drizzle 5. Execute query no worries, I can use my own turso db

Jackson KasiOP•8mo ago

Yes, those are the correct steps to reproduce the issue. okay then i will share the code soon!

Jackson KasiOP•8mo ago

Hi @Mykhailo Just wanted to let you know that in the base version, I didn't encounter any issues. However, in my full version of the code, I'm facing an issue. I may have made an error in the TRPC config or somewhere else. I will try to fix the issue. Thanks for your support.

Mykhailo•8mo ago

Super!

Jackson KasiOP•8mo ago

and this is the base code: https://github.com/jacksonkasi1/next-trpc-drizzle-turso

GitHub

GitHub - jacksonkasi1/next-trpc-drizzle-turso

Contribute to jacksonkasi1/next-trpc-drizzle-turso development by creating an account on GitHub.

Gaming

Programming

Issue Querying Table via Drizzle ORM with SQLite