All Projects → andywer → Postguard

andywer / Postguard

Licence: gpl-3.0
🐛 Statically validate Postgres SQL queries in JS / TS code and derive schemas.

Programming Languages

typescript
32286 projects

Projects that are alternatives of or similar to Postguard

Squid
🦑 Provides SQL tagged template strings and schema definition functions.
Stars: ✭ 57 (-45.19%)
Mutual labels:  schema, sql, query-builder, sql-query, database, postgresql
Querybuilder
SQL query builder, written in c#, helps you build complex queries easily, supports SqlServer, MySql, PostgreSql, Oracle, Sqlite and Firebird
Stars: ✭ 2,111 (+1929.81%)
Mutual labels:  sql, query-builder, sql-query, database, postgresql
Jooq
jOOQ is the best way to write SQL in Java
Stars: ✭ 4,695 (+4414.42%)
Mutual labels:  sql, sql-query, database, postgresql
Ship Hold
data access framework for Postgresql on nodejs
Stars: ✭ 110 (+5.77%)
Mutual labels:  query-builder, sql-query, database, postgresql
Eralchemy
Entity Relation Diagrams generation tool
Stars: ✭ 767 (+637.5%)
Mutual labels:  schema, sql, database, postgresql
E Commerce Db
Database schema for e-commerce (webstores) sites.
Stars: ✭ 245 (+135.58%)
Mutual labels:  schema, database-schema, sql, postgresql
Jet
Type safe SQL builder with code generation and automatic query result data mapping
Stars: ✭ 373 (+258.65%)
Mutual labels:  sql, sql-query, database, postgresql
Goqu
SQL builder and query library for golang
Stars: ✭ 984 (+846.15%)
Mutual labels:  sql, sql-query, database, postgresql
Qtl
A friendly and lightweight C++ database library for MySQL, PostgreSQL, SQLite and ODBC.
Stars: ✭ 92 (-11.54%)
Mutual labels:  sql, database, postgresql
Efcore.pg
Entity Framework Core provider for PostgreSQL
Stars: ✭ 838 (+705.77%)
Mutual labels:  sql, database, postgresql
Reiner
萊納 - A MySQL wrapper which might be better than the ORMs and written in Golang
Stars: ✭ 19 (-81.73%)
Mutual labels:  sql, query-builder, database
Db Dumper
Dump the contents of a database
Stars: ✭ 744 (+615.38%)
Mutual labels:  sql, database, postgresql
Vscode Sqltools
Database management for VSCode
Stars: ✭ 741 (+612.5%)
Mutual labels:  sql, sql-query, postgresql
Beekeeper Studio
Modern and easy to use SQL client for MySQL, Postgres, SQLite, SQL Server, and more. Linux, MacOS, and Windows.
Stars: ✭ 8,053 (+7643.27%)
Mutual labels:  sql, database, postgresql
Sqlz
SQL Query Builder for Go
Stars: ✭ 36 (-65.38%)
Mutual labels:  sql, query-builder, database
Express Knex Objection
A simple API system on a pg database, using knex and objection to simplify connection and management
Stars: ✭ 20 (-80.77%)
Mutual labels:  database-schema, sql, database
Go Sqlbuilder
A flexible and powerful SQL string builder library plus a zero-config ORM.
Stars: ✭ 539 (+418.27%)
Mutual labels:  sql, database, postgresql
Node Pg Migrate
Node.js database migration management for Postgresql
Stars: ✭ 838 (+705.77%)
Mutual labels:  sql, database, postgresql
Electrocrud
Database CRUD Application Built on Electron | MySQL, Postgres, SQLite
Stars: ✭ 1,267 (+1118.27%)
Mutual labels:  sql, database, postgresql
Reporting Services Examples
📕 Various example reports I use for SQL Server Reporting Services (SSRS) as well as documents for unit testing, requirements and a style guide template.
Stars: ✭ 63 (-39.42%)
Mutual labels:  sql, sql-query, database

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

Note that the project description data, including the texts, logos, images, and/or trademarks, for each open source project belongs to its rightful owner. If you wish to add or remove any projects, please contact us at [email protected].