P
Prisma•2w ago
max

Parameterized Views for postgres 🦄

This is the continuation but unrelated to this thread (not necessary to read) Simplified Schema: 
previewFeatures = ["typedSql", "views", "relationJoins"]

Model Person{
    id           String
    name         String
    workingTime  WorkingTime   
}

View WorkingTime {
    personId       String
    targetHours    Decimal
    actualHours    Decimal
}
previewFeatures = ["typedSql", "views", "relationJoins"]

Model Person{
    id           String
    name         String
    workingTime  WorkingTime   
}

View WorkingTime {
    personId       String
    targetHours    Decimal
    actualHours    Decimal
}
Parameterized View We know this is a bit hacky but we don't see any otter viable cleaner solution that would provide the same benefits/power. If we request a person and include their working time, we have to specify for which range want the time calculated. Like for the length of March we need to know how much that person has worked and should work. So far we have done this with queryRawTyped but its inconvenient to manually having to merge it with persons afterwards and if we would include the person in the underlying select in the first place, we DO have the data together, but we don't have the flexibility to include other relations on the request dynamically and take full advantage of the entire PrismaArgs. Inside the SQL statements where we create the view we retrieve the variables like so: 
WHERE date > current_setting(var.date_range_start)
WHERE date > current_setting(var.date_range_start)
We set this via prisma.$extends(withVariables(dateRange)).person.findMany({include: {workingTime: true}) And the extension does something like this: 
return prisma.$transaction([
    prisma.$executeRaw`SELECT set_config('var.date_range_start', dateRange.start)`,
    query(args),
])
return prisma.$transaction([
    prisma.$executeRaw`SELECT set_config('var.date_range_start', dateRange.start)`,
    query(args),
])
In an ideal world we could pass the date-range from where we include, not in the super weird de-coupled way above. Any feedback on this would be appreciated. We're not sure if we're on the right track with this approach. Once We find a smoother way to input the parameters we might be set up for some very powerful Database stuff fully type safe through the ORM
4 Replies
Prisma AI Help
Prisma AI Help•2w ago
You're in no rush, so we'll let a dev step in. Enjoy your coffee, or drop into #ask-ai if you get antsy for a second opinion!
Nurul
Nurul•2w ago
Hi max 👋 Your approach sounds reasonable to me. The approach you're taking aligns closely with the ideas discussed in this feature request which aims to make views more flexible and integrated.
GitHub
Client-side Views · Issue #21287 · prisma/prisma
Problem Currently, the Prisma JS engine offers support for working with views, which is a very useful feature. However, the current implementation requires mandatory application of migrations and s...
max
maxOP•6d ago
Thanks for the response. Do you have any input on how I could make the passing of the viewParams better? Currently we do 
prisma.$extends(withParams({dateRangeStart})).person.findMany({include: {workingTime: true}).
prisma.$extends(withParams({dateRangeStart})).person.findMany({include: {workingTime: true}).
What would be the dream: 
prisma.person.findMany(
  {include: {workingTime: {viewParams: {dateRangeStart}}}
)
prisma.person.findMany(
  {include: {workingTime: {viewParams: {dateRangeStart}}}
)
To make such an extension for one model would probably be somehow (if even at all?) possible to extend the type and react on its existence to set session variables like before, but as soon as we include from another path like account.person.workingTime, this wouldn't work anymore Is it somehow possible to extend a specific type like WorkingTimeWhereArgs? Cause if so then it would probably be possible generically and we'd just have to add another extension that looks through the entire args and if it finds a viewParams section it writes them to the session variables If I simply extend the view in the schema by some made up property holder to make it appear in the type like to : 
view PersonTime {
    view_param_date_start DateTime?
    // All regular colums that actually exist on the view
}
view PersonTime {
    view_param_date_start DateTime?
    // All regular colums that actually exist on the view
}
then of course we have type support for: include: {workingTime: {where: {view_param_date_start: "2025-01-01"}}} This actually works if I write an extension for $allOperations that - looks through the args, extracts all parameters that it finds that start with (defined by convention) "viewp_param_xyz", - deletes them from the args and and sets them as omit: {view_param_xyz}. - set the extracted params like in the Original pst via transaction. It has to be omitted or else prisma tries to find it in the View where it actually doesn't exist since we just faked it for type support. But its by all means not a clean solution. @Nurul (Prisma) I forgot to mention you, so you probably didn't receive a notification. Any input would be greatly appreciated
Nurul
Nurul•6d ago
I really like your solution of adding a dummy property! That is really smart 🙌 While this isn’t as clean as having a built-in viewParams feature, it does allow you to approximate the desired behavior. Until we can provide native support for this kind of type extension or a more ergonomic API, this approach is probably your best bet.
Is it somehow possible to extend a specific type like WorkingTimeWhereArgs?
I don't think this is possible at the moment

Did you find this page helpful?