Built an unconventional database thing
Tags: githunt, postgres, node, typescript
Published: 2019-01-09 20:02:32

---
title: Built an unconventional database thing
description: Statically validated, type-safe SQL queries in node.js
tags: githunt, postgres, node, typescript
published: true
---

Hey folks!

I just built a tool to statically evaluate SQL queries in JavaScript and TypeScript code: [Postguard](https://github.com/andywer/postguard).

It's born out of the desire to write explicit database queries without a query builder, yet statically analyzable, and built on top of Babel and the TypeScript compiler API.


## What does it do?

You query the database like this:

```ts
import { sql } from "squid/pg"
import database from "./database"
import { UserRecord } from "./schema"

export async function queryUserById(id: string): Promise<UserRecord | null> {
  const { rows } = await database.query<UserRecord>(sql`
    SELECT * FROM users WHERE if = ${id}
  `)
  return rows[0] || null
}

```

Define a schema for your tables. The record type can be derived from the schema when using TypeScript:


```ts
// schema.ts
import { defineTable, Schema, TableRow } from "squid"

export type UserRecord = TableRow<typeof usersTable>

const usersTable = defineTable("users", {
  id: Schema.Number,
  name: Schema.String,
  created_at: Schema.Date
})
```


Now let's run `postguard` to validate what we just wrote:

```text
$ npx postguard ./sample/*.ts

✖ Query validation failed in sample/test.ts:7:32:

  No table in the query's scope has a column "if".
  Tables in scope: "users"

   5 | export async function queryUserByID(id: number) {
   6 |   const { rows } = await database.query<UserRecord>(sql`
>  7 |     SELECT * FROM users WHERE if = ${id}
     |                               ^
   8 |   `)
   9 |   return rows[0] || null
  10 | }
```

Ahhh, we have a typo in our query! Let's fix it:

```ts
const { rows } = await database.query<UserRecord>(sql`
  SELECT * FROM users WHERE id = ${id}
`)

```

Let's run it again:

```text
$ npx postguard ./sample/*.ts

✔ Validated 1 queries against 1 table schemas. All fine!
```


## More advanced stuff

Let's take our previous sample code and change the SELECT query:

```ts
import { sql } from "squid/pg"
import database from "./database"
import { UserRecord } from "./schema"

export async function queryUserById(id: string): Promise<UserRecord | null> {
  const { rows } = await database.query<UserRecord>(sql`
    SELECT id, name FROM users WHERE id = ${id}
  `)
  return rows[0] || null
}

```


```text
$ npx postguard ./sample/*.ts

✖ Query validation failed in sample/test.ts:6:40:

  Query's result does not match the expected result type.
    Missing columns in result rows: "created_at"
    Actual columns in result rows: "id", "name"

  4 |
  5 | export async function queryUserByID(id: number) {
> 6 |   const { rows } = await database.query<UserRecord>(sql`
    |                                        ^^^^^^^^^^^^
  7 |     SELECT id, name FROM users WHERE id = ${id}
  8 |   `)
  9 |   return rows[0] || null
```

What happened? We defined that we expect the query to return `rows` of type `UserRecord`:
`await database.query<UserRecord>(/*...*/)`

Postguard evaluated the query and noticed that the result rows of that SELECT query do not match the TypeScript type `UserRecord`, since that type has a `created_at` property.

The fix is trivial:

```ts
import { sql } from "squid/pg"
import database from "./database"
import { UserRecord } from "./schema"

export async function queryUserById(id: string): Promise<UserRecord | null> {
  const { rows } = await database.query<UserRecord>(sql`
    SELECT * FROM users WHERE id = ${id}
  `)
  return rows[0] || null
}

```

```text
$ npx postguard ./sample/*.ts

✔ Validated 1 queries against 1 table schemas. All fine!
```


## Why?

So why did I even spent my time on that? Having worked with ORMs for years, I slowly grew more and more tired of them. A query builder seemed like a better solution.

Now the issue with query builders is that you effectively write SQL queries, but not as SQL queries; you formulate them using the query builder's proprietary API. Now I need to read up on two complex interfaces: My Postgres' fancy SQL features AND the query builder's comprehensive API...

Writing good old SQL feels like the natural way, so back to that. But now I've lost all confidence in my code and need to keep a really high test coverage, since I cannot statically reason about my queries. I actually need to run them to see if they work at all.

Enter the stage: Postguard. Back to confidence and short feedback cycles.


## Feedback

So this tool is still pretty damn young and should not be used for production code yet.

**I would love to hear some feedback regarding the overall approach, though!**

The concept might seem a bit esoteric, but the code feels quite natural.

Share anything that's on your mind :)


Happy hacking!