[DX] Database schema should be easily shareable

[CerealPlayer]

Using tools like Prisma as discussed in #178 could help create a db schema that we can all use to bootstrap our Postgres databases. Many other use cases for it too.

This way we wouldn't need to jump to #108 every time we want to work with db

[ykdojo]

Why not just use a plain SQL for creating the db schema though?

[CerealPlayer]

SQL might lead to vulnerabilities if not properly written.

A Prisma schema is safe, is easy to migrate for everybody working on the project (1 command) and provides with types, which makes it easier to work with the database.

Edit to add some more info:

Here are some screenshots of a Prisma schema example (not full, since the full file contains a database with multiple tables) and the types it provides. As you can see, in the schema you can quickly see the expected values, relations, default values... And the types are pretty nice too. The best part is since the database already exists (in @ykdojo supa account) we don't need to write an schema. You can just pull the info from the db and Prisma will automatically create it.

Prisma schema of a table called payment_details:

Types the Prisma client provides after pulling the schema from the db:

[ykdojo]

Hmm it is really cool.

I don't have a strong preference either way though - so perhaps we can wait a bit to see if anyone else has any thoughts on it.

[ykdojo]

I was doing some research on this, and it turns out, Supabase even has an entire doc on how to set it up here.

Seems pretty promising - I'll look into it.

[CerealPlayer]

Supabase doc's on this matter are pretty much Prisma doc's for every db.

That's another advantage I see. If anybody doesn't want to work with Supabase locally (personally I've run out of free projects) at least for the purely database stuff they can set up a Postgres docker container, bootstrap the same db using that schema plus Prisma migrate and use the Postgres connection string to connect to it the same way we would connect to Supa in prod.

[CerealPlayer]

Update

I started working on this but after going through the supabase.sql and looking at the cross schema references I can already say this will not work.
Prisma doesn't support cross schema referencing (see here) when introspecting databases, so unless we go for a different approach (such as handling auth on our own) we won't be able to use Prisma.