andywer / postguard

🐛 Statically validate Postgres SQL queries in JS / TS code and derive schemas.

postguard

Validate SQL queries in JavaScript and TypeScript code against your schema at build time 🚀

Locates SQL template strings and schema definitions in your code. Evaluates the queries, matching them against your database schema. Supports type-checking via TypeScript, so you get statically typed SQL queries validated against your database schema 😱😱

Use with squid. It provides SQL tagged template strings, auto-escapes dynamic expressions to prevent SQL injections and comes with some syntactic sugar to write short, explicit SQL queries.

🦄  Validates SQL template strings in code
🚀  Checks SQL queries syntax and semantics
⚡️  Works statically, without additional runtime overhead
⚙️  Built on top of Babel & TypeScript
🛠  Uses libpg_query, the actual Postgres SQL parser

---

Installation

npm install --save-dev postguard

# or using yarn:
yarn add --dev postguard

CLI

Run the tool like this:

postguard src/models/*

We can use npm's npx tool to run the locally installed package:

npx postguard src/models/*

Command line options

Usage
  $ postguard ./path/to/source/*.ts

Options
  --help        Print this help
  -w, --watch   Watch files and re-evaluate on change

Guide

• Usage - Hands-on examples how to use the tool
• Validations - List of validations that will be performed

Motivation

Let's quickly compare the options you got when writing code that uses a relational database.

Our sample use case is updating project rows that are owned by a certain user.

Plain SQL

Sample:

const { rows } = await database.query(`
  UPDATE projects SET
    last_opened = NOW(),
    open_count = open_count + 1
  WHERE
    projects.id IN (
      SELECT project_id FROM project_members WHERE user_id = $1
    )
  RETURNING *
`,
  [ userId ]
)

Pro:

• Efficient queries
• Explicit - No magic, full control
• Functional stateless data flow, atomic updates

Con:

• Very easy to make mistakes
• No way of telling if correct unless code is run
• Can be quite verbose
• Requires knowledge about SQL & your database
• No type safety

ORMs (Sequelize, TypeORM, ...)

Sample:

// (Model definitions not included)

const user = await User.findById(userId)
const projects = await user.getProjects()

const updatedProjects = await Promise.all(
  projects.map(async project => {
    project.last_opened = new Date(Date.now())
    project.open_count++
    return project.save()
  })
)

Pro:

• Easy to get started
• Type-safety
• Less error-prone than writing raw SQL
• Requires no SQL knowledge

Con:

• Implicit - Actual database queries barely visible
• Usually leads to inefficient queries
• Update operations based on potentially stale local data
• Virtually limits you to a primitive subset of your database's features

Query builder (Knex.js, Prisma, ...)

Sample:

// (Model definitions not included)

const usersProjects = await prisma.user({ id: userId }).projects()

const updatedProjects = await Promise.all(
  projects.map(project =>
    prisma.updateProject({
      data: {
        last_opened: new Date(Date.now()),
        open_count: project.open_count + 1
      },
      where: {
        id: project.id
      }
    })
  )
)

Pro:

• Explicit - Full control over queries
• Functional stateless data flow
• Type-safety

Con:

• Additional abstraction layer with its own API
• Atomic updates still hardly possible
• Requires knowledge about both, SQL & your database plus the query builder API

SQL with squid & postguard 🚀

Sample:

// (Schema definition not included)

const { rows } = await database.query<ProjectRecord>(sql`
  UPDATE projects SET
    last_opened = NOW(),
    open_count = open_count + 1
  WHERE
    projects.id IN (
      SELECT project_id FROM project_members WHERE user_id = ${userId}
    )
  RETURNING *
`)

Pro:

• Explicit - Full control, no implicit magic
• Fast due to absence of abstraction layers
• Functional stateless data flow, atomic updates
• Full query validation at build time
• Type-safety

Con:

• Requires knowledge about SQL & your database

Debugging

Set the environment variable DEBUG to postguard:* to enable debug logging. You can also narrow debug logging down by setting DEBUG to postguard:table or postguard:query, for instance.

Questions? Feedback?

Feedback is welcome, as always. Feel free to comment what's on your mind 👉 here.

License

MIT

postguard's Issues

Support glob paths

Questions? Feedback?

Add your feedback as a comment to this issue 📋

Set up GitHub actions

Validating TypeScript types vs. database types

I'm not sure if this is something that postguard handles.

I have a table like this:

CREATE TABLE users (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid (),
  name text NOT NULL,
);

Then in my code:

const usersTable = defineTable('users', {
  id: Schema.Number,
  name: Schema.String,
});

type UserRecord = TableRow<typeof usersTable>;

console.log(await pool.query<UserRecord>(
  sql`SELECT id, name FROM users WHERE name = 'test'`,
));

Postguard sees no issues here. I was hoping it would fail since I defined id in the table as Schema.Number but the data type of the id column is uuid which is parsed as a string by node-postgres. Am I doing something wrong or this is not supported / not how it works?

Selectively disable validation of queries

Something along those lines:

/* pg-lint:disable */
await database.query(sql`...`)

or

/* pg-lint:disable typechecks */
await database.query(sql`...`)

Query Parsing using spreadinsert

I am probably doing something wrong

this is the table in code

const usersTable = defineTable("users", {
  uuid: Schema.default(Schema.Uuid),
  phone: Schema.String,
  created_ts: Schema.default(Schema.Date),
});

export async function queryCreateUser(
  record: NewUserRecord
): Promise<UserRecord> {
  const query = sql`
  INSERT INTO users ${spreadInsert(record)} RETURNING phone
`;

my postgres table does not have the phone column at this point in time. Should postguard catch this issue?

Add strict mode

Fail if type cannot be inferred in TypeScript code. So every (?) warning should become an error in strict mode.

TypeScript: Check database.query<RecordType> calls

Match against the columns returned by the SELECT / INSERT ... RETURNING ... statement.

Add watch mode

pg-lint --watch "./path/to/sources/*"

Validate spreadUpdate()

spreadUpdate() has been added to squid in v0.4.0.

Programmatic usage / Use without squid?

Hi,
Looks like a great tool 👍
Is it possible to use programmatically and without squid?

We have a large codebase which uses knex. Changing to use squid is a significant amount of work. However we would love to use this tool to check all possible SQL variations we generate and preferably do it programmatically within our unit tests.
Since knex can generate the plain SQL strings, is there a way to simply pass a SQL string for validation?

Check for column completeness on INSERT

Be aware that Schema.nullable() implies a DEFAULT NULL, at least in Postgres (& MySQL).

Also related to #8.

Provide way to declare that column has a default in schema

Important for validation of INSERTs. Also need to add InsertionValues<Schema> for type inference from schema..

Support for column aliases (`as`)

Thanks for writing this library, it's exactly what I was looking for.

Is there a way to support column aliases? Here's what I get right now:

✖ Query validation failed in server/models/SomeModel.ts:39:13:

  No table in the query's scope has a column "user_token".
  Tables in scope: "some_table"

  37 | export async function getUserToken(requestId: string): Promise<string | null> {
  38 |   const { rows } = await database.query<{user_token: string}>(sql`
> 39 |     SELECT details -> 'user_token' AS user_token FROM some_table WHERE
     |            ^
  40 |       request_id = ${requestId} AND
  41 |       details -> 'user_token' IS NOT NULL
  42 |     ORDER BY timestamp DESC;

The workaround that seems to be fine is by using sql.raw:

    SELECT ${sql.raw('details -> \'user_token\' AS user_token')} FROM some_table WHERE 
      request_id = ${requestId} AND 
      details -> 'user_token' IS NOT NULL
    ORDER BY timestamp DESC;

Is this the best way to handle it? Thanks!