Running with Zapatos; zero-abstraction Postgres for Typescript

Photo by Joseph Barrientos on Unsplash

Today I’m going to explore a library called Zapatos which styles itself as “zero-abstraction Postgres for Typescript”. Most people have utilized ORMs and found them a bit heavy-handed or struggled to work with conventions of usage. Zapatos provides a command-line tool to autogenerate your typescript schema, which provides support for a couple of really nifty features:

1. Arbitrary SQL: It offers simple building blocks that allow you to write arbitrary SQL queries using tagged templates. You can apply appropriate types to the input and output data, and get static typing while writing your core SQL statements.
2. Everyday CRUD: Zapatos provides shortcut functions that effortlessly generate CRUD (Create, Read, Update, Delete) queries, ensuring fully typed operations without any complications or surprises. The language of the basic CRUD operations is simple and fluent, and via some chaining allows you to work with autocomplete while operating on your schema.
3. JOINs as Nested JSON: By utilizing nested shortcut calls, Zapatos generates LATERAL JOIN queries that enable the creation of complex nested JSON structures. These structures are still fully typed, ensuring data consistency and integrity.
4. Transactions: Zapatos includes transaction helper functions that assist in managing and retrying transactions, simplifying the handling of multi-step database operations.

These features collectively empower developers to work efficiently with their Postgres databases by providing a convenient and typed interface for schema generation, SQL querying, CRUD operations, nested data retrieval, and transaction management. I’m never afraid of adding a weapon to my arsenal so let’s get down to it.

For this we need a Postgres database and schema to work with, so we’ll start out by creating a new project and quickly setting up the required dependencies with a couple of simple commands: git init and touch docker-compose.yml. To make our lives a little easier, we’ll also pull the most recent (as of writing) compose setup from the Postgres documentation.

To set up the initial schema, we’ll make some minor adjustments to that starting script, seen here:

version: "3.1"

services:
    db:
        image: postgres
        restart: always
        environment:
            POSTGRES_USER: admin
            POSTGRES_PASSWORD: admin1pass
        volumes:
            - ./infra/data:/var/lib/postgresql/data
            - ./infra/docker-entrypoint-initdb.d:/docker-entrypoint-initdb.d
    adminer:
        image: adminer
        restart: always
        ports:
            - 8080:8080

Then create a directory to contain our startup scripts with mkdir -p infra/docker-entrypoint-initdb.d, populating an initialization script with touch infra/docker-entrypoint-initdb.d/init-db.sql and filling it with the following code:

./infra/docker-entrypoint-initdb.d/init-db.sql

Running docker-compose up should quickly confirm that our initial scripts run OK and create our new db, but let's double-check that everything is fine. Since our initial docker-compose includes an adminer installation, we can check it by clicking on the URL, entering our admin credentials, and switching to the ecommerce schema to see the tables:

Pretty good so far and simple, now we can start the fun stuff!

Now that we have a schema to interpret, let’s get Zapatos set up!

The next couple of steps will be very easy, all we have to do is change the docker-compose.yml to expose our local ports, install & configure Zapatos, then run the schema generation command line tool. First, let’s fix docker-compose to expose the Postgres service running in docker. This is as easy as adding two lines to the db service declaration:

services:
    db:
      (...)
+      ports:
+          - 5432:5432
    adminer:
      (...)

If you rerun the docker commands this should expose the Postgres service on port 5432 on your local machine.

Next, let’s set up a secondary project for the api with mkdir api then cd in to it. With this done, we can run npx zapatos to be prompted to install Zapatos on the local machine. Once this installation is completed we’ll run touch zapatosconfig.json and configure the file like this:

./api/zapatosconfig.json

With this complete, we should be able to re-run npx zapatos to get our schema generated. This will create a folder called zapatos in our api project with the tables in schema.d.ts! Take a look under the hood there if you want to better understand the API that becomes available once your project is managed with Zapatos.

It’s worth noting that you’re also able to programmatically re/generate the schema using these commands but for our purposes we’re going to keep it simple and run this as-is.

Now we can begin initializing the new project with npm init -y to skip over some configuration. We’ll need an API to run to access information in the tables as well, so we can start by getting a few basic routes and some endpoints for testing.

To set up a project with a consistent style for api access let’s start by modeling the relationships that different endpoints will have in files & folders: mkdir -p api api/pool api/user api/user/address api/product api/order api/order/item then simply follow it up with touch api/index.ts api/pool api/user/index.ts api/user/address/index.ts api/order/index.ts api/order/item/index.ts api/product/index.ts.

With all of these files setup, we’ll hop on over to the main api/index.ts file and set up our initial API:

./api/index.ts

And then setup the user router in the ./api/user/index.ts file:

./api/user/index.ts

Once this is done, we can install some dependencies with npm i dotenv express pg zapatos, npm i --save-dev @types/express @types/node tsx typescript, setup typescript with npx tsc --init then change the package.json scripts to include a way of starting the project:

"scripts": {
  "start": "tsx watch index.ts",
  (...)

With that done, we quickly create a dockerfile capable of running the api on node:alpine (I’m going to use the version that’s installed on my local machine):

./api/dockerfile

And now we can simply run the application as-is with docker-compose up. This should start the server and in terminal, you’ll see some console.logs for confirmation. We can additionally curl the API to see some basic success.

Now that we have an API capable of handling requests, we need to model user creation. For this we’ll need a connection pool which can easily be configured with pg. For this I’ll make this adjustment:

./api/pool/index.ts

And add to a .env a connection string similar to the one configured for the main Zapatos config file except that we’ll use the container name for the host:

PORT=3000
DATABASE_URL=postgresql://admin:admin1pass@db:5432/ecommerce

Now we can implement some user endpoints by opening up ./api/user/index.ts and adding the following code:

./api/user/index.ts

Let’s take a look at the changes implemented here and break down what Zapatos has allowed us to do. On L8 we describe a top-level GET that returns all users and some metadata about them via the db.sql command which allows us to use template string literals to get what is essentially sql autocomplete:

🤯

I’m going to be honest — this is where I really started to get it. You won’t use db.sql a ton unless you really need to control the generated SQL for performance purposes, but this is absolutely awesome. You pretty much can’t mistype your property names and everything that comes out is fully typed by default:

Can’t mistype your table names

No type conversions!

Pretty cool, but as discussed above you’re not going to want to write SQL manually for many tasks. Let’s look at the next couple of lines to better understand the helper functions around CRUD operations. I’m going to jump around a little bit so that the different endpoints function correctly when we execute some example code:

Create/Update

Starting on L15 we can see that the PUT verb is set up with a body using an optional id and email that will allow us to create or update a user account by utilizing the related db.update or db.insert commands.

Get Chris Bauer’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

To test it I’ll run two curl commands — one to create a user account and one to update it and note that the update_by property changes across each execution. Here are the results (note that I’m using make just because typing this out is annoying):

Create!

Update! (note the created date differing from the updated date)

Read

Starting on L52 we have a route parameterized with :id which allows us to pull the id of a user from the URL. Running curl http://localhost:3000/1 will return the first registered user, with the final value being incremental for the target user.

Read!

You wouldn’t necessarily do this with a real user in a real system unless it was some kind of social platform, but it’s a good example of how to use Zapatos’ db.select method to return specific records.

This is all good but we want to start populating some orders for these customers, but to do that we’d need some Products first. Once that’s done we’ll be able to explore creating an Order, then adding OrderItems to it. Let’s kick that off by setting up a product router using the same learnings we derived from the Users.

First we’ll edit the products API endpoints to allow us to create and read all products:

For this example, I’ve built the API to allow for multiple products to be created or updated simultaneously by adjusting the expected type in the API to be an array and writing a for loop to consume the values. Zapatos continues to be excellent at providing us with static typing throughout the development process and there strictly isn’t any need to run the code unless you want to double-check.

Static typing while we’re working on the api endpoints

This is pretty powerful!

Inserting our product listing

Looks like we’re picking up pet supplies

Now that we have a couple of products to work with, the next step is to set up an order flow.

Ordering in this simple app will be pretty straightforward; to add items to an order it first must exist. Normally this would be handled by pulling information out of the user token from the authenticated flow but that’s a bit out of scope for our purposes of exercising Zapatos. Our implementation will allow users to initialize the order by POSTing their user id to an API endpoint.

This results in some pretty straightforward code:

./api/order/index.ts

The order endpoint allows us to explore a new concept in Zapatos: on L17 there is a join denoted by the “lateral” option on the select. What’s really interesting about this is that as we describe the resulting object with “items” as a property, we get an auto-complete of the db.parent call:

The static type checking benefits are endless!

Great stuff! On L25 we see the endpoints capable of adding orders, so let’s add a new order:

The order information for us to use to add products!

Well, our happy path is covered! Every good programmer needs to think of failure cases so let’s see what happens if we use a non-existent user:

The error code is returned with the code as-is

The return of this attempt is that the request fails to create an order, which is exactly what we want with a constraint issue. This makes things quite easy as Zapatos will fail out of the promise and the result is highly interpretable for error handling.

Now that we’ve sorted the orders themselves, let’s implement the order items. Something that will come up pretty quickly is that adding order items is a bit more complicated than just inserting/updating records; our schema includes a property “quantity” which means that we’ll need to adjust that value if someone adds two items to their order. We also have the concept of “price per unit” that is included in the order in case we want to discount multiples of the same item.

Having overly complex logic in the API tier is generally an anti-pattern we want to avoid and standardizing the way we interact with the underlying data provider is a good goal to look for. For this, we’ll write a repository tier:

./order/data.ts

The repository implementation consolidates the code for Create, Update, and Delete based on whether or not a product is already in the order and the quantity being greater than 0. This covers all the necessary business rules. Furthermore, we have now transferred the previously implemented business logic related to order creation and retrieval to the repository, allowing us to extract it from orderRouter. By centralizing the code at the repository level, we can return to the orderRouter and greatly simplify the code:

./order/index.ts

Additionally, we’ve leveraged the transaction client of Zapatos to make sure that while creating or updating order items all operations must complete together or the entire execution fails and rolls back. We can now confidently hop into the itemRouter and implement order items:

./order/item/index.ts

And now for the tests:

Creating an order and adding 3 items

And to test removal:

Removing the fourth item from the orders

I hope that you’ve found this dive into the use of Zapatos as interesting as I did. The benefits of it are numerous; engineers can leverage the simplicity of utilizing building blocks for writing arbitrary SQL queries with static typing ensuring data integrity. CRUD queries are a breeze, ensuring fully typed operations with fluent language and autocomplete support. Utilizing nested shortcut calls enables the creation of complex nested JSON structures through LATERAL JOIN queries. With transaction helper functions, handling multi-step database operations is much simpler.

The library’s design is elegant and simple, and from start to finish this whole project and article probably only took me 5 hours which is a breeze when you’re learning an entirely different approach to data retrieval. I’m very much a proponent of Typescript and static type checking and found myself pleasantly surprised throughout the process of learning about it. Zapatos is a compelling alternative to heavy-handed and buggy ORMs and I’d recommend giving it a shot if you’re working in Typescript, Node, and Postgres.