andywer/squid

🦑 Provides SQL tagged template strings and schema definition functions.

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
    🛡  SQL injection prevention
    🔦  Static query validation using postguard
    ⚡️  Almost no performance overhead

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: "[email protected]" })}
`)

// same as:
// sql`INSERT INTO users ("name", "email") VALUES ('John', '[email protected]')`

const users = await database.query(sql`
  INSERT INTO users ${spreadInsert(
    { name: "John", email: "[email protected]" },
    { name: "Travis", email: "[email protected]" }
  )}
`)

// same as:
// sql`INSERT INTO users ("name", "email") VALUES ('John', '[email protected]'), ('Travis', '[email protected]')`

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: "[email protected]" })}
  WHERE id = 1
`)

// same as:
// sql`UPDATE users SET "name" = 'John', "email" = '[email protected]' 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

squid's Issues

64 bit numbers come back as strings even if defined with Schema.Number

I've got some 64 bit IDs in my system that I've defined in my schema as a Schema.number. However, when I dump the objects out that are being returned from queries they are strings. I understand why this is and it makes sense, but it would be nice to get an error from postguard or somewhere. Right now the types aren't accurate and that's confusing.

Accept multiple records for spreadInsert()

Hi, I just found this library and it looks really good!

I just miss one feature before I can start using it for real. I want to be able to do something like:

const users = await database.query(sql`
  INSERT INTO users ${spreadInserts([
    { name: "Jon", email: "[email protected]" },
    { name: "Bran", email: "[email protected]" }
  ])}
`)

// Text: INSERT INTO users (name, email) VALUES (%1, %2), (%3, %4)
// Values: [ "Jon", "[email protected]", "Bran", "[email protected]" ]

I can submit a PR if you want.

Support for "IN" clauses

I might be missing it completely, but I don't see support in the library for running a query that uses an IN clause. Of course I could just generate that myself, but it would be nice to make sure it isn't vulnerable to any sql injection.

Composable SQL templates

If I have a WHERE clause to conditionally include in the query, it would be nice to do something like

const whereClause = condition ? sql`name = ${name}` : sql`TRUE`
return db.query(sql`SELECT * FROM "foo" WHERE ${whereClause}`)

or even

const whereClause = condition ? sql`WHERE name = ${name}` : ''
return db.query(sql`SELECT * FROM "foo" ${whereClause}`)

Thoughts?

Support for column aliases (middlewares?)

Is there an existing approach to supporting columns with names that don't match my objects? My database column names are in snake case, but my code is all camel case. I've written some functions to convert objects to and from this, but it would be really nice to be able to define this in the schema.

Thanks!

feature-request: spreadUpdate

It would be useful to have a spreadUpdate/set which could be used to generate dynamic update queries in a way that handles the undefined case.

I am currently creating Patch Types as follows and would like a easy and type safe way to use these in update queries

export type PatchUserRecord = Partial<TableRow<typeof usersTable>>

Set up GitHub actions

Feature Request: auto generate schema.ts

just like https://github.com/sweetiq/schemats or just use it.

Feature request: pg schemas

It seems as though there might be an issue with using squid + postguard when using postgres schemas, I would assume that when referencing tables in schemas one should use the FQN of the table in the defineTable method but this does not seem to work as intended with postguard.

I would expect the following to work and type check correctly, in addition it seems as postguard ignores the schema portion of table names when it checks a query

defineTable('schemaName.tableName`, {...})

sql.safe() for the careful ones

There is always the risk that you might forget the sql tag on your template literal and thus end up with an SQL injection vulnerability like this:

// forgot the `sql` tag
await database.query(`SELECT name FROM users WHERE id = ${id}`)

To prevent this kind of thing, let's add sql.safe():

// forgot the `sql` tag
await database.query(`SELECT name FROM users WHERE id = ${sql.safe(id)}`)

The result of sql.safe(X) should be an object that the sql tag function is able to interpret, but that is not interpretable if used in a default template string.

Might even want to add a [Symbol.toString] property to that object that either returns something like "[SQL safe]" or explicitly throws an error stating that you did not use the sql tag function.

Support for branded types in schema

We use brands extensively in our code to make it easy to distinguish between different types of integers. For example:

type Brand<K, T> = K & { __brand: T };
export type CommunityId = Brand<number, 'CommunityId'>;
export type UserId = Brand<number, 'UserId'>;

It would be great if I could define this in my schema so that the columns in query results have the right type instead of having to be converted.

Thanks!

Spread Insert does not work for array columns.

sql`insert into test_table ${spreadInsert({
  array_column: ['a','b','c','d'],
})}`;

Yields

insert into "test_table" ("array_column") VALUES ($1, $2, $3, $4) 

rather than joining and formatting the array value as such

ARRAY['a','b','c','d']

or

'{"a","b","c","d"}'

Benefits of `defineTable` over manual type definition

Thanks for this library, I really like the simplicity of spread* functions. spreadInsert makes bulk inserts more straightforward than slonik's or pg-promise's approach.

I'm not sure I understand the benefit of using defineSchema when not using postguard though. It seems I can just define the types like this:

interface NewUserRecord {
  name: string;
}

interface UserRecord extends NewUserRecord {
  id: number;
}

instead of:

defineTable('users', {
  id: Schema.default(Schema.Number),
  name: Schema.String,
});

Is there anything I'm missing? If defineTable is only used by postguard maybe it should be a part of that library instead?