Create a view with Drizzle
Hi, need some help to understand how i can create a view with Drizzle.
"drizzle-orm": "^0.31.2",
"drizzle-kit": "^0.22.6",
export const wf_users_in_private_view = pgView("wf_users_in_private_view", {
wf_id: text('wf_id'),
user_id: text('user_id'),
}).as(sql
SELECT DISTINCT ${workflows.id}, jsonb_array_elements(${workflows.data}->'nodes')->'data'->>'id' FROM ${workflows} WHERE ${workflows.is_private} = true)
On "npm run generate", and reading Drizzle document i was expected to have an output of "CREATE VIEW", but have:
No schema changes, nothing to migrate š“7 Replies
Running into the same problem. Did you figure this out?
Basically, currently the code won't generate the views, but will allow you to query against existing ones. It's a known limitation š
Hello @Justin , I manually created the view and declared the view in my codebase with the
pgView from 'drizzle-orm/pg-core', but I can't find a way to query this view. I expected the db.query.myView.findMany() to work but myView does not exist on type.... Is there some documentation explaining how to query views with drizzle-orm ? Thanks !So here the views are not populating under the
db.query object yet, you would need to do your view queries like this instead (for now):
I imagine this will improve in time, but this is what works currentlyIn the case like this we will have a schema.js to create tables and another SQL file to create the views. I'm wondering how we store and run this SQL file?
Hello @Justin ,
Thanks for the response! Your suggestion works, but I need to use the query builder to avoid refactoring my entire codebase, particularly with the with clauses for including relations.
I want to implement an "archived" feature and was considering using a view. However, Iām open to other solutions. I was wondering if Drizzle ORM supports middlewares, as they could potentially simplify this process, but it seems they might not be available.
If using views is the best approach, do you know when the query builder will support working with views?
Thanks a lot