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 they're authors and book reviews and I've even got some 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 hmm so I think it 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 query result any which means that book has an any type so maybe maybe I should write a 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 string and got created by which string and we've got title which is a string we've got publication year I'm 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 okay and with the node progress library I believe you can specify a return type for a query and hey lo and behold we have we have some type errors so um yeah I think I just misspelled year it should be publication year so let's um 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 sequel but it's not the full story so let's um let's see what happens when you run a migration so I'm gonna I realize that the publication year could actually be null if we 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 um 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 no and fun fact in javascript 20 22 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 um yeah you can't 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 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 gonna 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 raw SQL and hand coding types for the results there are some good things about this we as we saw we do get some type safety and another nice thing is that it doesn't really introduce any any layers of indirection you you really are just dealing with you know 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 if the types and the database disagree which is what we saw when we made publication you're nullable then you don't know which one's right and that's kind of the fundamental problem with this approach so the first 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 type ORM so type ORM is one of the standard ORMs for javascript ORM is object relational mapper so with type ORM we define this data source which says how to connect to our database and we provide a list of entities and this 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 type ORM 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 about nullability 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 can tell it to find the book class and it type ORM 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 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 a error in our typescript which is good one of the cool things about a one of the cool things about an ORM is that it can actually generate the migrations for you and so here it's gonna detect what we've changed in our typescript and it's gonna tell us that it has generated 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 you know pray that you never have to run the down migration so yeah let's try let's try running this migration so it says it should met great and now let's you know let's fix this error about a possible null object so I've got fix here okay so a second and once this loads should hopefully have the fix yeah great okay so um since we've 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 dot created by 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 it definitely saw or I'm 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 quick the simple queries really are pretty direct to write in 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 on the downside they are there 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 you to make to use an ORM effectively you need to know sequel you need to know typescript and you need to need to know how to use the ORM and so doing things like you know fine-tuning the performance of a query you wind up you know working with your ORM to try to produce a really specific sequel query which is just kind of adding overhead and also if you if you work in an environment where there's multiple users of your database you know the other the other users may have problems with your being you're putting source of truth in typescript rather than the database itself there's also just a lot more churn in you know the flavor of the month with ORMs than there is with databases themselves you know Postgres and MySQL are 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 and so you can kind of 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 and there's this pie SD scheme sts which is about two years out of date now so there's a lot of forks and I have one as well called PG to TS and so that's the one I'll be showing everyone today so um the idea with PG to TS is that you you run a command and so PG to TS 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 um you don't have to write you don't have to write this book type by hand so let's let's plug that in just like we did before import and now we should get a type error yep so publication year and this should also be a location 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 ski you would run PG to TS as part of your continuous integration testing and so you would make sure that this schema file is 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 um try running the migration so now if we look at this publication year it's nullable and bring up our DB schema again okay so I'm just gonna rerun PG to TS generate so it's gonna scan our database again and lo and behold publication year is now nullable and so 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 keeping producing entities that correspond to your database types in your typescript and keeping them in sync the downside is it does out of build step and you do have to manually add the type annotations for your queries unless you 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 the 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 so I'm assuming that you're running PG to TS or something like that to get this book interface defined in typescript that's an important building block but then there's also this connects types table which you can use to tell connects 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 that's pretty neat so we can go ahead and fix the error here and so over here reach so save that and we go over here and reload great the bugs 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 indexed up TS which is exactly what we want and so we can go ahead and plug in the bug fix and 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 to the 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 dot created by column to the users dot 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 a name and if we save 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 type script you really need to know your sequel and you really need to know your query builder so you know if we we look at the connects 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 post 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 PG typed PG typed 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 PG typed you import this SQL tag and you add it to your SQL queries and then you you know 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 we can run PG typed and it will generate a 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 you add the you add this query type to the tags template and we have to import it and there you go and now we have a nice type and it shows the air so you know we can fix that fix it and there you go so what's interesting here is I 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 PG typed 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 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 author or from the users table so I need to now that I've changed my query I need to run PG typed again and if I look at this query now it has author name and it has the right type and so I can go in here and my author name and let me look server will restart and there you go we have the author names okay so how does this stack up um so the great thing is that you just get to write raw SQL and you get types back for your queries um there are a few downsides though not all types come back perfectly so there's there are some issues around detecting when a column and a the result of a query can be null as with some of the other approaches that 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 you know with structural typing that's fine but I think it's usually nicer to to work with the DB schema and it just feels like a lot of fuss sometimes for for really simple queries that you have to write out this SQL tagged statement and then add a add a type that you generate to them so I think that between sequel 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 there's a few approaches so zapatos is a is one example but I'm going to show you PG types and crudely typed which is a tool that I built so the idea with crudely typed 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 PG typed it makes no attempt to it makes no attempt to model those queries so let's take a look so we're going to create a typed DB 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 get books query this way we're going to do get books equals typed DB 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 you know we don't have this because we haven't done a join and so go over here yep it works and you can you can add some slightly more complicated things like you know 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 dot name yep there we go okay so how does this stack up I think pretty well I think it's a I think it's a good middle ground for complicated queries you just get to write raw SQL and you get accurate types but for the simpler queries which you know in my main application are something like 70% of the queries you don't have to go through much process and you you get accurate types based on your DB schema you know there are some downsides it does still add a build step and I think there's sometimes a temptation to use the simple generated queries more often than you should so instead of running you know ten simple queries you should probably move some of that logic into Postgres and just run one query so that's the talk you know there's a lot of choices to make when you're working with SQL and typescript and you know none of the none of the options are perfect but I think the important thing is just to be conscious of which approach which approach you're taking and understand the trade-offs hope you enjoyed the talk check out my check out my book and my blog and following me on Twitter thanks