TypeScript and the Database: Who Owns the Types?

So I'm working on this cool new app, so it has a database where I have books and their authors and book reviews. I've even got some data I've put in there, so I've got some of my favorite books. And I've got a cool web server here, so it connects to the database, it runs a query, and it renders all the books. So yeah, I think it's pretty good. Let's take a look.

So I think, seems like it maybe has a bug. So let's see what's going on here. Let's see. So I guess, what is the type of books? It's Any, it's query result Any, which means that book has an Any type. So maybe I should write, maybe I should use types to solve this problem, because this is TS Congress after all, so we can define a book interface. And let's look at the structure of this table. So we've got an ID, which is a string, and we've got created by, which is a string. And we've got title, which is a string, and we've got publication year. We're just gonna copy that. For the JavaScript people here, integers are a special kind of number. So let's just go number. And last but not least, we have contents, which is a string, or I think it could be null. And with the node-progress library, I believe you can specify a return type for a query. And hey, lo and behold, we have some type errors. So yeah, I think I just misspelled year. It should be publication year. So let's fix that. And now if we head over here, hey, what do you know? Pretty good. So types can definitely be useful when you're working with SQL. But it's not the full story.

So let's see what happens when you run a migration. So I realized that the publication year could actually be null if we don't know when it's published. So for example, who knows exactly when the Iliad and the Odyssey were published? So better just leave it as null. So let's run that. And we can confirm that.

Yep, we have our new books and publication year is null. So yeah, let's see what happens. Yep, null. And fun fact, in JavaScript 2022 minus null is in fact 22 because JavaScript. So the problem here is that we changed our data schema in the database, but this type that we wrote by hand didn't update to reflect that change. So really, publication year now is nullable. So it should be number or null. And once we make that change, then TypeScript gives us an error, right? So yeah, TypeScript is nice enough to say that you can't subtract null. And so I think here I have the fix here. We just add a conditional. Yeah, great. And let's see if that fixes things. Sure enough, it does. So once again, types can be very helpful, but there's also some potential problems here.

So in this talk today, I'm going to go over different approaches for using TypeScript and SQL together, which I think is sort of a choose your own adventure approach. There's a lot of different ways to do it. By the way, if you like this talk, you can follow me on Twitter or check out my blog and my book.

So we saw sort of the naive approach, which is writing a raw SQL and hand coding types for the results. There are some good things about this. As we saw, we do get some type safety. And another nice thing is that it doesn't really introduce any layers of indirection. You really are just dealing with very immediately with the SQL and with the types. The problem though is that we don't have a single source of truth, right? If the types and the database disagree, which is what we saw when we made publication year nullable, then you don't know which one's right. And that's the fundamental problem with this approach.

So the first choice you really need to make is where you want the source of truth to be. Should the source of truth for types be in the database or should the source of truth for the types be in TypeScript? If you want the source of truth to be in TypeScript, then that basically means that you're using an ORM. So let's take a look at what this example looks like using TypeORM. So TypeORM is one of the standard ORMs for JavaScript. ORM is Object Relational Mapper. So with TypeORM, we define this data source which says how to connect to our database and we provide a list of entities and they should look familiar. They correspond to the tables in our previous example. And instead of defining the tables through SQL, we define them through JavaScript classes and we provide these decorators to tell TypeORM that this should become a table and that these should become columns. And also a little bit about what type of column they are. So in cases where the TypeScript type is ambiguous, we need to provide SQL type information and we can also provide information about null ability. And for foreign relations, we can provide information about the relationship.

So let's take a look at what the server code looks like. So instead of running a raw SQL query, we get this entity manager and we tell it to find the book class and TypeORM knows to turn that into a select query on the book table. And what's nice about this is that the result type of this is an array of books which is exactly what you would want and so we have enough type information here to catch the error. So we can change this to publication year, and publication year, and let's see if we can load this over here. Hey, there you go. So what do migrations look like in this world? So let's go through the same process of, say that this column can be nullable and we also need to add null to its type. You can see that we've already gotten an error in our typescript, which is good. One of the cool things about an ORM is that it can actually generate the migrations for you. And so here it's going to detect what we've changed in our typescript and it's going to tell us that it has generated a migration.

So let's take a look at that. So it's kind of what you would expect, alter table, alter column, drop null. The down migration reverts it. Pray that you never have to run the down migration. So yeah, let's try running this migration. So it says it should commit. Great. And now let's fix this error about a possible null object. So I've got fix here. Okay. So let's give that a second. And once this loads, should hopefully have a fix. Yeah.

Great. Okay. So since we've included information about the relationships between the entities, we can also go sort of to the next level, we can tell type ORM to pull in the created by relation. And now we can add another column and we can do book.createdby.name. So this should pull in the name of the author in addition to publication date. Pretty cool. So that's what it looks like if you put the source of truth in TypeScript.

So there's some good things about this. ORMs definitely solve the problem of keeping your types and your database in sync. It's pretty nice that they generate migrations for you and the simple queries really are pretty direct to write in ORMs. And ORMs, you'll find a lot of dislike for them online, but it's pretty undeniable that they're very widely used. So on the downside, they are the classic example of a leaky abstraction. So the theory with an ORM is that you can treat the database as an implementation detail and you can just work in TypeScript. But in practice, that doesn't really work. To use an ORM effectively, you need to know SQL, you need to know TypeScript, and you need to know how to use the ORM. And so doing things like fine tuning the performance of a query, you wind up working with your ORM to try to produce a really specific SQL query, which is just kind of adding overhead. And also, if you work in an environment where there's multiple users of your database, the other users may have problems with your being, you're putting the source of truth in TypeScript rather than the database itself.

There's also just a lot more churn in the flavor of the month with ORMs than there is with databases themselves. Postgres and MySQL have been around for decades now and are still quite popular.

So, what if you're not going to use an ORM? Well, then it's sort of undeniably useful to have a TypeScript version of your schema. So, you can go the other way. You can go from the database schema to TypeScript. So, let's take a look at that. So, let's look at the tools to generate TypeScript schema files from your database.

So, kind of the granddaddy in this space is Schema TS, which got a lot of stars. And then no changes in over four years. So, it also got a lot of forks. So, then there's this PyST Schema TS, which is about two years out of date now. So, there's a lot of forks. And I have one, as well, called pg2ts. And so, that's the one I'll be showing everyone today.

So, the idea with pg2ts is that you run a command and so, pg2ts generate, it connects to your Postgres database and it outputs this DB schema file. So, let's take a look at that DB schema file. So, auto-generated and it has interfaces for all of our tables. So, the nice thing about this is you don't have to write this book type by hand. So, let's plug that in, just like we did before. Let's go import, and now we should get a type error. Yep, so, it should be publication year and this should also be publication year. There we go. And let's see if that fixes the issue. Sure enough, it does.

So, superficially, this might not seem that different than what we did before, when we wrote out the type by hand, but the beauty of this is that in practice, you would run PGTTS as part of your continuous integration testing. And so, you would make sure that this TypeScript schema file and the database never got out of sync with each other. So, the database is the source of truth and this DB schema is derived from it. So, let's try running the migration. So, now, if we look at this publication year, it's nullable. And let's bring up our DB schema again.

Okay. So, I'm just going to rerun PGTTS generate. So, it's going to scan our database again and lo and behold, publication year is now nullable. And so, now we also get a type error, same as before. So, that's what a schema generator does. So, it does a really good job of producing entities that correspond to your database types in your typescript and keeping them in sync. The downside is it does add a build step and you do have to manually add the type annotations for your queries. Unless you add some other process which we will talk about later on. But, in general, if you are not using an ORM, you should use a schema generator.

So, once you've done that, kind of your next question is whether you want to use a query builder or write raw SQL. So, let's look at the query builder option.

So, connects JS is probably the most popular query builder for JavaScript and TypeScript. So, let's take a look at what this example looks like using connects. So, to query the database, we use this connects object and we tell it we want to look at the book table and we want to select everything from it. And connects is smart enough to figure out that it has a book type and we already get some type errors, which is what we wanted.

So, how does this work? So, I'm assuming that you're running pg2ts or something like that to get this book interface to find in TypeScript. That's an important building block. Then there's also this connects types table, which you can use to tell Kinects about your schema. So, you define this tables interface and it has a list of all your tables. And it's able to, if you query from one of these tables, it's able to pull in the correct types. So, that's pretty neat.

So, we can go ahead and fix the error here. And so, over here. Great. So, let's save that. And reload. Great. The bug's fixed.

So, let's take a look at what a migration looks like in this world. So, I'm gonna go ahead and run my migration. And so, now, as you might remember, the publication year is nullable. So, now, I have to regenerate the DB schema. So, now, if I go ahead and look in DB schema, yep, publication year is nullable. And sure enough, we have a type error in index.ts, which is exactly what we want. And so, we can go ahead and plug in the bug fix, and if we go over here, great, it's fixed.

So, you can also do slightly more elaborate queries with connects. So, here's an example of joining the books table on the author. So, let's plug that in. So, we're querying the books table as before, but now, we're going to join on the users table, joining the book.createdBy column to the users.id column, and if you look at the inferred type, it's book and users, the intersection type, which is actually exactly right.

So, let's go ahead and add the author. I think it comes out as name, and if we save, we should be able to load. Yep, so it worked, pretty cool.

Okay, so how does Query Builder stack up against some of the other solutions we've looked at? So, I think the nice thing here is that you definitely get accurate types, and you don't have to plug them in by hand. It's able to use the DB schema and your queries to get you accurate types without explicit annotations, which is great, but unfortunately, Query Builders are another classic example of a leaky abstraction. So, in order to use a Query Builder, especially for more complicated queries, you really need to know your TypeScript, you really need to know your SQL, and you really need to know your Query Builder. So, you know, if we look at the Connex docs, they claim their TypeScript support is best effort. I think they're underselling it a little bit. I think it's actually quite good. But if you look at some of these queries, it's not totally clear to me that the Query Builder syntax is any simpler or more complicated than the Postgres SQL syntax. And at that point, the thing that runs is SQL and so you should probably just be writing the SQL anyway.

So what if you're not going to use a Query Builder? Well, then we have another option, which is a tool to convert your SQL queries directly into TypeScript types. So there's a few tools like this and we'll look at PGTyped. PGTyped is a tool that reads SQL queries and generates TypeScript types for you. So it has some nice docs here. So let's take a look at how it can help us. So with PGTyped, you import this SQL tag and you add it to your SQL queries and then you pass in your database pool and any parameters to the query and it runs the query for you. And the type comes back as any. So yeah, what's the point of that? So the interesting thing here is that we can run PGTyped and it will generate a um types file for us so we can take a look at this. So this has the result type of the query and if we had parameters on the query it would include that too. And the idea is that you add this query type to the tagged template and we have to import it. And there you go and now we have a nice type and it shows the error so you know we can fix that. Fix it and there you go. So what's interesting here is I didn't run, I didn't generate a db schema it's just doing introspection on this query against our live database schema. So we can try running a migration. So let's go ahead and do that. So we can just double check that our book, yep, publication year is nullable and so we don't have an error yet but if we go back and we rerun pgTyped now it knows that publication year is nullable and so we need to need to plug in a more careful expression there. So let's give that a shot. Yep, there we go. And so we can also try a more complicated query here.

So here I'm manually joining the author so that I can get that. So I can pull out the author name from the other table, from the user's table. So now that I've changed my query, I need to run pgTyped again. And if I look at this query, now it has authorName and it has the right type. And so I can go in here and I can add my authorName. And let me look. Server will restart, and there you go, we have the authornames.

Okay, so how does this stack up? So the great thing is that you just get to write raw SQL and you get types back for your queries. There are a few downsides though. Not all types come back perfectly. So there are some issues around detecting when a column in the results of a query can be null. As with some of the other approaches, it adds a build step. The queries are a little bit, the query results are a little bit duck typey. So if you run the exact same query in two different places, you'll get two distinct types. They'll be compatible. And so with structural typing, that's fine. But I think it's usually nicer to work with the DB schema. And it just feels like a lot of fuss sometimes for really simple queries that you have to write out this SQL tagged statement and then add a type that you generate to them. So I think that between SQL to TS conversion and the query builders, there is kind of a sweet spot.

So I want to show you what that looks like. There's a few approaches. So Zapatos is one example. But I'm going to show you PGTyped and CreedlyTyped, which is a tool that I built. So the idea with CreedlyTyped is that it generates all the simple queries for you. But if your query stops being simple, if it gets more complicated, then you should just run something like PGTyped. It makes no attempt to model those queries. So let's take a look. So we're going to create a typedDB. And we're going to pass it a tables object from our db schema, which is generated as before. And then instead of generating the GetBooks query this way, we're going to do getbooks equals typedDB.table.

It knows about our tables. And we're just going to generate a select. And we just call this with our db pool. And great. So it knows about the types, the types reference our db schema. We don't have this because we haven't done a join.

And so if we go over here, yep, it works. And you can add some slightly more complicated things like if you want to do a join. It actually pulls it in as a, what is it? Created by. Yeah, created by. It actually pulls it in as a sub-object. So it's author.name. And yep, there we go.

Okay, so how does this stack up? I think pretty well. I think it's a good middle ground. For complicated queries, you just get to write rawSQL and you get accurate types. But for the simpler queries, which in my main application are something like 70% of the queries. You don't have to go through much process and you get accurate types based on your DB schema. There are some downsides. It does still add a build step. And I think there's sometimes the temptation to use the simple generated queries more often than you should. So instead of running 10 simple queries, you should probably move some of that logic into Postgres and just run one query.

So that's the talk. There's a lot of choices to make when you're working with SQL and TypeScript and none of the options are perfect. But I think the important thing is just to be conscious of which approach you're taking and understand the trade-offs. Hope you enjoyed the talk. Check out my book and my blog and follow me on Twitter.