Mini RFC: Postgres dbgenerated

Changes

Allow Postgres to generate Primary Ids (UUID) vs Prisma (CUID) -- built in as of Postgres13
Allow updatedAt to have a default now value
Add a default Enum where it makes sense
Lean into @db utils to allow for a more DB forward schema/validations

Misc

I had to update my CI Postgres from 11.5 to 15 in my latest Bison create... However
It looks like someone already updated our CI flow in Canary to Postgres 15 (*could use a release)

TLDR

It allows for the flexibility to utilize pg in the terminal or a DB Viewer when creating and making one-off edits. **For larger migrations, you'd still utilize our Prisma Factories.

RFC Notes

dbgenerated > Prisma Loom: https://www.loom.com/share/eac06de2eb1a4a82bfcb0f4cc80b0541

Reasoning:

not everyone runs through the UI or Prisma Factories.
It's not easy to change later
The underlying SQL being more strict "id" TEXT NOT NULL VS "id" UUID NOT NULL DEFAULT gen_random_uuid()

Viewpoint: Not all of our clients have a full-stack team of engineers. For some of the larger enterprise clients, there's a person for DB, Backend, Frontend, Ops, etc. I've met some resistance from folks worried about ORMs and what they are doing. They look to have as much control as they can at the DB level. This would allow folks to see the raw SQL and run in pg in their normal day-to-day.

Similar for smaller clients. Having to set up a script file to add one person is a bit more verbose than it needs to be. They'd rather hop into the terminal and give their new employee access on the fly. While we want to encourage best practices, most of these folks just want to dive in quickly and move on with their day.

This change isn't a make-or-break change -- more of a nice Dev experience allowing all of our options to stay open. The worst case is you copy a timestamp and cuid and slightly alter it. (what I do today)

Using the generated DB fields is a low LOE for us. It also allows folks to continue learning what happens at the DB level should something change (MySQL vs Postgres for example). We've marked learnings like this as a Pro as well in tRPC land, where we need to start understanding joins and selects better. I feel this falls in the same category of "What is this tool doing for us" and being aware of what is happening.

There are also other @db utils that could be learned/leveraged as we dive into Prisma/Postgres more. Types of Date/Time Timestamps, String types, Varchar length, etc.

Opinion/Preference

Lean into the DB as much as possible and allow Prisma to help normalize types for the app.

Questions

I'm curious if others have opinions, concerns, etc. So far it seems like a minor preference change -- but correct me if I'm wrong.

Alternatives

I recall there being a Prisma shell similar to rails c that maybe would allow for us to do one-offs but I have yet to try. (if it still exists) I don't think this would be able to leverage our Factories, but it would utilize the core schema file.

Here are a few counter points to what you're proposing, and a few questions. Overall, I'm not convinced everything proposed should be the default, but rather an alternate path when it's necessary.

Overall, I think this is a bigger discussion point than just the changes outlined here, but I wasn't sure where else to put this.

CUID as a default I'm still leaning CUID as a default mostly because it's portable. For most projects, regardless of the underlying database, we can use it in exactly the same way. If we move to more database generated values, we'd have to tweak the logic if the database changes. Flexible is better for something that's Open Source.

Most of the time scripts (seeding, fixing up data, etc) should go through Prisma rather than use raw SQL.

They'd rather hop into the terminal and give their new employee access on the fly. My guess is this is client dependent. Some that are technical may prefer this, but others will want to do this through an admin page. If the client wants this functionality, I don't see why we wouldn't have it as a script if an admin tool isn't possible. Raw db insert seems like the wrong approach usually.

Shell similar to rails c I believe you are referring to https://github.com/egoist/prisma-repl. I think this would be a great default and would allow you to run one-off Prisma commands if needed.

Seeds & Factories Should seeds use factories? I'm inclined to say no in favor of explicit data.

Outliers

For some of the larger enterprise clients, there's a person for DB, Backend, Frontend, Ops, etc.
I've met some resistance from folks worried about ORMs and what they are doing.

I can see this point. but view this use case as an outlier. For these clients we should explain why we use an ORM and how it helps reduce manual mistakes. Directly writing to the database without going through an application layer is dangerous so we shouldn't encourage it. For the few that require it even after those discussions, we could take the approach you mention.

@cball - I think my biggest takeaway and maybe extra context is Dev Experience here.

but others will want to do this through an admin page.

In my experience, this never gets built -- never in scope. The more considerable use case for all of this probably is experimenting with the Prisma Repl.

**The cases I personally would like to see solved are developing the backend and API before the UI exists. There's not a great way to dive in like there is with other stacks and get rolling (without a UI).

I want to be able to explore my DB Schema and validate concepts without adding a bunch of one-off scripts and tests before I'm ready.

**When a client wants to add Jim to test staging, etc. There's not an efficient way to "jump in" and do a thing. The way I do this now is to connect to the DB and add a row.

Adding a row needs better defaults at the DB level. Otherwise, I'm copy/pasta from a previous row which is just as bad.

Additionally, on the note of migrating from one database to the other. I can see the use case for CUID. However having had to do this in the past, IDs are probably the least of our worries when needing to change from Postgres -> MySQL, etc. There are a lot of "gotchas" here and it will always be a heavy, thourghly tested, lift to swap DBs for Prod -- even for a quick MVP.

Flexible is better for something that's Open Source.

If we are talking about this for OpenSource -- I'd personally like to see a different default Prisma Schema depending on the DB selected in the Options. Again, there are a lot of annoying gotchas between Prisma and MySQL. It's more maintenance, but easier for us to roll when a client has the need.

#PersonalOpinion I'd love to hit the DB Defaults specific to each DB... but also realize these ARE personal preferences. While we want to promote running everything through the stack. In practice, it's not always an option and sometimes a momentum hit.

Based on that info tho...

The Actual RFC

Options:

Leave as is... Document approaches for "How to 'x' when you need to do 'y'"
Update Bison to generate a schema based on DB chosen. (iterate/settle on defaults)
Other... some middleground

Next Steps

Will expand this thread out into a fuller RFC this week and wait to get more thoughts from the team.

echobind / bisonapp