squid

SQL tagged template strings and schema definitions for JavaScript & TypeScript.

The simple and safe way of writing SQL queries in node.js. Use postguard to validate SQL queries in your code against your table schemas at build time 🚀

👌  Static typing made simple<br />
🛡  SQL injection prevention<br />
🔦  Static query validation using postguard<br />
⚡️  Almost no performance overhead<br />

Parameters are SQL-injection-proofed by default. You can explicitly opt-out, by wrapping the parameter value in sql.raw().

Supports only Postgres right now, but it is easy to add support for MySQL, SQLite, … as well. Create an issue or pull request if you need support for another database.

Why?

Why not use a query builder?

Query builders like Prisma or Knex.js seem like a good choice, but they all have one issue in common: They provide an abstraction that maps 1:1 to SQL, making you create SQL queries without writing SQL, but using their proprietary API.

You don’t just require developers to learn both, SQL and the query builder’s API, but the additional abstraction layer is also an additional source of error.

Why not use an ORM?

ORMs like Sequelize or TypeORM can get you started quickly, but will regularly lead to slow queries and can turn into a hassle in the long run. Read more about it here and here, for instance.

Installation

npm install squid

Usage

JavaScript

import { defineTable, sql, spreadInsert } from "squid/pg"
import database from "./database"
// Feel free to put the table schema in a different file
defineTable("users", {
  id: Schema.Number,
  name: Schema.String
})
export async function queryUserById(id) {
  const { rows } = await database.query(sql`
    SELECT * FROM users WHERE id = ${id}
  `)
  return rows.length > 0 ? rows[0] : null
}

TypeScript

// schema.ts
import { defineTable, Schema, NewTableRow, TableRow } from "squid"
export type NewUserRecord = NewTableRow<typeof usersTable>
export type UserRecord = TableRow<typeof usersTable>
const usersTable = defineTable("users", {
  id: Schema.Number,
  name: Schema.String
})

// users.ts
import { sql, spreadInsert } from "squid/pg"
import database from "./database"
import { NewUserRecord, UserRecord } from "./schema"
export async function createUser(record: NewUserRecord): Promise<UserRecord> {
  const { rows } = await database.query<UserRecord>(sql`
    INSERT INTO users ${spreadInsert(record)} RETURNING *
  `)
  return rows[0]
}
export async function queryUserById(id: string): Promise<UserRecord | null> {
  const { rows } = await database.query<UserRecord>(sql`
    SELECT * FROM users WHERE id = ${id}
  `)
  return rows[0] || null
}

We extend the pg driver’s query() method types transparently, so you can pass a generic type parameter specifying the type of the result rows as you can see in the sample above.

The query() type parameter defaults to any, so you don’t have to specify it. If it’s set, the type of the rows result property will be inferred accordingly.

Query values

All expressions in the SQL template strings will be escaped properly automatically, so you don’t need to worry about SQL injection attacks too much.

If you need to pass a value dynamically that should not be escaped, you can use sql.raw:

async function updateTimestamp(userID, timestamp = null) {
  await database.query(sql`
    UPDATE users
    SET timestamp = ${timestamp || sql.raw("NOW()")}
    WHERE id = ${userID}
  `)
}

Tag function

The sql template tag creates query objects compatible with pg, the super popular Postgres driver for node.

import { sql, spreadInsert } from "squid/pg"
sql`INSERT INTO users ${spreadInsert({ name: "Andy", age: 29 })}`
// => { text: "INSERT INTO users ("name", "age") VALUES ($1, $2)",
//      values: [ "Andy", 29 ] }
sql`SELECT * FROM users WHERE age < ${maxAge}`
// => { text: "SELECT * FROM users WHERE age < $1",
//      values: [ maxAge ] }

Import

All schema-related exports are database-driver-agnostic, so they can be imported from the main entrypoint squid:

import { defineTable, Schema, NewTableRow, TableRow } from "squid"

Non-schema-related exports are exported by the database-specific submodule squid/pg:

import { sql, spreadInsert } from "squid/pg"

For convenience squid/pg also exposes all the database-agnostic schema exports, so you can have one import declaration for everything:

import { defineTable, sql, spreadInsert, Schema, NewTableRow, TableRow } from "squid"

SQL injections

All values passed into the tagged template string are automatically escaped to prevent SQL injections. You have to explicitly opt-out of this behavior by using sql.raw() in case you want to dynamically modify the query.

The only attack vector left is forgetting to use the sql template tag. To rule out this potential source of error, there is also a way to explicitly escape a value passed to the template string: sql.safe().

An untagged template string using a value wrapped in sql.safe() will then result in an invalid query.

// This is fine
await database.query(sql`SELECT * FROM users LIMIT ${limit}`)
// Results in the same query as the previous example
await database.query(sql`SELECT * FROM users LIMIT ${sql.safe(limit)}`)
// Forgot the tag - SQL injection possible!
await database.query(`SELECT * FROM users LIMIT ${limit}`)
// Forgot the tag - This line will now throw, no SQLi possible
await database.query(`SELECT * FROM users LIMIT ${sql.safe(limit)}`)

API

sql`…`

Turns a template string into a postgres query object, escapes values automatically unless they are wrapped in sql.raw().

Example:

const limit = 50
await database.query(sql`SELECT * FROM users LIMIT ${50}`)
// same as:
await database.query({ text: "SELECT * FROM users LIMIT $1", values: [limit])

sql.raw(expression)

Wrap your SQL template string values in this call to prevent escaping.
Be careful, though. This is essentially an SQL injection prevention opt-out.

Example:

await database.query(sql`
  UPDATE users SET last_login = ${loggingIn ? "NOW()" : "NULL"} WHERE id = ${userID}
`)

sql.safe(value)

Wraps a value in an object that just returns the (escaped) value.

Use it if you want to make sure that a query lacking the sql template tag cannot be executed. Otherwise a missing template string tag might lead to SQL injections.

spreadAnd({ [columnName: string]: any })

Check for equivalence of multiple column’s values at once. Handy to keep long WHERE expressions short and concise.

Example:

const users = await database.query(sql`
  SELECT * FROM users WHERE ${spreadAnd({ name: "John", birthday: "1990-09-10" })}
`)
// same as:
// sql`SELECT * FROM users WHERE name = 'John' AND birthday = '1990-09-10'`

spreadInsert({ [columnName: string]: any })

Spread INSERT VALUES to keep the query sweet and short without losing explicity.

Example:

const users = await database.query(sql`
  INSERT INTO users ${spreadInsert({ name: "John", email: "john@example.com" })}
`)
// same as:
// sql`INSERT INTO users ("name", "email") VALUES ('John', 'john@example.com')`

const users = await database.query(sql`
  INSERT INTO users ${spreadInsert(
    { name: "John", email: "john@example.com" },
    { name: "Travis", email: "travis@example.com" }
  )}
`)
// same as:
// sql`INSERT INTO users ("name", "email") VALUES ('John', 'john@example.com'), ('Travis', 'travis@example.com')`

spreadUpdate({ [columnName: string]: any })

Spread INSERT VALUES to keep the query sweet and short without losing explicity.

Example:

await database.query(sql`
  UPDATE users
  SET ${spreadUpdate({ name: "John", email: "john@example.com" })}
  WHERE id = 1
`)
// same as:
// sql`UPDATE users SET "name" = 'John', "email" = 'john@example.com' WHERE id = 1`

defineTable(tableName: string, schema: { [columnName: string]: Schema.* })

Define a table’s schema, so the queries can be validated at build time with postguard. When using TypeScript you can use TableRow<typeof table> and NewTableRow<typeof table> to derive TypeScript interfaces of your table records.

See dist/schema.d.ts for details.

Schema

Example:

defineTable("users", {
  id: Schema.Number,
  email: Schema.String,
  email_confirmed: Schema.Boolean,
  profile: Schema.JSON(
    Schema.Object({
      avatar_url: Schema.String,
      weblink: Schema.nullable(Schema.String)
    })
  ),
  created_at: Schema.default(Schema.Date),
  updated_at: Schema.nullable(Schema.Date),
  roles: Schema.Array(Schema.Enum(["admin", "user"]))
})

See dist/schema.d.ts for details.

TableRow / NewTableRow (TypeScript only)

Derive table record interfaces from the table schema. The type returned by TableRow is the kind of object a SELECT * will return, while NewTableRow returns an object that defines the shape of an object to be used for an INSERT with spreadInsert().

The difference between the two is that NewTableRow marks properties referring to columns defined as Schema.default() or Schema.nullable() as optional.

Example:

const usersTable = defineTable("users", {
  id: Schema.Number,
  email: Schema.String
})
type UserRecord = TableRow<typeof usersTable>
type NewUserRecord = NewTableRow<typeof usersTable>

See dist/schema.d.ts for details.

Performance

The performance impact of using the template string is neglectible. Benchmarked it once and it did 1000 queries in ~10ms on my MacBook Pro.

Debugging

Set the environment variable DEBUG to squid:* to enable debug logging for this package.

License

MIT