This is more of a random theory question about prisma and where you have some logic live. I have a table

contacts

and a table

addresses

. Table

addresses

has a

contactId

and

isPrimary

. And

contacts

has a

primaryAddressId

in the table. If someone updates or adds an Address that

isPrimary = true

then it should set the contact’s

primaryAddressId

. So where would you as a prisma dev have this live? • Database Trigger • Middleware • Just my api code? • Somewhere else? This is actually a part of a bigger internal discuss I have been having and was curious this community’s thoughts

i might use https://www.prisma.io/docs/concepts/components/prisma-client/middleware or add a worker

If someone updates or adds an Address that isPrimary = true

I would say that this is also data modelling problem, you have 2 locations where the primary address is contained, and they can be out of sync, which would go against your business rules. I have done a similar data modeling thing in the past, and personally, I would remove the

primaryAddressId

from the contacts table, and then add a partial unique constraint on

addresses(contactId) where isPrimary = true

- this would make it so that for each contactId in the addressed table, they can have only 1 row where isPrimary is true, but lots where isPrimary is false.

Yeah that makes sense I think it was just easier to query but that makes sense

down the road, if you have other categories of 'primary' say - "shipping", "billing" (2 very common ones) then in your addresses table you can just change the constraint to be `addresses(contactId, address_type) where isPrimary = true`` and you don't need to then add a

primaryShippingAddressId

to the customer etc.

Yeah that makes sense

Looks like upstash is interesting

Apart from upstash, you can also have a look at zeplo.io

@Jonathan Marbutt I don't think you can use the

@relation

attribute to load in the

primaryAddress

as a relation via the prisma schema since all the

@relation

items need to be fields on the model / in the database. And this may boil down to doing all those business rules in another layer of the application. You could do it with a middleware I think, and do a test on

params.odel == 'Contact' && params.select == 'select'

or something like that, although I probably wouldn't do that as that may not be an appropriate use case, since that would run on every db query (https://www.prisma.io/docs/concepts/components/prisma-client/middleware#performance-and-appropriate-use-cases) Since this is a business rule, that is getting some enforcement support from the db, along with the other 50-100 business rules associated with your models, this is where you would probably need a separate layer on your application that handles the business rules, and then those delegate to prisma for persistance. So you're probably not using the prisma models directly, but those are the core data structures for some other class. Or use custom models on top of the existing ones -- https://www.prisma.io/docs/concepts/components/prisma-client/custom-models#wrap-a-prisma-model-in-a-class - For this particular

primaryAddress

case - this is probably what I would do - inherit from the prisma class and extend it with a

primaryAddress

method -- this is just an example, not tested at all

import { PrismaClient, Contact } from '@prisma/client'
class Contact {
    constructor(private readonly prismaContact: PrismaClient['contact']) {}

    function primaryAddress() {
        return this.prismaContact.contacts.find(contact => contact.isPrimary);
    }
}

Thanks for the detailed reply, That is very interesting.

I'm definitely in the postgres camp, but I haven't used a lot of triggers unless it is for something that is specific like auditing (logging, automated history tables) or performance (automated summary counts of relations etc), or the triggers implement something that is completely outside the scope of the app itself. I do find that if you are implementing business rules in both the code layer with coordination at the database triggers/checks layers, that better be documented, since if that business rule changes, multiple areas of the application will need to be updated. It might also depend on implementation styles, so if the way that folks utilize the datastore is directly through db connectivity, then you probably need to implement business rules as triggers/constraints so that all applications follow the same rules and the rules are consistent across applications, but if all access to the data store is through a library, or through a network api of some type, then the business rules can be implemented in that library / microservice/ webapi etc. There are also infrastructure issues at play too, depending on your scale and your deployment, rolling out constraints and triggers to the db may be more complicated or require more coordination than rolling out an update to a microservice. It really doesn't matter to me so long things are consistent and people know how to change things and where those changes are going to be made.