Prisma Typed SQL Queries

00:00 So we've got ourselves a pretty good search page here with query raw so that we can have this really specific query. But guess what? Prisma thinks that this with like the schema having to validate and everything is not good enough. It is not, like it's kind of annoying actually that we have to parse the results with this user search results schema and like the drawbacks of making a change here and now we have to also update our schema and all of that. That's kind of annoying.

00:30 It's not what I want to do. So Prisma, the latest version of Prisma now has support for TypedSQL. So let's take a look at what that is all about. So I've come over here to Prisma and we'll go to our schema file and we're going to add a new preview feature. So this is still not officially stable.

00:50 It is a preview feature called TypedSQL, SQL. And with that enabled now, we can come over to our terminal and I have npx prisma generate dash dash SQL dash dash watch. And so this is gonna just watch our SQL directory for any SQL queries that we want to have written. So if I come in here at a SQL directory and then add a search.sql file in here. Now Prisma is going to generate a special export that uses this SQL file as the query.

01:29 So What does that query need to be? Well, let's come over here, we'll grab this query, just like this, and we'll stick it in there. Now we had a couple of things that are parameterized, we'll get to those here in a second, I'll just comment that out right now, no filtering going on right now, and we'll have it limit down to two so that we can see that there is some sort of difference here. So I'll save that, and then because we changed this SQL file, our server isn't automatically restarting when we make these sorts of schema changes or change our client. So our client is generating, but our server is not going to restart.

02:07 So I'm going to restart the server for myself here. And with that, I refresh. Okay, good. Everything is working. Now we need to change and get rid of all of this stuff that we don't need.

02:18 So instead of query raw, this is gonna be query raw typed. Typed, there we go. And this is not a template literal like this. This is actually a function that we call. And we are going to need to import our query, and that's gonna come from the Prisma client.

02:35 So we'll go over to our db server file here, and I'm going to import star as SQL from Prisma client SQL. There it is. And we'll just export the SQL. In fact, here we can export star as SQL from that client. Okay, so now with that I can come over here and add SQL right here.

03:02 Then we'll come down here and say SQL.search, and we'll pass the like param. Now, we don't actually support that yet, so let's get rid of that for now. We'll just comment this out, and we'll save this, and boom, oh look, it is applying our SQL search. So, our search SQL, if I change this to limit three and then restart the server again, because again, updates to our client are not going to restart the server. But with that restarted, boom, now we've got three.

03:32 So let's bring this back to 50, and let's add support for these specific dynamic parts of our query. To do that, we use a special comment, and here it's at param, and we say the type is string and it is called we say it's the first parameter and it's going to be called like so this is our search we could call it search or whatever we want it doesn't actually all that matter all that much I'm just going to use like because that's what we used here but then here instead of the dollar curly we're going to do a colon and that is a reference to that param. And so now, if we save that, our client is gonna be regenerated. And would you look at that? We have type errors here because we're not providing that param.

04:17 That is super cool. And now we can take that and pass the like right here. Awesome. Now, of course, that's not gonna work because our server needs to be restarted when we change that client. You could probably put together some sort of watcher that restarts your server, but we're going to just manually restart here.

04:37 And with that now, I can do a search, and it filters just like we did with what we had before. Except now, we don't have to parse anything. And this is the real cool thing about this. So instead, this is actually just gonna be our users. And we can get rid of all this.

04:57 We no longer need to parse that. We can get rid of the schema, all of that. We don't have errors anymore. If there's an error, it'll actually be a SQL error. It can go to our catch boundary and that's fine.

05:08 So we no longer are gonna have parsing errors and everything else is all type safe still. See, so you look, here's our user, that's a result and here's the name. It's a string or null. It's all type safe, which I just think is so brilliant. It's awesome that now I can write regular SQL in a dot SQL file.

05:25 I can have all the tooling that I want to in here, whatever. And then, and I can parameterize it and everything. And then I can come over to my JavaScript code, reference that, pulling that of course from our Prisma Client SQL. And I reference that just in this query raw typed. I pass those parameters.

05:45 Those parameters are typed as well. So here, if I come over here to our SQL file and I say, hey, this is a number, save that, our client is gonna be regenerated and we should be getting, Oh, right, ah, that's, it needs to be an int. Ha, my bad. So we change this to an int, and now it regenerates the client, and we're gonna get some type errors in here because we're not passing an int. So we get type safety on the way in to our query in this parameters, and we get type safety on the way out.

06:21 And we don't have to worry about using Zod to parse anything. We get to use a real dot SQL file. It's not a query builder. It's just like regular SQL stuff. Super, super helpful.

06:32 I'm a huge fan of Type SQL from Prisma, and I hope you are too.