Prisma ORM: Local First, Typed SQL Queries and Serverless with Søren Bramer Schmidt

Welcome to SyntaxToday. We have Soren Bramner Schmidt on. He is the cocreator of Prisma, and we're gonna be talking about all kinds of exciting stuff. First of all, Prisma going local first, being able to run Prisma in the browser and and a sync engine coming up. We talked to him about, like, biggest mistakes on writing, SQL queries and and just database mistakes in general for JavaScript idiots like like us.

We asked them, what what's the deal with GraphQL? And and is GraphQL even used anymore? Where is it popular? They have a new thing called Type SQL coming out or or it's already out, and that will give you full type safety on writing raw SQL queries. And And then finally, ask them a whole bunch of questions about, like, best practices on running database queries and and doing database stuff when you're on serverless. You're all over the world, but your database lives somewhere.

What's what's the big deal? Let's get into it. Soren, thanks so much for coming on.

Thanks, Wes. And and and Scott, good to meet you. I'm really happy to be here and excited to to dive into some of these things. Yeah. Likewise. Yeah. We've been using,

Prisma for a long time since we launched the Sanity site, and Wes and I've been using it before that. I think we've been down with Prisma back in the Graphcool days. Yeah. Yeah. We were just talking about that, how

Scott and I have I think we've gone through every iteration of of Prisma back to when it was a GraphQL,

hosted thing called GraphQL. So it's been many years. What was it like how many years have you been at it? It's been a it's been a a wild ride you've been on with us. Yeah. Yeah. Like, you're for sticking with us for so long. I'm curious. How did you how did you end up adopting Prisma in the 1st place? What was that like? What were you evaluating, and and how did you end up picking Prisma? I

have a course called Advanced React in GraphQL, and the initial offering of that, I was just kinda looking into, implementations, you know, how how they work with different things. And and at the time, Prisma was just a GraphQL first.

It's since moved for anyone listening, it's since moved to being just a very good, fully typed JavaScript

ORM for various databases. Is that a a fair thing to say? That is that is accurate. There's no GraphQL left in Prisma. We are we're heavily inspired by GraphQL and my background in in the c c sharp world using using ESLint, but GraphQL has heavily inspired the syntax for for Prisma, and I I think for the better. Maybe that's a a good first question JS

GraphQL had had massive popularity, for a couple Yarn, and, now we're we're starting to see a lot of people say, maybe we should just query the data we need directly on the server, maybe even render it on the server.

You don't need a GraphQL API. But then we also we have a lot of people on from, like, corporate world, and they they say GraphQL is a savior for us because it's a nice a nice layer to interact with all of our 11 different APIs. So what's your thoughts on where the whole GraphQL world is right now?

So so GraphQL is a bit of heavy machinery, and everybody in that world, they did GraphQL like stuff before there was GraphQL. They just they just did it in their own ways. So the innovation GraphQL brought JS, let's standardize on a on a way to do this, on a way to pnpm, on a way to reference other types, and then we can build and share tooling. So from that perspective, GraphQL was a huge step step up. Many people were using OAuth in the Microsoft world or other things outside, but, really, people were just doing Wes APIs and had all of these conventions about how to how to do all of these things you need to do. So what GraphQL brought was standardization and and a massive improvement in tooling, in productivity, and I still love GraphQL for that.

But for many things, it's it's very heavyweight.

You don't need you don't need all of the overhead of of the type system generating the types, generating the client, all of that stuff. It's just too much. Even to get into your initial question a little bit, back to that Wes you were asking a little bit of, like, why

we chose Prisma.

And, you know, at the time, you know, I think I I came from Mongo. I know Wes had done a fair amount of Mongo, and so migrations and things like that weren't my favorite. So I personally was looking for a tool that, one, made those types of things easy, but, also, you know, the Studio component, the ability to inspect our data in an easy way. For me, it was like a a nice onboarding into, SQL based databases that, you know, I I didn't really have the experience with. And, also, believe it or not, Sentry has a Prisma integration.

So we use Sentry's Prisma integration for our tracing. And, also, we actually, this'll be just a quick ad read into Sentry, but we use, we use Sentry and Prisma together to find our slow queries, which is a really cool thing. Sentry has the ability to tell you which of your queries are slow in your database, and, that allowed us to do things like add indexes where we didn't have them or,

just in general. Yeah. Yeah. Take care of our our database in a way. Can we tell that story real quick? JS Wes had launched a syntax website for maybe 6 months, and we had something in our century pop up that it was like, oh, this is this is a slow query. And I was like, that's weird. Like, why why would, like, finding a show based on its number be a slow query? And it turns out that we had forgot to put an index on that field.

So what it was doing is essentially just querying every single show, looping through them, and then and and then giving us the actual show for it, which is hilarious. And then I went back into the Prisma schema to add the index, and I had the Prisma, ESLint

turned on. And the Prisma linter told me that I needed to put a index on it, and I was like, oh, I missed it. It's pro I mean, that happens all the time. Indexes are are just one of those things you can miss. I mean, that's something you you should always be thinking about, but you can always miss them. Your how you're querying your database changes over time and stuff. Either way, head over to century. Io forward slash syntax. Don't have a good 2 months for free. Alright. Back to the show.

That's such a common Scott. And just the other day, I was talking to somebody from a major US retailer that was built there, the Wes shop, the infrastructure with Prisma. And and he was relaying to me how they had this problem that whenever traffic spiked, there was a high chance that the entire site would crash. And they they were doing all of this work with the Kubernetes clusters and scaling and this and that.

And what JS the problem? A missing index on the on the sessions table. Yeah.

Classic.

Is do you see any other, like, common things happen like that? Because what I what I foresee is a bunch of us JavaScript idiots Yeah. Grab an ORM off the shelf knowing We call them node hipsters.

Very primitive.

Yes. Like, knowing nothing about how databases work, you know, and we just start building startups and whatnot, and we you can do silly stuff like forget an an index. Is there any other, like, common stuff you see over and over again where people that are not necessarily

database experts? The other thing that happens all the time, and it it it's really the the major problem with most ORMs, we make it too easy to to create crazy queries that are difficult for the database to optimize.

Specifically, when you when you do joins, you traverse relations, and you filter on the related data. That can become really expensive and really difficult to optimize with an index.

And it's so tempting to do because you just, like, bap bap bap, and you have all of the data you need, which is awesome.

But what you don't think about is all the work that has to happen on the database to load up that data.

Yeah. That was also a problem with with GraphQL for me is, oh, you can get anything you want? Sure. Let me go 40 levels deep and grab everything I want. You know? Who liked it? Give me their profile photos, but who would like theirs? Give me their profile. And then before you know it, oh, there's actually database queries happening under the hood here. Yeah. And you can only really do that if you aggressively

catch the results.

But then you need to worry about that too. Right? I mean, you have stale data, and when do you rephrase that cache? And it actually gets really, really complicated.

Yeah. Getting into DataLoader and then all that fun stuff. That that is like the, the GraphQL iceberg is like on the surface, it it's so so great. Under the surface, it's a little more complex.

GraphQL influence that is still present in the Prisma client. We have built in the data loader pattern. So that means if you do the GraphQL thing where you're traversing in layers and on a deeper layer, you do a lot of the same queries, we'll automatically batch all of those up according to the data loader pattern.

Yeah. One thing I'm really thankful about with Prisma is that, after you guys, you know, pivoted a couple of times, using Prisma has been extremely stable. Like, I feel like it's one of the libraries we can update with and never have to worry about, you know, too many anything like, even having, adjust our code base too much. Is that something you guys really, think about deeply when making big changes?

Big time, and I I'm glad that you're noticing this. One of the things we have really had to to work on over the last year and a half is this perception of Prisma is moving really slow. We have creep files and opening, GitHub issues, lots of feature Wes, Lots of cool things and functionality that people want. But what happens as you mature as a product, you go from appealing to early adopters that just want new shiny stuff all the time to having to serve a much more mature audience. And I think the 2 of you are in that category. You you, experienced software developers, worker centric. You want stuff to work. You want to be able to upgrade without having to spend half a day fixing stuff. Yeah. Yeah. And that's really what most of our users are telling us. It's, what we call the silent majority. They're not so vocal online on Twitter, but they are there, and they are the majority of our user base. That's what they want. They want us to move slow, to never break things, to make it a little bit better all the time, and that's what we've been focused on for the last 3 Yarn, since we kind of matured into that category.

But it does mean, that we are a little bit slower to release new features, new fancy things. And the small group of people that are very vocal online, we hear from them, and Wes like to hear from them. But Yeah. We have to serve that other audience too.

Alright. Well, we'll we'll make sure to ask you about the Drizzle, kids on Twitter. I'm sure I'm sure they they get under your skin a little. But let's talk about that JS one thing that you're starting to hear from people JS, I don't need a database ORM. I'm gonna simply write SQL strings straight up. You know? And and then people are, well, what about what about types, and what about TypeScript? So one thing that Prisma has just rolled out is typed queries or typed SQL.

Typed SQL. Type SQL. Type SQL is what we say internally.

Oh, so tell us about that. That's right. So that when you think about building an abstraction on top of database queries, you can really go two directions. You can either build an ORM that looks more like what Prisma is, or you can build a query builder that looks more like Kiesely or Dressel.

And based on our background and how we wanted to like, the design sensibilities we had when we started Prisma, we went with the Prisma model. We want something that is extremely easy to use, no foot guns, and it it works great for the majority of use cases. We think about the 90% of of CRUD style queries in your application.

I think the Prisma API is ideal for those cases.

But then, people will tell you that it lacks flexibility, and that's that's true. There's lots of queries that you just cannot express with the Prisma API. So what we hear from a lot of those young, grizzled people online is that they want full flexibility.

They tell us, oh, we have to rip out Pressplay in order to migrate to Drizzle so we can have the flexibility.

And that's great. If that's if you want to do, the query builder thing, do that. That's fine. There's nothing wrong with that. But I think for most people, especially people in more mature organizations, you want a a simple, API that is very easy to to do code review on. You can see what's going on.

And that's the Prisma API. And then for those 5 or 10% of the queries where you need to have more flexibility, maybe you need to optimize performance, you should just drop down to a lower level.

And that means whenever you are at that that lower level, you know to pay attention during code review. You know that something special is going on.

And for a long time, we have had, what we call raw queries. Just write a SQL query and send it to Prisma, and we'll execute it as a SQL query.

Mhmm. But the developer experience of that is not great. There's no real tooling, no auto completion, no type safety, no nothing. So what we've done with Type SQL is say, we like that distinction between the super high level Prisma API and then the lower level that allows you to do anything, but we want to provide a better developer experience.

So what Type SQL does is it enables you to write SQL queries in a dot SQL file, and that means you can you can have a more native experience. You get auto completion based on your on your schema.

And then on top of that, we're providing type generation.

So that means you write your SQL query with full auto completion provided by your IDE, and then the Prisma generation step generates input and output types.

So that means in your application where you're executing this query, you now have the same level of TypeScript type safety that you get from, from normal Prisma queries, and we think that is a a delightful experience.

Yeah. That that's great. So to be clear, it's not, like, types or autocomplete for actually writing the SQL strings.

It's it's for when the SQL runs and returns the data, that data will be fully typed. Correct?

That's correct. But if you have a modern IDE like Versus Code or something like this, they have extensions that you can you can use to connect to your database, and then you actually get the autocomplete experience as you're writing your SQL query.

Oh, that's cool.

That is not something I've done. Sorry. Go ahead, Scott. No. No. I was just gonna say, yeah, I I did not necessarily know that existed. Do do you have any off the top of your head that you know? Is Yarn there just generalized,

just look for MySQL or yeah. Just search for your database in the in the extension, Explorer. They're all, kinda terrible. They mostly work, but they're not great. So what we're probably gonna do at some point is integrate that experience into the Versus Node the Prisma Versus Code extension and see if we can do a better experience. I think we can.

But for now, we're just telling people to to use those, those tools that already exist.

And what are some instances where someone would duck out to writing SQL strings directly instead of using the ORM?

So, really, the Prisma ORM syntax is very limited. It's great for for loading some data, for updating a model, writing a new model. Mhmm. But as soon as you need to do something, you could you could think of as more analytical queries, or really just you Node more flexibility than what the Prisma API provides, then you need to to to dip down to raw SQL queries. It could be as simple as saying you want, to compare 2 2 columns in the same row. The Prisma API currently doesn't support that. Or if you want to to do complex aggregate queries Yeah. We don't have great support for that. You can do that with SQL.

Yeah. So for for anyone listening, it's very common for someone to say, oh, okay. I wanna, run a bunch of aggregate data on I wanna see how many people from California did these sales, but only when they had bought this item. You know? And it's very expensive to or sometimes impossible to query all of that data, bring it into your programming language like JavaScript, loop over it, and then Yeah. And then have have the data. Like, I've I've run into that situation many times in in MongoDB Wes I had, like, a 40 second query when I was running stats on 10 years' worth of data.

And the solution to that is is your database is very fast at doing that type of stuff. So you have to switch languages from JavaScript loops into your your actual database language, and then they can run them much faster. Is is that a a good way to explain that? That's exactly right. And and if you if you're not super experienced with writing SQL queries,

you can you can just copy your your Prisma scheme into one of those AI things and have it write complex SQL for you. That works remarkably well.

Just, make sure make sure you review it first. Yeah.

Yeah. Drop tables. Yeah. Be fine. Yeah. Run it run it against production.

Yeah. Oh, that so that's now fully available in the latest version of Prisma?

It is. It's called Type SQL. It's in the latest version of Prisma. You just go and use it. And as I said, there there are some things we want to improve, like the integrated auto completion experience as you're writing your SQL queries.

Mhmm. We also currently require you to to provide access to a database, ideally a local database, not your production database. And that's because we are we are asking the database about type information when we're generating those types.

In a future version, we we might find a way to to take all of that logic and build it into Prisma instead.

Okay. So is there basically, you take the the query that the person wants to run,

And is there, like, an explain command or something like that? It's it's similar. So we are generating a prepared statement, and most databases are able to to return type information based on a prepared statement and Okay. The actual database schema. So that's the functionality we're using.

Okay. And which databases does this work with? So it works with Postgres, MySQL, and SQLite.

SQLite is a little different. We've actually had to, take parts of the SQLite query generator and embed into Prisma because it doesn't have this functionality of creating a prepared statement and and returning type information to you. So we had to to lift that out of SQLite and embed it directly into Prisma. Nice. So so we lean on, like Wes mentioned, the the linter, which I think is a really fantastic

tool for, making sure our schema is correct.

But what do you think are the most common mistakes people make when authoring their schemas?

So we actually have a new tool that is going to to help you with these kind of common errors.

Just a few months ago, we we released a tool we call Optimize, and it's a it relies on that same tracing information you were talking about before, Scott. And it's super cool that you're passing that into Sentry and getting live information about slow queries. But for anybody out there who's not using Sentry, you can go and take a look at Optimize. Who's not using Sentry? They do exist, I've heard.

So Optimize is a is a purpose built tool just for Prisma relying on that same tracing information.

You just enable it in your local development environment. So when you're running your tests, so just clicking around, trying your website, then it collects information from all of these from all the queries you're generating.

It will show it to you in a nice UI overview, but then it will also detect, patterns of common mistakes and provide recommendations for how to address them.

The missing index Node is is is an obvious one.

We have a few a few recommendations, and and over time, as we learn from, from the community what are the common mistakes, what is most useful, we'll we'll build out this library of recommendations.

So I think that's going to be a very useful tool.

Let's talk about serverless. So one thing that I ran into with the the syntax website was Wes I was doing my transcripts, the way that our transcripts work is we get the entire transcript for the audio.

And what is returned from that is, like, you have a whole transcript, and then each individual sentence has like, we call them a utterance. Right? So you you 1 transcript has many utterances, and then every single utterance will also have, individual words. And every single word has, like, a time marking of when it starts and stops. So quite a bit of data Yeah. For, like, a 1 hour podcast. And, I built the whole thing out. Worked great, and and then I deployed the thing to to Vercel.

And we Scott hitting hitting issues with it because, the payload of those types of thing was was just too large. Because I was trying to do it in a single linked what what's the word for it? Linked query, batch query.

Basically, I wanted to create the transcript and the utterances and the words for those utterances in one single transaction. Yeah. Yeah. Yeah. Yeah. One transaction. Thank you. And I I was hitting issues with that because it was it was simply simply too large. I don't know if it was timing out or whatnot.

So I ended up having to to chunk it up and do it 1 by 1, so I wouldn't hit those issues. But that's a that's a limitation of serverless Wes you you can't run code for that long in serverless.

So curious about your thoughts on on how to approach something like that.

So it it's a real challenge for us because we have, hundreds of thousands of developers doing all kinds of weird things with Firstline, and they are running Firstline all kinds of weird environments.

Yeah. And the world of serverless has presented a lot of weird environments. So you mentioned Vercel. Vercel is a really great platform, super easy to use, but it also comes with a set of standard configurations that you then need to fit yourself into. And and one of the things about serverless is that it's a very constrained environment. Right? For every single request, it'll you get your own environment to run-in. So there's less variance, which is great, but it also means for for for reasons of economics, that environment is very constrained.

And it sounds like a problem you might have been running into is that the the memory available on that instance is very constrained. Now if you if you use Lambda and maybe even on Vercel, you can go and change that. You can change what is the environment available for this serverless function, how much CPU is available, how much memory is available.

Yeah. And you can change that for different endpoints of your application. Right? You can say your general website, it just needs a small environment. That's fine. It doesn't do a lot of work, but then you have these background type jobs that needs lots of memory. Yeah. You can go and tweak that, and maybe that was that is what you're running into.

Yeah. Yeah. I I think that was it. It was a bit odd because I was able to I literally just took the transaction and and looped over and did each one at a time. The downside to that is if if it fails somewhere in between, I don't get the nice ability to roll back the entire thing. You need to manually clean it up, right, and then Yeah. Try again. But not not a big deal for for us at the end of the day. Another question I have about serverless is everyone talks, oh, serverless or even, like, edge, run JS close to the user as possible. And then everyone says, well, if if you need to connect to your database, you're kinda ruining Yeah. Benefits of that. Is that true?

Latency is is just a killer of performance. Right? So whenever you go from 1 machine to another, that's bad. Especially if you go back and forth many times, you want that latency to be as as short as possible.

Scott, typically, between your application server and your database, you can have many round trips to serve a single user request. So you want your application and your database to be as close as possible. You want them to be in the same AWS region as a minimum. Maybe if you can have them in the same availability zone, that'd be great.

What you want to avoid at all Scott is to have your application server in South Africa and your, your database in US east.

That's really, really bad. 200 milliseconds is okay, but if you do it 4 times Mhmm. It's not okay.

Yeah. So that's the that's the problem people are running into when they are deploying at the edge. It's really there are other problems, but this is the primary one. This is the one that confuses people.

So if you are in in the Cloudflare ecosystem, they are doing this thing they call smart placements Wes they're trying to detect that you have these round trips back and forth. And then they say, even though the user is in South Africa, we're gonna not use a node in South Africa. We're gonna use 1 in US east because that's where the database is. It's gonna be faster.

But you have to think about those things.

Yeah. Totally. We had them on and and asked them the same question. Like, yeah, we will try to, against what you think, move where your application is running and try and move it as close to the database. Or and they also have this product called TypeScript. It's not something I've checked out. Yeah. Wes, essentially, the fat pipe behind the scene that goes directly to your database.

Hyperdrive is very similar to to Accelerate from Prisma. So we Okay. We build Accelerate initially because Prisma users came to us 3, 4 years ago and said, we're trying to use IDS serverless and and the data API. We want to use that because it optimizes performance from serverless functions.

But this data API thing from AWS, it really doesn't work, and it still doesn't work. It's a terrible product. So we built Accelerate to be a proxy in between your application server and your database.

And one additional benefit you get from Accelerate is that we place the Prisma query engine right next to your database. So that means those round trips, multiple round trips, they now happen close to your database even if your if your application server is somewhere else. That could be because you're using, Cloudflare, some other edge environment to to have the application server run close to the user. That could just be because you have misconfigured your things and the the server is in US Wes and the database is in US East.

Either way, Accelerate will help you with that problem.

Okay. And it also does connection pooling as well. Right? Because another problem that you have with serverless is, yeah, you run, like, 1 function, and it connects to your database. Right? But if if you get a spike in traffic, you could be running a 100 serverless functions at once, and you can't create a 100 connections

to your database. Right? What's the the the solution? Correct. Well, you you can create a 100 connections. You can Sanity connections, especially MySQL. MySQL doesn't really mind connections. Postgres is a little bit more expensive because it needs a a full process for each connection. But if you have a beefy server, you can still have thousands of connections.

The bigger issue actually is that establishing that connection, especially over a a high latency network JS is really slow because there's a SSL handshake and everything going on.

So you want to have an a pre established connection that you can reuse. Oh. And So that's the benefit. Sorry. Go ahead. I I didn't realize that. Okay. That's a primary benefit. And when people talk about co starts in serverless, part of it is your application server has to start up. But the other part is you need to establish those TCP connections to your SSL handshake and get the database connection running. It just takes a long time. It takes on the order of half a second. And if you're able to reuse those connections, then you can you can make that a lot faster. So that's the benefit of using something like Accelerate or PT Bouncer Wes you where you can can have those connections already established in something that is more long running, a traditional server. And then you can establish fast connections to to that proxy from your serverless functions.

And I if you're probably using I think AWS charges based on, like, wall time. So, like, a half second every single serverless function, that's gonna get expensive real quick, isn't it? That's right. It it adds up.

Oh, man. Because Cloudflare Workers doesn't wait on IO. So if you make, like, a fetch request to your database or something like that, then it will

Are you all using Cloudflare?

We're not using it on the Syntax website, but I'm, I'm pretty pretty bullish on a lot of the Cloudflare stuff given the

the limitations of it, but I'm a big fan of it. As most good things has started out as a bit of a toy a few years ago, but I really think that the Cloudflare has a real shot at becoming the next generation general purpose cloud platform. I think they are the 1 company that that is on a path to dethrone AWS. It's gonna be a long, long path.

But if you look at all the technology they're building, it's impressive.

And why do you think that? So you you mentioned the wall time and the pricing model. With Cloudflare, you get the benefit of deploying to 300 regions whenever you deploy your application.

On on AWS, where you have 20 some regions and by default, you deploy to pnpm.

When you have a Lambda function starting up, you get that slice of compute and you pay for it no matter what is going on, even if you're just sitting there waiting for external IO. Mhmm. Cloudflare has a different model that is much more efficient.

So when you deploy it to the entire world, that's a huge benefit, but also you pay much less money because they have a more efficient runtime.

Wow. That's that's good. I'm I'm I'm very excited about Cloudflare. Sorry. Go ahead, Scott. No. No. Yeah. Yeah. And Cloudflare is a really good,

I would say, like, onboarding into their products, where just simply to use Cloudflare CSS. Right? They they get you into that and then slowly upsell. But the upsells are all really small. Like, it's really easy for me to move all of my domains over there. That experience is great. I totally agree that their products seem to just, you know, really be finding the right spot for me personally.

One thing that I think goes along with this conversation about latency is local first. It's a a topic that keeps coming up. Right? Because what is faster latency wise than just storing the data directly on your phone and syncing it behind the scenes? I watched, one of your talks on I I believe it was expo and local first.

I'm curious about, like, what Prisma's position is there, and, like, is are those things coming to the web as well? Is it like, what what's going on there?

So we talked about, this majority journey you go on as a as a product and as a category establishes, and local first is all the way up there.

Like, a a year ago, it was all academics. It was really it was very much, this is not useful for anybody.

But we are moving up that that curve of maturity, and it is getting to a point where it's actually really interesting, and I think people should be looking at it. Local first, the promise is that you can deliver much greater user experience. And if you look at at products like Linear, like Figma, they're built this way. When you're working in linear or the thick Figma document, it's all happening on your local device.

And as a result, it's just a great experience. It's fast. It's snappy.

It works, at least to some extent, offline, which is great.

But I think what is underappreciated and what is going to make this, this model mainstream is that it is also a much better developed experience.

If you listen to some of the tech talks from the from the folks at Linea, they talk about how they have this really complex piece of machinery called the sync engine, and they have some smart people working on that. They have from the beginning of their company 7, 8 years ago, they've invested in this thing.

But that means all the rest of the developers in the in the company, they have a much simpler world to operate in. All the data is local. They can just Mhmm. Bang out new features really quick. They don't have to think about distributed systems problems and and caching and all this kind of stuff that that we were talking about before.

Those problems just do not exist.

All the data exists locally, and you can program your features with data expectation.

That means you need to crawl the database many, many times. It's okay. It's right there. It doesn't cost anything.

That is a a real shift in how you think about building applications.

There are lots of, of complications that come with this, lots of trade offs, and that's what we, as an industry, we need to kind of grapple with. We have thoughts on how how this can work really, really well in a way that is not just for a few early adopters, but for for the for the masses of developers, and that's what I was talking about at this App. Js conference earlier this year. Yeah. You know what? I think Prisma JS really

well positioned there. I I know there's a handful of different services and and new platforms popping up left and right. But a lot of times, what they are is either they're just the sync engine, they're just the local side, or they're trying to be the whole picture, but they're having to create, like, the ORM from scratch. And it feels like Mhmm. You all have so much of the groundwork laid that, like, once you have the local story and the syncing, like, everything else will just come together, which to me is an ideal scenario concerning I I like Prisma. I use Prisma all the time. If I could use Prisma to do my Local First work instead of having to reach for RepliCache, or any of these, you know, kind of complex solutions, that would be an ideal scenario for me, for sure.

I agree. Replica JS really cool, and and the new stuff they're working on, even cooler.

Yeah. Totally. It is it is it has been part of the inspiration that that has put us on the path that we are on. So just a second. We can talk about how we're thinking about the syncing problem and and and how we think that should work.

But the first thing we did JS we we brought Prisma to to the client. Prisma's an ORM. It works just on the server. So earlier this year, we released Prisma support for for Xpro and React Native, and I think the most, critical thing that we did that we got right is we introduced, what we call reactive queries.

So on the server side, it's it's really very simple. You have a request coming in, you execute some queries, you return a result, and then all of the state everything, you just blow it away. You don't care. New request comes in, fresh state.

If applications work that way, whenever you click the button, the app would restart. It would be a terrible terrible experience.

So that's why front end development is actually a lot more complex than back end development. You have an application, can run for for minutes or hours, and you need to to deal with all of that. That is what is complicating most UX work.

With reactive queries, you can get rid of all of that state management. Instead, you can in your in your components, in React, you can just query the data you Node, then you can rely on the on the underlying mechanism to update the UI with if any of the underlying data changes.

So we released that earlier this year. It's been, really popular.

And I think it's a it's a really it really simplifies how you're building applications.

Your state management essentially becomes your local database, and it's effortless to write to. We just, built a desktop app with Tori Wes you have, you know, a SQL like database that you're shipping and or, Node, I guess, we just use IndexedDB for that. And that's the best part. The state management was either saving or loading from that IndexedDB, and that's it. You know? It's you always have that data. It's always persisting, and it's super duper fast.

Yeah. I so I built something with Dexi a couple weeks ago. Nice. Cool, Wes. Dexi is yeah. Scott, like, went deep and tried, like, every single local first everything and, explained it to all of us. So my first sort of foray into this was was using Dexy JS, which is a IndexedDB kind of a ORM.

And they have React Hooks where you can say use LiveQuery, where you simply just, like, query a list of whatever you want. And then anytime some other piece of the code updates that, it's it the query updates and and React updates. And I was like, man, this is this is great. And, like, in a lot of the cases, I didn't even need to think about, like, UIs for loading state or UIs for Just CUBS for free. Right? Yeah. Yeah. Or what do you call that when, like, you you think it's gonna you think it's gonna save properly? Optimistic UI updates. Optimistic UI. A lot of that is like, I'm not optimistic. I know it's just gonna take a millisecond to save. I don't need to be optimistic.

Of course, there's still situations you probably want that, but, man, it's it's so great. And so is that the plan with Prisma JS to eventually come to

client side JavaScript world with, that type of thing? So we think we have a good shot at becoming, the tool that makes local first development mainstream.

Nice. That's very It's a bit of a path to get there, and we we started with React Native and Pnpm partly because it was easier technically for for us to implement, support for React Native compared to browsers, but also because this is this was an ecosystem that was really eager for something better.

Working with SQLite in React Native applications, is a bit of a pain, and we we just bring the Prisma DX to that experience.

So what happens next is we're going to to build support for Prisma in the browser.

You might know, but but Prisma uses a Rust query engine.

So we've already done all the hard work to compile that to to WebAssembly.

We had to do that for for edge function support.

So we're gonna use that for for Prisma support in the browser, and then we will, bundle a a WebAssembly version of SQLite that works out of the box.

There is a a key advancement that has happened in browsers. All the major browsers support this now, and that is the, I think it's called the origin private file system or something like that, OPFS.

It's a really fast API to to store files in the browser.

IndexedDB is actually pretty slow, and now you don't have to rely on that anymore. You can store your SQLite file directly on the file system using this new API.

So we're going to make use of that, and then we will have a unified, API across React Native applications, browser applications, and, of course, you will continue to be able to use the the Prisma API on on your server.

Yeah. So instead of using the database built into the browser, which is IndexedDB, you're literally just using the database. API.

Yeah. You stick the file system, the Sanity file into the browser, and then you use Prisma client side to read and write to that, and that's fast.

It is extremely fast, much faster than you would expect it to be. Of course, you need to download a megabyte or 2, of of WASM stuff initially, and that's a bit annoying.

But for the applications we Yarn targeting, they're more like linear. They're more like like apps, less like websites.

So we think that's a decent trade off.

Mhmm. Yeah. You'll you'll load it once, and you have the tab open for or the desktop app open for for weeks sometimes. Yeah. And and this thing will just be cached forever. So even if you go away and come back, it'll be there. Oh, that's that's exciting for that type of thing. So working with Sanity database locally in the browser or in your application is great. But the next step becomes, well, how do you make sure that the data exists not just for you, 1 user on your device, but in a shared environment? So if you think of a product like Notion, Notion has pages, and the pages can be they're in a workspace, of course, but then each page can be shared with 1 or more more people, or they can be private.

So what you really want from a, a local first experience as a developer is you want a way to to define what pages, what data components should be loaded onto the device and which shouldn't. And you want to really make sure that the data that is not should not be accessible to this 1 user never shows up on the device. And if you want to do this in a way where you really truly support offline capability, offline editing, it's pretty difficult because you can't just say, here's a query that includes some filtering for the user and then cast a result of that. That's not good enough.

You need to take all of the data that is relevant for that user and move it locally.

So we're going to build a a sync engine that will be available open source. You'll be able to run this on your own server if you want to. But then, of course, we'll also provide a a cloud service that just does this for you, scales well, takes care of backups, and so on.

The key opinion that we're putting into this thing that I think is different from most other local first sync systems is we're going to to make every kind of key piece of data in the application a separate SQLite database.

So if you're thinking about Notion, Notion has pages. Every page Notion would be its own SQLite database.

And that makes sense because the, the permission boundary in Notion is on the page level. You either have access to a page or you don't. And that means Node the sync engine can know what pages, what databases you have access to, and just preload all of them. They'll all be available on your device.

As soon as you open up your app, and you're you're ready to go. Man, I'm doing so much of this by myself right Node. So I'm I'm stoked to hear all this.

Yeah.

How does that work when you get a little bit more complicated in, like, authorization and access? You know? Like, I wanna be able to see a list of, items in the store, but I shouldn't be able to see who owns that item or you know? Like, there's there's part of the that data model that is should be restricted, but part of it needs to be public.

Yeah. It's a great question. And I I think we will see that there there's going to be a a hybrid model where where some of those things really are like documents, like Notion, like Figma, even like linear. And and the, the permission Node fits very well to the document model. And then there are other things, like what you're talking about, that are a little bit more like the arbitrary SQL queries we talked about before.

They are they are at a different Vercel, a little bit more complex.

And I I think we'll see that the documents, we can use the that nice and easy, sync engine I just talked about. And then these other more complex queries, they will need something else.

So maybe you'll want those to always run on the server. Maybe that's the best trade off for your app. Maybe you want to precompute those lists and put them in their own documents. And Mhmm. A sync engine like the one that we're creating should make it easy for you to do that. But this really is something we all need to figure out as an industry.

Yeah. Yeah. We still don't really know. As an example, when we launched support for for Xper earlier this year, we have now been using this internally. Right? One of the things we found is that because we have reactive queries, we wanted to push all of the state into the database.

Anything like where you are in the UI, what pnpm up has have opened and so on, all of that we want in the database because then we could make use of the reactive queries.

But creating a database schema that supports all of it is actually a little bit, annoying. It's a lot of stuff to manage.

So now we're trying to to figure out how to make that a a more seamless experience, and this is going to be true for for all of these these new paradigms, these new approaches we need to invent. We need to see it first. We need to see what doesn't work so well, and then we need to to find solutions to it. David Capiano was just knocking at my door talk talking about state machines,

for exactly that type of thing. That's it's pretty wild that you, you could store, like, every single thing even, like, down to what item are you hovering, and then your UI is a function of state. Right?

Absolutely. State machines are great.

Let's ask real quick. We we said we would ask about the Drizzle guys.

They are constantly on Twitter being hilarious and sometimes a little bit mean. I'll say that.

Internally, are you able to share what's the the feelings towards them? I know they've tried they joked about even sending you a box of bees on Valentine's Day.

So so look, I'm I'm a little old school. I don't get all of these memes. The the drizzle meme game is is, like, top of the line, and I I cannot keep up. I just I just give up.

Have you ever had calls with with those guys at all? I listened to the to the, the syntax episode with the Drizzle team, and I think they even mentioned having a call with, with Node. I haven't personally had a call, but had some interactions on, on GitHub.

I think they've they've been doing great work. Right? People love the the Drizzle Query Builder. If you look at the Npm downloads, they are about tied or or maybe exceeding, which is super cool. So so great work. I don't understand the way they're communicating. That's okay.

That's that's Scott

Yeah. Yeah. Can I ask you about, like, what database is your favorite? You know? Like Oh, man. You've been seeing a lot of people kinda sour on MongoDB lately. Everybody's talking SQLite.

Yeah. They're going back to their old favorites, MySQL or, Postgres.

But I'll I'll share this. By far, the majority of the Prisma user base is using Postgres.

It's more than more than 50% is is just Postgres.

Do you know the split for the rest?

Then it's MySQL and Mongo.

We support also SQL Server. I think that is still higher than SQLite, and that's because our our client support is so new. Right? Mhmm.

But Postgres is really such a huge market share. And if you look at industry trends, you'll see that too.

SQL Server, Oracle, MySQL going down, Postgres going up.

And I think what has happened is that Postgres is extremely flexible, and that's what people have loved it for for for so long, but it has been difficult to operate. If you talk to people who have operated Postgres at scale, they'll they'll share all kinds of horror stories. Postgres has just improved. It's faster, almost comparable to MySQL. It JS easier to operate, and this has happened over the last 5 years. So my prediction is that that Postgres is just going to dominate.

You asked about MongoDB, and I think MongoDB was popular for two reasons.

1 is the document model.

Document model is extremely convenient. You can just have this very complex data structure. You can Scott locate it all in 1 place so it's fast to load, but you still support indexes on that complex data.

But the relational databases, they do that too now. They have, they have excellent JSON support, including indexing.

That's the thing that has happened in the last 5 years, very much, driven by Mongo's early support.

And then the other thing that really made Mongo popular was the the operational and and scaling characteristics. You could do replications, you could do sharding.

You could have a really large dataset.

And what has happened, in the last 5, 10 years is that computers has just gotten really, really fast.

Mhmm. In my last real job before starting, Prisma, I was working at Trustpilot, and we it's a large online review site. And we had this problem that we kept buying bigger and bigger database servers from Amazon until we had bought the biggest one, and we're like, oh, no.

Now we're in trouble.

So we actually we moved to Mongo for that reason. It was a multiyear process to do that. But now the single machines you can get is sometime something like a 100 times faster than the big machines we were using back then. And that just means you don't really need to to scale out the same way you did in the past.

So Postgres can get you really, really far.

That that's really funny because we've often joked about that. Like, the solution to a lot of my problems were simply just buy a faster computer. Yeah. Oh, wait a few years and then buy a faster computer. Wait a few years. But, like like, why is that? Is that because of, like like, processors are getting faster, memory's cheaper?

So we got SSDs.

Yeah. That was a big thing. SSD Node. Yeah. Because the database has to live on Yeah. Drive somewhere. Right? It it used to be that you had to have your working set in memory. Otherwise, you're dead because you could do, like, 10, 50 requests to to the to the spinning disk per second. That's just not a lot when you're talking web servers.

But now you have SSDs, NVMe's. They're extremely fast. You can you can tolerate having a large portion of your data not in memory. And at the same time, the memory capacity you have now is not 64 gigabytes. It's it's terabytes. So it's just you have a lot more of everything.

Man. And one thing we also hear a lot when we talk about SQLite and, like, Cloudflare and whatnot is, like, it's not it's not guaranteed what is it? Not guaranteed rights or or guaranteed rights, but not guaranteed reads Wes if if you update 1 on somewhere in Node part of the country, somebody else making that read. Can you explain that problem, and is that actually a problem?

So that's the read your own rights, problem. And and in a distributed system, you often end up in the situation where you you have, replicas of your data in multiple regions so that you can serve reach really fast.

But because of the way the database is structured, you can only do a writes in 1 region. So whenever a write comes in, it is redirected to that central primary database.

But then when you've done that right and you go and do a read immediately after, maybe reading the same data you just wrote, Wes, you get that from the replica, and the data hasn't really trickled down there yet. Mhmm. So that's the read your own write problem. There are simple ways you can you can get around this in your application layer. A thing we did at at Trustpilot all the time was we just cache that data for a little bit and use the cache. That works well. You can also set a a little time and save. For for a little while, I'm gonna go and read from the primary instead of the replica.

And some database products, they have built in support for this. I know that Kent, Kent C. Dodds, he he's very fond of the setup where he uses SQLite on Flyt io, and he has a setup with a single primary and the replicas. And he has a sophisticated mechanism in his Epic, Wes stack to to do exactly that and deal with the regional rights problem. Oh, right on.

Oh, we forgot to ask, and you wanna talk about this.

Prisma is releasing their own hosted Postgres?

That's right.

Yes.

So for a long time, I have wanted we have wanted databases to be much simpler than they are. The entire serverless movement was about treating your servers, what was it, as cattle and not as pnpm or something like this. Reality is databases are still very finicky. You create 1 database. It's kinda expensive. You really care about it. You you tune it. It's just so complex.

We want creating a database to be as easy as creating another page in Notion. That's how databases should be. You should create the database. You shouldn't worry about it. You should be able to have access to the full history so you can can roll back if anything happens.

It should just work. It shouldn't be expensive.

And for a long time, we were waiting for other companies to to do this, and it just hasn't really happened.

So now we're doing that. We're building a Postgres, a hosted Postgres offering, that one will have a very generous free tier. You'll be able to create lots and lots of databases for free. And then because of all of the, investment we have already done in our other cloud products, Accelerate and Pulse, we're going to to build in a point in time recovery for free for all of these databases.

Pulse is a change data capture product that just records and stores all changes that happens in your database. So our backup mechanism is going to be based on that. You mean you could just, like, scrub it back, like, an hour and a half if you wanted? Yeah. Go back an hour and a half.

Restart your database to that point or create a new database and restore it to that point and then go and have a look at that data. Oh, and that and that's different from what most places do right now Wes it's,

oh, you you took 3 backups today, or you wanna make another one. You can restore to one of these 3 backups.

Yeah. That's such a common thing, Jeff. You have daily backups or something like this, and then doing the restore is a is a big deal. It shouldn't be like that. You should just be able to go into your Notion history or your database history and see what has happened in the past. Wes, we're gonna have to switch to Postgres.

That's what we're gonna have to do and jump on this tool. I that's the worst part about doing this podcast. I I for someone like me, I'm always like, oh, we gotta we gotta use this now. We gotta move off of MySQL.

So we're really excited for this database product, and we are we're working on it right now. We are working on some some really cool low level technology that enables us to to do all of those things. I think a preview version might be available by the time that this podcast launches, but if not, I'd encourage anyone to just either subscribe to to to the Prisma Twitter feed or go and DM me if you want early access to to the stuff.

Sweet.

Cool. Last part of the show where we get into sick picks and shameless plugs, did you come prepared with a sick pick and shameless plug today?

Almost.

Almost, man. That's good. That's good. I'll take it. What is your sick pick? So my sick pick is maybe a little lame, but I'll just encourage everybody to go and try out this, AI development stuff.

If you haven't already, I know most people probably have. It's all pretty terrible, but it also is is super cool, and it's pointing towards the future that I think is extremely exciting.

So if you haven't already, go try out Cursor. Use Claude to just solve some of your programming issues. What I found again and again and again is I will not use those things, and then I'll try to fix a problem for half an hour and be like, oh, I should just try this AI thing, and it solves the problem immediately. It's such a great experience.

Yeah. Yeah. In fact, we, we're, we have a show, episode 834, on using AI tools effectively for this type of thing. Because we get a lot of people who hear us talk about these things and instantly Yarn, like, pooh pooh them. Yarn are people writing code anymore or whatever? But, like, yeah, you shouldn't just blindly use this code in your apps. But as an assistant, you're pretty great. Yeah. Mhmm.

What about a shameless plug?

So I'll plug 2 things. 1 is Prisma optimize. If you're a if you're a Prisma user, you should really just take, 20 minutes, an hour. Play around with Optimize. It's a super simple tool Wes just enable in in Prisma ORM, and then it'll it'll start to collect information about the queries you're running.

And at the end of it, it'll it'll look for for patterns for dangerous patterns, things that might be a problem for you in production and give you recommendations for how to improve your application.

Play around with that. I found it a really a really interesting way to just get a better understanding of what your application is actually doing and maybe a way to to learn about how to how to make things faster.

So that's my that's my my plot number 1. Plot number 2, trial database product. Freshman, Postgres. I think this is going to be really cool. You'll be able to create, as many databases as you want. You're not limited to a single database.

And we really are trying to to simplify how how you work with data. The personal ORM is all about querying data in a in a simpler way, and our hosted Postgres offering is about a much simplified way to manage your your databases.

Zeck Awesome. It sounds awesome. Yeah. Well, thank you so much for coming on. Appreciate all of your insights into to everything. It seems like there's a lot of exciting stuff coming down the horizon with all the Local First stuff. The new Type SQL stuff is really exciting. So and, of course, the hosted stuff. Appreciate all your time, and, we'll catch you later.

Thank you, Wes, and and Scott. It's been great. Great talking to you. Yeah. Pnpm problem. Thank you.