Statically typed PostgreSQL queries in TypeScript

A new approach towards implementing statically typed, PostgreSQL backed service in NodeJS using

Introduction

When was the last time that you wrote SQL by hand? A decade and a half ago, the use of Object-Relational Mapping (ORM) wasn’t widespread. Establishing a connection to the database, sending a query looked just like opening a file and reading some bytes. There were no layers of abstractions. Both the flow the program, and the exact optimization to the queries were up to the developer.

Then we saw the rising adoption of ORMs, an object-orientated mapping layer that aids the programmers to think in object-oriented ways. ORMs give us a complete representation of the database schema in our codebase, provide us with static type checking and object-oriented way of access data. To that end, we sacrificed the freedom to optimize queries manually, and spend additional time working around different issues of ORMs.

Despite these disadvantages, ORM continues to help us write correct programs, by providing a mean to define schema and type check our constructions. Correctness is very important to enterprises building mission-critical applications, and type checking is one of the finest in our arsenal.

But can we have the best in both worlds? The freedom of using raw SQL queries with the static type safety on par with an ORM? The solution I propose is to work backward, instead of generating SQL database schema as it is done by ORMs, let’s generate type definitions from the database schema.

Below, I use PostgreSQL as a representative SQL database because it is more of the most feature-rich and freely available database. The same reasoning can also be applied to other databases like MySQL and SQL Server.

Working backward – Schema driven development

One of the first question to going in the reverse direction of ORM is – why? Although this question is very nicely answered by the Postgraphql project, it is worth highlight a few key points:

  • Postgres has a very strong type system, capable of representing not only the base types such as TEXT, INT, JSON, XML and Arrays, but also composite types defined by users. We can also create domains to constrain their valid values to a subset of what the underlying base types would allow. Most ORMs cannot fully provide these types because they need to maintain a baseline compatibility with different flavors of SQL databases. Starting with a strong type system provided by the database is definitely better than any weaker types provided.
  • There are many tools that help design and visualize database schema, only a few of them support generating ORM code for your language and framework of choice, but almost all of them can generate SQL statements.

That said, we indeed have a motivation to work backward from SQL schema to language objects. Going from schema to objects means to have a reflection of the SQL schema as typed objects.

Javascript is a dynamically typed language. To have some form of static type check, we need to use a programming language that supports it. With the widespread use of Angular 2, we see an increasing adoption of TypeScript, a superset of Javascript that supports static types.

TypeScript allows one to write code in javascript but also being able to define and check types statically.

Here is an example where we define an interface for a book. A book is a plain javascript object that has a title, an author and the year of publication.

1
2
3
4
5
interface Book {
title: string
author: string
publication_year: number
}

Here is an instance of a book:

1
2
3
4
5
let animalFarm: Book = {
title: 'Animal Farm',
author: 'George Orwell',
publication_year: 1945
}

TypeScript does static type checking and will happily compile the expression above.

But the instance below is not a valid instance of type Book, and TypeScript will throw an error on compilation.

1
2
3
4
5
6
let animalFarm: Book = {
title: 'Animal Farm',
author: 'George Orwell',
publication_year: '1945'
// ^----- note that here the type is a string
}

Database and Schema

Suppose we have created a database with a single table called books, with columns title, author and publication_year. Each row within the books table is a record of a book.

1
2
3
4
5
CREATE TABLE books (
title TEXT ,
author TEXT ,
publication_year INT
);

We can query the database using pg-promise, an awesome NodeJS library that provides a promise interface for interacting with PostgreSQL.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
cosnt pgp = require('pg-promise')();
const db = pgp('postgres://postgres@localhost:5432/example');

// Let's get all the book published after 1990
// And print their publication year relative to 2016
let q = 'select * from books where publication_year > 1990'
db.each(q, [], (book) => {
const author = book.author;
const title = book.title;
const relYear = (2016 - book.publication_year).toString();
console.log(author + ' - ' + title + ' (' + relYear + 'yr ago)')
}).catch(error => console.error('failed to query db', error))



// > Tim O'Brien - The Things They Carried (26 yr ago)
// > Paula Hawkins - The Girl on the Train (1 yr ago)
// > ---

Note that we can carry out the relative publication year calculation because it is a number. Our assumption is based on knowing that publication_year is define by the database schema to have type INT. Since we have previously defined an interface for Book in TypeScript, we can use that interface to statically type the result value of our db query.

1
2
3
4
5
6
7
//                v---- note our type annotation
db.each(q, [], (book: Book) => {
const author = book.author;
const title = book.title;
const relYear = (2016 - book.publication_year).toString();
console.log(author + ' - ' + title + ' (' + relYear + 'yr ago)')
}).catch(error => console.error('failed to query db', error))

Voila! We managed to apply static type check to our toy example.

However, in real life, database schema change constantly. Columns get added, removed or merged as the needs arise. It will be an endless chore to maintain the interface declarations in TypeScript. For this method to really scale, we need a way to automatically generate type definitions from a schema.

Schemats - automatic type definitions generation from schema

To this end, we can use Schemats, a tool for automatic TypeScript definitions generation from a database schema. It can be easily installed via npm

1
npm install -g schemats

We will use a real life example here to have a glance on how Schemats automates type definition generation. We first load a partial dump of the OpenStreetMap project data in our local database. The schema of the database is defined here.

To generate interfaces for osm database . with table users under namespace osm, run the command below:

1
schemats generate -c postgres://postgres@localhost/osm -t users osm.ts -n osm

The resulting file is stored as osm.ts.

Writing code with typed schema

Let’s import osm.ts directly.

1
2
3
4
5
6
7
8
9
10
11
import * as osm from './osm'

// Now query with pg-promise and have a completely typed return value
let usersCreatedAfter2013: Array<osm.users>
= await db.query("SELECT * FROM users WHERE creation_time >= '2013-01-01'");

// We can decide to only get selected fields
let emailOfUsersCreatedAfter2013: Array<{
email: osm.users['email'],
creation_time: osm.users['creation_time']
}> = await db.query("SELECT (email, creation_time) FROM users WHERE creation_time >= '2013-01-01'");

With generated type definition for our database schema, we can (as a bonus!) write code with autocompletion and static type checks.

demo 1 demo 2

Whenever the database is updated, we can use Schemats again to regenerate the updated type definition files.

A future without ORMs

We manage to provide static type checks to database queries in an automated way, ORM-less. This gives us the freedom to optimize SQL queries, gain full control on the flow of the program, while still having type checking to guard against mistakes. Schemats is a fun project to work on. It is still very new and has lots of opportunity to improve:

  • Add feature to support more PostgreSQL types.
  • Make it work on MySQL and SQL Server.
  • Integrate with build tools.

If you feel like helping out on any of these features, check it out on Github!

Updates:

2016-12-04: Changed syntax around queries as per Vitaly’s suggestion

2017-02-25: Updated syntax up to date with latest version of TypeScript and Schemats