We all love writing types in TypeScript, but we often find ourselves having to write types in another language as well: SQL. This talk will present the choose-your-own-adventure story that you face when combining TypeScript and SQL and will walk you through the tradeoffs between the various options. Combined poorly, TypeScript and SQL can be duplicative and a source of headaches, but done well they can complement one another by addressing each other's weaknesses.
TypeScript and the Database: Who Owns the Types?
AI Generated Video Summary
The Talk discusses the use of TypeScript and SQL together in software development. It explores different approaches, such as using an ORM like TypeORM or a schema generator like pg2ts. Query builders like connects JS and tools like PGTyped are also discussed. The benefits and trade-offs of using TypeScript and SQL are highlighted, emphasizing the importance of finding a middle ground approach.
1. Working with a Database and Types
I'm working on a new app with a database for books, authors, and reviews. The web server connects to the database, runs a query, and renders all the books. I encountered a bug and discovered the need for types. By defining a book interface and specifying the structure of the table, I resolved the type errors. Running a migration allowed me to handle the possibility of null values for the publication year.
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 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.
2. Handling Database Schema Changes
We encountered an issue when our data schema in the database changed, but the type we manually wrote didn't update accordingly. By making the publication year nullable, we resolved the error. TypeScript provided an error message when we attempted to subtract null, allowing us to fix the issue by adding a conditional.
3. Using TypeScript and SQL with TypeORM
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 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.
4. Migration Process and Source of Truth
So let's take a look at the migration process and fixing errors. By including information about entity relationships, we can pull in additional data from the database. ORMs solve the problem of keeping types and the database in sync, generate migrations, and simplify query writing. However, they can be a leaky abstraction, requiring knowledge of SQL, TypeScript, and the ORM. Fine-tuning query performance can add overhead, and other users may have issues with the source of truth in TypeScript.
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.
5. Generating TypeScript Schema Files
If you're not using an ORM, having a TypeScript version of your schema is useful. Tools like Schema TS, PyST Schema TS, and pg2ts can generate TypeScript schema files from your database. pg2ts connects to your Postgres database and outputs an auto-generated DB schema file with interfaces for all tables. By using this approach, you ensure that the TypeScript schema file and the database never get out of sync, as the database is the source of truth.
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.
6. Schema Generator and Query Builder
I'm going to rerun PGTTS generate to scan the database again. The schema generator produces entities that correspond to database types in TypeScript and keeps them in sync. It adds a build step and requires manual type annotations for queries. If you're not using an ORM, a schema generator is recommended.
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.
7. Using Connects JS for Query Building
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, 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.
8. Using Query Builders and PGTyped
Let's add the author and see if it works. Query Builders provide accurate types without explicit annotations, but they can be complex. PGTyped is a tool that converts SQL queries into TypeScript types, generating a types file. It runs the query and returns the type as any. By adding the query type to the tagged template, we get a nice type and can fix any errors. PGTyped does introspection on the live database schema, allowing us to run migrations and handle nullable fields.
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.
9. Using CreedlyTyped and PGTyped
Here I manually join the author to pull out the author name from the other table. Running pgTyped again with the updated query provides the correct type. Although raw SQL with types is great, there are downsides like imperfect type inference and the need for a build step. The query results can be a bit duck typey, resulting in different types for the same query. The sweet spot lies between SQL to TS conversion and query builders. CreedlyTyped generates simple queries, while PGTyped handles more complex ones. Let's explore CreedlyTyped and PGTyped by creating a typedDB and using the tables object from our db schema.
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.
10. Using TypeScript and SQL: Benefits and Trade-offs
This section discusses the benefits of using TypeScript and SQL together. It highlights a middle ground approach where rawSQL is used for complex queries, providing accurate types. For simpler queries, accurate types can be obtained based on the DB schema. However, there are downsides, such as the need for a build step and the temptation to rely too heavily on simple generated queries. The talk concludes by emphasizing the importance of being aware of the trade-offs involved in working with SQL and TypeScript.
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.