hirefrank Posts - Answer Overflow

hirefrank

Explore posts from servers

•Created by hirefrank on 8/13/2024 in #help

Drizzle + Turso + Embeddings?

Is there support for turso/sqlite for embeddings and indexes similar to: https://orm.drizzle.team/learn/guides/vector-similarity-search

2 replies

DTDrizzle Team

•Created by hirefrank on 4/26/2024 in #help

SQL many statements?

Any ideas on how to debug this?

Task migrations deno run -A src/lib/db/migrations.ts
error: Uncaught (in promise) LibsqlError: SQL_MANY_STATEMENTS: SQL string contains more than one statement
    at mapHranaError (file:///Users/fharris/Projects/jobasaurus-crawler/node_modules/.deno/@[email protected]/node_modules/@libsql/client/lib-esm/hrana.js:257:16)
    at HttpTransaction.batch (file:///Users/fharris/Projects/jobasaurus-crawler/node_modules/.deno/@[email protected]/node_modules/@libsql/client/lib-esm/hrana.js:105:19)
    at Object.runMicrotasks (ext:core/01_core.js:642:26)
    at processTicksAndRejections (ext:deno_node/_next_tick.ts:39:10)
    at runNextTicks (ext:deno_node/_next_tick.ts:48:3)
    at eventLoopTick (ext:core/01_core.js:175:21)
    at async file:///Users/fharris/Projects/jobasaurus-crawler/node_modules/.deno/[email protected]/node_modules/drizzle-orm/session-afae3551.mjs:926:25
    at async LibSQLSession.transaction (file:///Users/fharris/Projects/jobasaurus-crawler/node_modules/.deno/[email protected]/node_modules/drizzle-orm/libsql/index.mjs:86:28)
    at async SQLiteAsyncDialect.migrate (file:///Users/fharris/Projects/jobasaurus-crawler/node_modules/.deno/[email protected]/node_modules/drizzle-orm/session-afae3551.mjs:922:9)
    at async file:///Users/fharris/Projects/jobasaurus-crawler/src/lib/db/migrations.ts:4:1
Caused by: ResponseError: SQL string contains more than one statement

Task migrations deno run -A src/lib/db/migrations.ts
error: Uncaught (in promise) LibsqlError: SQL_MANY_STATEMENTS: SQL string contains more than one statement
    at mapHranaError (file:///Users/fharris/Projects/jobasaurus-crawler/node_modules/.deno/@[email protected]/node_modules/@libsql/client/lib-esm/hrana.js:257:16)
    at HttpTransaction.batch (file:///Users/fharris/Projects/jobasaurus-crawler/node_modules/.deno/@[email protected]/node_modules/@libsql/client/lib-esm/hrana.js:105:19)
    at Object.runMicrotasks (ext:core/01_core.js:642:26)
    at processTicksAndRejections (ext:deno_node/_next_tick.ts:39:10)
    at runNextTicks (ext:deno_node/_next_tick.ts:48:3)
    at eventLoopTick (ext:core/01_core.js:175:21)
    at async file:///Users/fharris/Projects/jobasaurus-crawler/node_modules/.deno/[email protected]/node_modules/drizzle-orm/session-afae3551.mjs:926:25
    at async LibSQLSession.transaction (file:///Users/fharris/Projects/jobasaurus-crawler/node_modules/.deno/[email protected]/node_modules/drizzle-orm/libsql/index.mjs:86:28)
    at async SQLiteAsyncDialect.migrate (file:///Users/fharris/Projects/jobasaurus-crawler/node_modules/.deno/[email protected]/node_modules/drizzle-orm/session-afae3551.mjs:922:9)
    at async file:///Users/fharris/Projects/jobasaurus-crawler/src/lib/db/migrations.ts:4:1
Caused by: ResponseError: SQL string contains more than one statement

The generate SQL its trying to run is

ALTER TABLE `job` ADD `min_salary` integer;--> statement-breakpoint
ALTER TABLE `job` ADD `max_salary` integer;--> statement-breakpoint
ALTER TABLE `job` DROP COLUMN `salary`;

ALTER TABLE `job` ADD `min_salary` integer;--> statement-breakpoint
ALTER TABLE `job` ADD `max_salary` integer;--> statement-breakpoint
ALTER TABLE `job` DROP COLUMN `salary`;

Using sqlite/turso and deno. Thanks!

8 replies

DTDrizzle Team

•Created by hirefrank on 4/22/2024 in #help

Creating a View with Turso/Sqlite

export const jobsView = sqliteView("jobsView").as((qb) =>
  qb
    .select({
      id: job.id,
      title: job.title,
      company_department: job.companyDepartment,
      url: job.url,
      job_location: job.location,
      description: job.description,
      company: company.name,
      company_slug: company.slug,
      company_domain: company.domain,
      company_type: companyMetadata.type,
      departments: normalized_department.normalized,
      locations: normalized_location.normalized,
      earliest_known: sql`(STRFTIME('%Y-%m-%d', job.earliest_known))`.as(
        "earliest_known_formatted",
      ),
      last_seen: sql`(MAX(joblog.seen_on))`.as("last_seen"),
      days: sql`
      CAST(ROUND(julianday(MAX(joblog.seen_on)) - julianday(DATE(job.earliest_known)) + 1) AS INTEGER)`
        .as("days"),
    })
    .from(job)
    .leftJoin(company, eq(job.companyId, company.id))
    .leftJoin(
      normalized_department,
      eq(job.normDepartmentId, normalized_department.id),
    )
    .leftJoin(
      normalized_location,
      eq(job.normLocationId, normalized_location.id),
    )
    .leftJoin(joblog, eq(job.id, joblog.jobId))
    .leftJoin(companyMetadata, eq(company.domain, companyMetadata.domain))
    .groupBy(job.id)
);

export const jobsView = sqliteView("jobsView").as((qb) =>
  qb
    .select({
      id: job.id,
      title: job.title,
      company_department: job.companyDepartment,
      url: job.url,
      job_location: job.location,
      description: job.description,
      company: company.name,
      company_slug: company.slug,
      company_domain: company.domain,
      company_type: companyMetadata.type,
      departments: normalized_department.normalized,
      locations: normalized_location.normalized,
      earliest_known: sql`(STRFTIME('%Y-%m-%d', job.earliest_known))`.as(
        "earliest_known_formatted",
      ),
      last_seen: sql`(MAX(joblog.seen_on))`.as("last_seen"),
      days: sql`
      CAST(ROUND(julianday(MAX(joblog.seen_on)) - julianday(DATE(job.earliest_known)) + 1) AS INTEGER)`
        .as("days"),
    })
    .from(job)
    .leftJoin(company, eq(job.companyId, company.id))
    .leftJoin(
      normalized_department,
      eq(job.normDepartmentId, normalized_department.id),
    )
    .leftJoin(
      normalized_location,
      eq(job.normLocationId, normalized_location.id),
    )
    .leftJoin(joblog, eq(job.id, joblog.jobId))
    .leftJoin(companyMetadata, eq(company.domain, companyMetadata.domain))
    .groupBy(job.id)
);

When I use drizzle-kit generate:sqlite --config ./drizzle/drizzle.config.ts this doesn't get picked up / no sql gets generated. This is my first time creating a view for sqlite/turso. I feel like I'm doing something obviously wrong but I've been starring at this for an hour. :/ Thanks in advance!

2 replies

DTDrizzle Team

•Created by hirefrank on 3/4/2024 in #help

Odd connection ended error

I've been using Drizzle for months without a hitch. But for some reason I'm now running to an error when I do a simple insert. This is new code (never worked before) and I've been staring at it for hours. Can you spot the simple mistake? What does this error generally mean?

error: Uncaught (in promise) Error: write CONNECTION_ENDED domain:5432
      return query.reject(Errors.connection('CONNECTION_ENDED', options, options))
                                 ^
    at Query.handler (https://deno.land/x/[email protected]/src/index.js:288:34)
    at Query.handle (https://deno.land/x/[email protected]/src/query.js:141:65)
    at eventLoopTick (ext:core/01_core.js:166:7)

from this code....

const [metadata] = await db.insert(companyMetadata)
    .values({
      id: "061e34f0-c452-4d33-a923-02618cb6b5e9",
      domain: "airbnb.com",
      url: "https://www.airbnb.com/",
    }).returning();

1 replies

DTDrizzle Team

•Created by hirefrank on 7/11/2023 in #help

Sqlite3 support for Deno

Apologies if I'm being dense, but is there support for deno with a local sqlite3 db? I can't seem to get better-sqlite3 working in Deno. TIA

13 replies

Gaming

Programming