SQL tagged template strings and schema definitions for JavaScript & TypeScript 3.
Use pg-lint to validate SQL queries in your code against your table schemas at build time 🚀
☑️ Static query validation using pg-lint 👌 Static typing made simple 🛡 SQL injection prevention ⚡️ Almost no performance overhead
Parameters are always 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.
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.
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.
npm install squidimport { defineTable, sql, spreadInsert } from "squid/pg"
import database from "./database"
defineTable("users", {
id: Schema.Number,
name: Schema.String
})
export async function createUser(record) {
const { rows } = await database.query(sql`
INSERT INTO users ${spreadInsert(record)} RETURNING *
`)
return rows[0]
}
export async function queryUserById(id) {
const { rows } = await database.query(sql`
SELECT * FROM users WHERE id = ${id}
`)
return rows.length > 0 ? rows[0] : null
}In TypeScript:
import { defineTable, sql, spreadInsert, NewTableRow, TableRow } from "squid/pg"
import database from "./database"
type NewUserRecord = NewTableRow<typeof usersTable>
type UserRecord = TableRow<typeof usersTable>
const usersTable = defineTable("users", {
id: Schema.Number,
name: Schema.String
})
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> {
const { rows } = await database.query<UserRecord>(sql`
SELECT * FROM users WHERE id = ${id}
`)
return rows.length > 0 ? rows[0] : null
}We extend the pg driver's query() methods 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.
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}
`)
}The sql template tag creates query objects compatible with pg, the super popular Postgres driver for node.
> import { sql, spreadAnd, spreadInsert } from "squid/pg"
> sql`SELECT * FROM users WHERE ${spreadAnd({ name: "Andy", age: 29 })}`
{ text: "SELECT * FROM users WHERE ("name" = $1 AND "age" = $2)",
values: [ "Andy", 29 ] }
> 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 ${spreadAnd({ name: "Andy", age: sql.raw("29") })}`
{ text: "SELECT * FROM users WHERE ("name" = $1 AND "age" = 29)",
values: [ "Andy" ] }
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])Wrap your SQL template string values in this call to prevent escaping.
Example:
await database.query(sql`
UPDATE users SET last_login = ${loggingIn ? "NOW()" : "NULL"} WHERE id = ${userID}
`)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'`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]')`Define a table's schema, so the queries can be validated at build time with pg-lint. 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.
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.
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.
The performance impact of using the template string is neglectible. Benchmarked it once and it did 1000 queries in ~10ms on my MacBook Pro.
Set the environment variable DEBUG to squid:* to enable debug logging for this package.
MIT