Built an unconventional database thing

Hey folks!

I just built a tool to statically evaluate SQL queries in JavaScript and TypeScript code: 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:

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:

// 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:

$ 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:

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

Let's run it again:

$ 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:

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
}

$ 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:

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
}

$ 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!

Blog

Built an unconventional database thing

Andy Wermke

What does it do?

More advanced stuff

Why?

Feedback

Join Our Newsletter. No Spam, Only the good stuff.

Related