Drizzle Team•8mo ago

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!

1 Reply

jabh•8mo ago

Hey, the docs say:

Views are currently only implemented in the drizzle-orm, drizzle-kit does not support views yet. You can query the views that already exist in the database, but they won’t be added to drizzle-kit migrations or db push as of now.

https://orm.drizzle.team/docs/views

Drizzle ORM - Views

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

Gaming

Programming

Creating a View with Turso/Sqlite