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
jabh3mo 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.