Skip to content
🐛 Statically validate Postgres SQL queries in JS / TS code and derive schemas.
Branch: master
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
devtools Add query parser dev tool (#15) Jan 2, 2019
docs Rename package to `postguard` (#26) Jan 8, 2019
src Support spreadUpdate() (#28) Feb 6, 2019
test Support spreadUpdate() (#28) Feb 6, 2019
types Resolve query result rows structure Jan 4, 2019
.gitignore
.travis.yml Add Travis CI config Dec 23, 2018
LICENSE Change license to GPL v3 Jan 4, 2019
README.md
package-lock.json 0.2.0 Feb 6, 2019
package.json 0.2.0 Feb 6, 2019
tsconfig.json Fix tsconfig.json custom types path Jan 4, 2019
tslint.json Add prettier & tslint Dec 23, 2018

README.md

postguard

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

Build status npm version


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



Screencast

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

You can’t perform that action at this time.