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
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 Userconst projects = await user const updatedProjects = await Promiseall projects
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 const updatedProjects = await Promiseall projects
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)
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