Complete Prisma & Supabase DB Tutorial

[@АлександрГригорий-е6о]

how to query many-to-many data in supabase client? (likedBy & likedPosts)

[@dougs-coding-corner]

Great Question, that one is a little unintuitive. I've added the example to the GitHub project. Essentially, it boils down to using a nested select statement. As Prisma creates the "_LikedPosts" join table, you can use the following select statement on posts on the Supabase client side: ``` client .from("post") .select("*, user(*), comment (*), _LikedPosts(user(*))") ``` See how you provided the "_LikedPosts" join table and then specified the nested user entity to return? This will result in a response object like: ``` { ... _LikedPosts: [{user: {...}}, {user: {...}] } ```` You can clean that up by providing custom names to joins using the following syntax: ``` client .from("post") .select("*, author:authorId(*), comment (*), likedBy: _LikedPosts(user(*))") ``` This will result in ``` { ... author: {...}, likedBy: [{user: {...}}, {user: {...}] } ``` Which might be easier to work with. :) It looks a little confusing, I highly recommend checking out the GitHub code and playing around with it to get a better feel for it. :)

[@studiomonty]

@@dougs-coding-corner I wonder if Supabase is the way to go for larger scale SaaS applications, if a many-to-many is considered something advanced (which I suppose happens a lot in a real life SaaS application). Wouldn't it be better to use Prisma with MySQL then?

[@ethanconnelly8794]

You're gonna go far. This is a brilliant tutorial.

[@kehrin]

I wish every tutorial was like this. Thank you so much!

[@andric7]

Hey Doug, amazing video! Just subscribed to your channel, hoping to see more videos like this! Especially around T3 Stack and Supabase. Would love to see what your workflow is like for Prisma/Supabase when in production, and also how to set up a custom auth provider with Prisma/Supabase (like Supertokens or Clerk), and how that interacts with RLS for authorization.

[@TahaTan]

Thank you very much for your video, thanks to you, I have been informed in a good and efficient way.

[@ML51626]

Hey Doug, great Tutorial! I‘m wondering if prisma is capable of handling RLS and triggers in the schema.prisma file nicely (including putting the results into the migrations later). If not: what could be a good strategy? Adding the sql manually to the migrations? Using the studio UI and then create migrations using the supabase cli?

[@ajinkyax]

Great tutorial, even its useful for just postgresql with prisma. also I would recommend using tsx & bun instead of ts-node and node

[@TLTechbender]

Amazing tutorial helped me out a ton!!!!

[@hanserlabber4164]

Subscripted! Your kind of doing the stuff - fast - good detailed (not too detailed) explaination - friendly - I hope you will produce more videos. Especially I would love to see how you would setup the t3-turpo apps (supabase created a fork with auth for expo app) the nextjs app in the demo is using next-auth.. so I want to work with supabase from both apps but I am not sure if it is straight forward (with your videos I am a little bit more hopefull 😄) Thanks for your videos man!

[@thomasbarnekow1281]

Awesome video. Thanks a lot!

[@Hapayfull]

Geat stuff. Learnt a lot!

[@federicomarazzani2649]

Hey Doug great video man!, it would be nice to see implemented NextAuth with this setup

[@trinkel8]

Excellent video. Very clear and to-the-point. It explained so much the I was missing in other tutorials and confirmed that I had made the right choice in tooling . . . until I got to the part where Prisma won't work on the front end 😢. Is this a limitation of using Prisma with all databases or just with Supabase?

[@dougs-coding-corner]

Thank you for your feedback, really appreciate it! 🤩 Sadly, this is a limitation that comes with Prisma for all types of databases. Prisma is written in a way that relies on the NodeJS runtime. Even if it were ported to the browser context, then you would have to expose your actual database credentials to the client, as Prisma doesn't provide any sort of data API on its own. So on the frontend you either need to use the Supabase client, or provide a thin API layer (e.g. using GraphQL) yourself.

[@trinkel8]

@@dougs-coding-corner That really is a bummer. I really like the Prisma approach . . . especially after dealing with dbase and SQL stuff for years. Thanks for the quick reply.

[@xidnebwoz]

Supabase Auth can not work well with Prisma, that's so pity.

[@tonyabracadabra6935]

Is it something can be resolved?

[@Tanner-cz4bd]

no@@tonyabracadabra6935

[@dheerajs2838]

From performance and developer friendly purpose, would you recommend Prisma for schema & seeding but using supabase for querying. Do you know how fast supabase queries are compare to prisma? Good content by the way

[@dougs-coding-corner]

Good question, I haven’t checked that myself. I would assume that Prisma would be faster, since it goes directly to the database rather than through an additional API layer, which also performs additional actions like auth validation. It might be interesting to see how both clients convert complex join operations to SQL and then compare the efficiency of their queries (I’be heard for example that Prisma sometimes does more trips to the database than technically necessary in complex joins), but I haven’t encountered huge performance issues with either one yet.

[@trinkel8]

Ok, last question, I promise . . . well, at least until the next one comes to mind. In the schema, you talk about using functions specific to the database in attributes such as `@db.Uuid` and `@default(dbgenerated("gen_random_uuid"))`. Do these map to functions in all databases, or do we have to change these if we change the `db provider`? Again, excellent video. Thanks for posting it.

[@dougs-coding-corner]

Yup, these change depending on the provider. An SQLite database has no way of generating a uuid on its own or validating the uuid string format, so those wouldn't be available for the SQLite provider. In those cases, you can only rely on the Prisma client itself to ensure that the proper values are generated. :)

[@hdd87]

FYI, I had to use this in package.json to seed: "prisma": { "seed": "ts-node --compiler-options {\"module\":\"CommonJS\"} prisma/seed.ts" } Also from what I can tell there seems no more need to use --backup with supabase stop command.

[@additionaddict5524]

Great video, thanks for this

[@Sabutofons]

Thank you for taking your time to share this useful video. I just suscribed to your channel after watching the video. My only queston is let say you have been developing locally as you demonstrated in the video. How do you connect your local supabase project to a production project?

[@dougs-coding-corner]

Thank you for subscribing and sorry for the late reply! All you have to do is switch out the connection string to point to your production database. Then run the Prisma migrate or push command to push your Prisma schema to the production database. You would usually set that connection URL as part of your env variables and run the migrate command as part of your deployment process. Hope that helps?

[@Sabutofons]

@@dougs-coding-corner That really helps. Thank you very much.

[@jeremyh9841]

Why import client from supabase ? Its enough to use prisma client and connect to supabase uri.

[@MrJohn360]

Thanks for sharing

[@arbizen]

this helped a lot!

[@عبدالقادرعبدالرحمنعبدالله]

Possible to use Supabase edge function with prisma ?

[@yooujiin]

how can I integrate supabase Auth with prisma User model? supabase uses plural for models... how can I link the two so that I only use supabase for Auth. I assume it has something to do with the userid field

[@nested9301]

u are still gonna use the supabase client for the auth stuff tought

[@oamarkanji3153]

Can you make a video with prisma and supabase auth?

[@dagassa8029]

yes, with auth it’s the horror, especially because it‘s not fully supported by prisma. same as row level security. but there are workarounds…

[@barinbritva]

Thanks a lot!

[@wata1991]

could you go over an example with supabase auth, because in your example the User table exists in isolation, but in reality we need to connect the supabase managed auth table to it somehow.

[@isaacfink123]

The supabase users table exists in a different schema and prisma doesn't have access to it so if you use prisma and want any type of auth you need your own user table

[@wata1991]

@@isaacfink123 thats literally what I’m asking 😅

[@dougs-coding-corner]

Interesting idea, I'll note it down for a future video. In the meantime, Supabase provides a nice article on how you can approach this by creating a users table in public with a foreign key constraint on the auth.users table: supabase.com/docs/guides/auth/managing-user-data They also explain how to potentially use a trigger to automatically propagate entries from auth to public. However, I would usually recommend that you app checks on login if this user already exists in your public table and create it otherwise. This has the benefit of truly separating the two concerns of "authentication" and "user profile", which is good practice from a domain driven design perspective. :)

[@avi_5827]

How can I do Full Text search on Prisma Postgresql setup

[@PaxNot]

Why not use @default(uuid()) and @default(now()) in place of the dbgenerated syntax?

[@dougs-coding-corner]

the uuid() and now() syntax only implement the default values on the Prisma client level. If you manually insert data into the database or use a different client, you still need to provide these values yourself. When the default values are used with dbgenerated, then the database makes sure to generated these values and it doesn't matter what client I use. Personally, I would always use the dbgenerated options if working with a database that provides these default values on a database level.

[@PaxNot]

@@dougs-coding-corner Thank you for clarifying!

[@t3ntube357]

Please can someone answer me, why using Supabase + Prisma, can't I just go with Supabase as they offer almost everything Prisma can do? I'm I missing something here?

[@dougs-coding-corner]

You can absolutely only go with Supabase if you find their approach and feature set satisfactory. Prisma is a lot more declarative than Supabase, both in it's API for interacting with the database as well as managing the databases themselves. This allows you to worry less about the details and write more concise and developer friendly code (e.g. no need to write any SQL statements yourself). Prisma's type definitions based on the schema are also way more mature than Supabase's types inferred from the table layouts. Finally, Prisma allows you to be database agnostic - so starting out with a SQLite database initially and then switching to something like Supabase when your app gains traction and needs to scale better is extremely straightforward, which can save costs, as Supabase only allows 2 projects in the free tier. Hope that gave you some pointers as to why you might want Prisma with Supabase. Generally speaking, Prisma doesn't allow you to do anything that you couldn't do with the Supabase client, but it makes your life significantly easier and speeds up development, which is really all these newer frameworks and technologies are about. :)

[@murtadanazar]

Hi 👋 bro. , what is the name of the font that you use in the terminal? ❤️

[@dougs-coding-corner]

Tha's "PT Mono" - instead of the default terminal I use ITerm2 (iterm2.com/) with "oh my zsh" (ohmyz.sh/), which provides a significantly improved experience over the default Mac terminal :)

[@murtadanazar]

​@@dougs-coding-corner This is more than wonderful, thank you 🫂❤️

[@codelucky]

Can I also use Hasura? If yeah, where does it fit in between?

[@dougs-coding-corner]

Sorry for the late reply. Personally, I haven’t worked with Hasura, so In don’t have any experiences there. The Supabase client already supports a GraphQL interface, so there is probably no need to implement one yourself if you are already using the Supabase client. In general, I would think of Hasura as an alternative option to interact with your data from the client and as an alternative to the Supabase client. Hope that helps?

[@codelucky]

@@dougs-coding-corner Thanks for your reply. I have decided to go with tRPC instead of Graphql so Hasura was totally eliminated. Supabase + Prisma + tRPC + NextJs+Fastify are my basic tech stack.

[@rec-trick]

who is the best strapi or supbase ?

[@nested9301]

supabase is just a hosted postgres instance ,that make it easy to work with the database without worrying about are the deployment stuff ,and provide auth , edge functions and storage so you don't have to manage your own server that will be a pain in the a** strapi is headless content managment system that allow u to create your apis with the no code approch just using the ui but u need to self-host it anyway so idon't recommend that crap unless u want to pay for their cloud version which is expensive as *uck

[@rec-trick]

Ty ❤️❤️

[@Bozo---]

Strapi is a headless CMS. It's different than supabase.

[@adrenalin.]

Can I use uuid_generate_v4 instead of gen_random_uuid?

[@dougs-coding-corner]

Yup, anything works that is supported and enabled by Postgres and generates a UUID type value (because of the @db.Uuid field specification). Here is a link to the documentation: supabase.com/docs/guides/database/extensions/uuid-ossp

[@nested9301]

actually i think prisma and supabase is just pointless since u can't use it with storage and auth just stick with supabase client