• Stars
    star
    161
  • Rank 225,026 (Top 5 %)
  • Language
    TypeScript
  • License
    GNU General Publi...
  • Created over 5 years ago
  • Updated about 1 year ago

Reviews

There are no reviews yet. Be the first to send feedback to the community and the maintainers!

Repository Details

πŸ› 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 πŸš€

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

More Repositories

1

webpack-blocks

πŸ“¦ Configure webpack using functional feature blocks.
JavaScript
2,979
star
2

threads.js

🧡 Make web workers & worker threads as simple as a function call.
TypeScript
2,942
star
3

leakage

πŸ› Memory leak testing for node.
JavaScript
1,583
star
4

pg-listen

πŸ“‘ PostgreSQL LISTEN & NOTIFY for node.js that finally works.
TypeScript
540
star
5

typed-emitter

πŸ”© Type-safe event emitter interface for TypeScript
JavaScript
250
star
6

use-inline-memo

βš›οΈ React hook for memoizing values inline anywhere in a component
TypeScript
163
star
7

laravel-js-localization

Simple, ease-to-use and flexible package for the Laravel web framework. Allows you to use localized messages of the Laravel webapp (see `resources/lang` directory) in your Javascript code.
PHP
143
star
8

squid

πŸ¦‘ Provides SQL tagged template strings and schema definition functions.
TypeScript
124
star
9

ava-ts

πŸš€ Fork of the AVA test runner with native typescript support
JavaScript
116
star
10

postcss-debug

Debug your postcss workflow with ease! Creates snapshots of your CSS files before/after each postcss plugin is run.
JavaScript
94
star
11

react-usestyles

πŸ– Style components using React hooks. Abstracts the styling library away.
JavaScript
87
star
12

postcss-theme

PostCSS plugin to enable versatile theming.
JavaScript
87
star
13

react-stateful-fn

βš› Stateful functional components for React.
JavaScript
57
star
14

puppet-run

πŸ€– Run anything JavaScript in a headless Chrome from your command line
TypeScript
53
star
15

threadpool-js

Javascript thread pool implementation using web workers.
JavaScript
47
star
16

jquery-dim-background

jQuery plugin to dim the current page except for some user-defined top elements.
JavaScript
43
star
17

observable-fns

πŸ•΅οΈβ€β™€οΈ Light-weight observable implementation and functional utilities in TypeScript
TypeScript
41
star
18

npm-launch

πŸš€ Minimalistic task runner on steroids!
JavaScript
39
star
19

drag-mock

Trigger HTML5 drag & drop events for testing
JavaScript
35
star
20

gear

πŸ›  Experimental tool to bootstrap typed JavaScript code.
JavaScript
33
star
21

proposal-double-colon-types

πŸ€“ JS / Flow syntax proposal. Types Γ  la Hindley-Milner.
20
star
22

http-event-stream

πŸ“‘ Modern spec-compliant Server Sent Events stream implementation.
TypeScript
19
star
23

ts

βš™οΈ The CLI that TypeScript deserves.
TypeScript
18
star
24

key-store

πŸ” Isomorphic encrypted key store written in TypeScript.
TypeScript
17
star
25

plow

πŸ‘¨β€πŸŒΎ Postgres migrations and seeding made easy
TypeScript
14
star
26

isomorphic-crypto

πŸ”’ Isomorphic crypto package for node and the browser.
JavaScript
12
star
27

type-reflect

☝️ TypeScript plugin providing access to type information at runtime
TypeScript
11
star
28

srv

πŸ“‘ Functional node server. Composable routing. Take a request, return a response.
TypeScript
9
star
29

rungpt

GPT client with local plugin framework, built by GPT-4
TypeScript
9
star
30

php-easygit

Manage Git repositories from within your PHP webapp. Commit, branch, clone, checkout, ...
PHP
7
star
31

zaster

πŸ’Έ Headless multi-blockchain wallet and SDK.
TypeScript
7
star
32

react-commandments

πŸ“– Thou shalt honor thy reactive code and keep it holy.
6
star
33

koa-router-index

Koa v2 middleware to create an index page for API servers.
JavaScript
5
star
34

gulp-elixir-modules

Elixir extension for handling frontend modules easily.
JavaScript
5
star
35

json-sql-import

Small PHP tool to import JSON data into database tables using transformation rules.
PHP
4
star
36

puppet-run-plugins

🧩 Plugins for puppet-run.
TypeScript
4
star
37

shutter-legacy

πŸ“Έ Visual snapshot testing with no effort.
TypeScript
3
star
38

deep-assert

πŸ” Better deep-equals comparison, supporting custom property assertions and pretty diffs.
TypeScript
3
star
39

ideabox

Place to collect techy ideas and get feedback.
1
star
40

bundle-decomposition-research

JavaScript
1
star
41

stellar-wallet

With the new Stellar wallet to the moon πŸš€
JavaScript
1
star