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
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
License
MIT