Running home on Typescript and Postgres with Zapatos
The debate over how effective and purposeful ORMs are, have been around for quite some time now. (A summary to the famous and scary analogy ORMs are the Vietnam in Computer Science can be found here.) One of the biggest complaints amongst others, is the inadequate abstraction they provide. Another one is no matter which dialect is used (HQL/JPQL etc.), in the end, its SQL which speaks to the database and more often than not, the use of SQL becomes inevitable when the problem becomes complex. And its always easier to fine tune queries in SQL. Martin Fowler in his article OrmHate speaks both of these issues and suggests that the more practical solution would be to have the relational model in memory than trying to avoid it, with enough manholes for the use of SQL as required.
But I’m not here to hate on ORMs, although I felt like it’s a sensible way to start this post. Because this, is about an alternative. I’m currently working on a project that’s being written in Typescript with a Postgres database. The architects did not want to have the burden and the overhead of having an ORM, having to eventually write SQL, but they still wanted type safe abstractions of the tables to improve the developer experience, code efficiency, maintainability and testability. And they had found a wonderful library which I quite enjoy using and exactly answers the controversial downsides of an ORM while still preserving the good bits.
The problem
As a Typescript developer using a relational database, there are a few core features I’d expect from a framework/library that enables me to be more productive.
- Access my database schema tables through a set of strongly typed interfaces that represents the tables.
- Avoid boilerplate SQL for CRUD.
- Means of leveraging the power of SQL in complex scenarios.
Even though these sound exactly like the core features any ORM would have, this list lacks one important feature that makes ORMs what they are. The abstraction of the relational engine trying to present it in an object oriented way, ultimately resulting in all the backlash for leaky abstraction.
What Zapatos provides for Typescript and Postgres developers is exactly that. It’s not an ORM. But it’s everything you need to be productive as a developer, and run fast!
Stepping into Zapatos
We’re having a database first approach in our project. So we have our schemas and tables already in place. With Zapatos CLI, we’re able to generate a Typescript schema representing all tables in our database.
I’ll take a bit of a ‘complex’ table structure for the example. Consider the following.
The above table has a jsonb column as well as a few usual text columns. After running the Zapatos CLI, I’m presented with something similar to the following.
The schema consists of a namespace definition for each table found in the Postgres schema. And inside the namespace, are 4 main interfaces that the developer will be interacting with — Selectable, Whereable, Insertable and Updatable. As naming suggests, this gives type support for all basic operations where the given table is involved.
Of course the configuration options which is used by the CLI to generate the Zapatos schema lets you customize how your columns should be exposed to the schema. For example, in this table, we might need to exclude user_name column from being exposed as Updatable. This can be easily achieved with columnOptions in ZapatosConfig, where the required column can be excluded from updates.
How to CRUD
Building on the interfaces that are generated, Zapatos provides a set of ‘shortcut’ methods where developers can perform basic CRUD operations. Code for a simple select looks like the following. The table and column names are string literals with type and auto-complete support.
This returns an array of user_state.JSONSelectable which is derived from line 27 in the above schema.d.ts.
Zapatos supports two more variants of select — selectOne and selectExactlyOne — where a single user_state object is returned in both, but selectOne is of type user_state | undefined and selectExactlyOne throws an error if no record or more than one record is found.
The same type of shortcuts are provided for insert, update and delete making the life of the developer quite easy. In addition, Zapatos supports count, upsert and truncate operations through shortcut methods.
These shortcuts allow the developer to write clean, concise and type safe SQL at the development time. And it gives the same level of developer experience as any ORM would, with its object model.
When things get complicated…
But how does type safety applies when things get complicated, with complex select queries? Zapatos has a great fallback (as I like to call it, although it’s really a core feature) with tagged template strings, where it gives the developer the option to define the required types to be used within the query as well as the return types.
In the above code block, the type arguments s.user_state.SQL and s.user_state.Selectable[] are respectively the types which are to be used within the SQL statement using string literals and the return type. Since we’re querying only from the user_state table, the only type we need within the SQL is user_state.SQL. If types from more tables are needed, those can be appended with a | sign. The second argument user_state.Selectable[] indicates that this statement returns an array of user_state.Selectable type when run.
The self property allows the developer to refer to the same column that’s preceding in the key name (state in this case) and param property allows to refer any variables outside the SQL to be used as parameters. These two constructs gives quite a power to the developer to write complex SQL queries and while still retaining the type safety within the SQL statement and the objects that are being returned.
About custom types
One last cool feature I want to highlight is the ability to define custom types for generic jsonb columns that we define in the database. Re-visiting the DDL to create the user_state table, the auth_context column is defined as a jsonb column, which means it can hold any json object.
But as developers writing in typescript, of course we’d like to type check the contents of that JSON object as well. And Zapatos has the answer with custom types. This is done through the use of domains in Postgres.
First, we need to define a domain for the desired column — auth_context. And then alter the column defined as jsonb to use the domain type instead of jsonb.
When the schema generation is done again, Zapatos creates a new file with the name of the domain inside zapatos/custom. In this case its AuthContextObject.d.ts. This will also change the type in line 6 of schema.d.ts to AuthContextObject. The initially generated file contains the following line.
Now we can assign any complex type for the AuthContextObject here, replacing db.JSONValue giving complete type support for the formerly ambiguous JSON object.
Transaction Support
Zapatos gives developers the ability to control how the transactions are managed through a transaction function, which essentially lets the developer to run a given set of Zapatos operations inside a single BEGIN/COMMIT block. It lets the developer define a desired Isolation Level for the transaction as well.
Logging/Debugging
Zapatos provides some runtime features where the developer can extract the SQL query that’s constructed, the result that’s returned and the transaction details (such as retries). This can be used for debugging and query optimization tasks.
This is a one-time runtime configuration that could be added to your index.ts.
What’s missing from ORMs
As I mentioned, Zapatos is not an ORM. So it has some drawbacks in terms of features if you are to compare a traditional ORM with Zapatos one on one. Since I have drawn comparisons with ORMs, I’ll try to be fair and make a note of a few important things that Zapatos doesn’t support.
An ORM is usually database agnostic. If we take a code first approach, it has the ‘write once deploy anywhere’ feature. But Zapatos works only with Postgres. It has Postgres specific features when it comes to transaction management (Isolation Levels) and it does not support any other database.
It does not have a code first approach either.
Since ORMs create their own object model out of the relational model, for example, if we query an object with a foreign key reference, the ORM is able to load (lazy or eager) the referenced object into memory and the developer has the ability of accessing that object’s properties using the dot operator. We don’t have that luxury with Zapatos. If we need to access the referenced table properties, either a join or another read statement is required.
Ending note
The experience using the library has been quite smooth so far. Just one point to look out for is the fact that it doesn’t support schema level differentiation when generating the Zapatos schema file. So if your Postgres database has multiple schemas with duplicate table names in each schema, you will run into some problems. And it would be nice to have inner join support through shortcuts methods. It only supports lateral joins at the moment.
In conclusion, Zapatos makes the life really easy for developers that are using Typescript and Postgres. While not burdening ourselves with a fully-fledged ORM, Zapatos gives the much needed type and transaction support and ease of issuing CRUD statements which are essentially the basic expectations while using ORMs.
Happy running!
P.S. Zapatos has quite an extensive documentation filled with many examples here, that will answer any of the how-to questions.
