max Posts - Answer Overflow

max

•Created by max on 4/17/2025 in #help-and-questions

Is there a way to differently parse DateTime and db.Date when reading from Postgres

Both DateTime and db.Date are being parsed into a full Date in js. This causes unwanted behavior since the Day will shift for negative Timezones and hand-picking the affected fields for differenting handling is not a viable solution for me. I'd like a generic way to apply a different parsing strategy for different Database types

7 replies

PPrisma

•Created by max on 4/15/2025 in #help-and-questions

Handling @db.Date fields client side from different Timezones (Postgres)

We have a database where some tables have date only fields (DateTime @db.Date) while others are regular DateTime fields. When the server sends this data then javscript will create a new Date() for both since we don't have a date only primitive there. This causes the @db.Date field which is stored as 2025-04-20 to be understood as a UTC 00:00 date. Therefore if I'm in New York which is -4h the date will result in 2020-04-19T20.00.000Z and the data will shift by one day in something like a calendar. We COULD simply say that all dates that we receive client side we add the current timezones offset so that it will then be correctly parsed as 2020-04-20T00.00.000Z. Then we woulc correctly have the beginning of the DB's date in the local timezone to correctly sort in the entry, BUT then we would mess up regular DateTime dates. A "createdAT" timestamp from 14:00 local NY time will come back to the same client with a 18:00 time which is WRONG. So in short we do need some way to differenciate between how we parse DateTime and db.Date Dates. If we have the service for that entity client and server side then we could add a manual mapping client side to only parse the specific fields that we know are db.Date fields. But this is not a generic or maintainance low solution. I also don't want to resort to saving them as DateStrings since the Database shouldn't have to adjust (and lower) its capabilities because of the client and we would loose some possibilities that Dates can do which strings can't.

7 replies

PPrisma

•Created by max on 2/20/2025 in #help-and-questions

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

7 replies

PPrisma

•Created by max on 2/20/2025 in #help-and-questions

Is joining views into models a recommended pattern? 🦄

We're a bit on the edge with preview features but we just discovered today how convenient it is to create Views and be able to reference and include them from other Models. Underlying we're using Postgres for context. 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
}

Working time represents the amount of hours the employee has worked and should work. We actually have it as a parameterized view that can react on input via client-extentions but more on that in another thread There is much more going on in our schema but basically WorkingTime is a fairly complex View that Joins over many tables, It in itself is very performant. Is it a good practice to use this setup a lot wherever Views are the most fitting solution? Or are there downsides to this that we've not yet noticed? Its pretty sexy because we can include this from anywhere fully type-safe and don't have to use queryRawTyped to fetch WorkingTime separately and then always manually merge persons and their corresponding working time afterwards. Is this a good approach or are there downsides? This Question continues in another thread due to the question being technically unrelated and the character limit

3 replies

PPrisma

•Created by max on 11/7/2024 in #help-and-questions

Soft delete & include

I recently impemented soft-delete with prisma ClientExtentions and it works wonderfully, but: When handling relational joins I cannot simply add a {where: {isDeleted: false}} to every include statement with a deep patch function, cause to-one relations dont have where. Is there a generic way to add a where statement to every include?

1 replies

Gaming

Programming