Triggers vs stored procedures
Hey everyone, I don't think this is related to Kysely directly, so excuse me but this is the only SQL type of server I am in. I am wondering what is the best way to automatically update a table attribute based on when another attribute changes (in the same table or another table). For example, if I want to have the attributes
updatedAt
and lastLogin
updated on their own from the Model below
I need these values determines automatically. I have some sources saying use Triggers and some saying just have functions that you can invoke yourself in server side logic.
Based on an article, this seems to be the main con of triggers
and the main pro seems to be that whereas, functions on the server have a chance of not running due to an errorr occuring right before the attributes updatedAt
, lastLogin
are updated11 Replies
Any input would be appreciated
Triggers can indeed be confusing, especially later once their creation is buried in one of the 500 migrations you have.
This is obviously just a matter of preference, but I usually do this in the typescript code. I have a "database access layer" that exposes higher level functions for accessing the db. This layer takes care of using Kysely and also doing this kind of denormalization.
You could also ask yourself, do you need those computed columns in the database? Are they just duplicate information? In some cases it's better to calculate the values on request and not store duplicate data. If the computation is heavy, then it might make sense to store it in the DB.
Thank you for help @koskimas, I like the idea of a "database access layer", I will try to implement that. I think some columns need to be computed when an event occures, such as "lastLogin".
This brings up another thing I have been unsure about, say, I have 3 tables. Business table, Working Hours table, and Bookings Table.
Business table contains a FK to the Working hours table and to the Bookings Table.
I need access to the Working Hours table data from Bookings table. I can add a FK inside the Bookings table that points to the Working Hours table or I can query the Business table and from there query the Working Hours table.
in situations like these, I've opted for including a FK in the Bookings table to the Working Hours table and my thinking is that, it will be better performance. But I am sure if that is the best practice or not
FK or not, make sure things are indexed properly.
Thanks for sharing that, I looked into it a bit and it seems like @unique in prisma creates indexes automatically but I added a few more @@index([attributeName])
Thank you guys!
Unknown User•13mo ago
Message Not Public
Sign In & Join Server To View
Why are you doing the change in the db directly?
Unknown User•13mo ago
Message Not Public
Sign In & Join Server To View
Sounds like something migrations would be used for?
Unknown User•13mo ago
Message Not Public
Sign In & Join Server To View
I think it's just that there's not much to say if you need to be able to modify the db directly. You have no option but to use triggers in that case. What else could you possibly do?