All Projects → andywer → Squid

andywer / Squid

Licence: mit
🦑 Provides SQL tagged template strings and schema definition functions.

Programming Languages

typescript
32286 projects

Projects that are alternatives of or similar to Squid

Postguard
🐛 Statically validate Postgres SQL queries in JS / TS code and derive schemas.
Stars: ✭ 104 (+82.46%)
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 (+3603.51%)
Mutual labels:  sql, query-builder, sql-query, database, postgresql
Ship Hold
data access framework for Postgresql on nodejs
Stars: ✭ 110 (+92.98%)
Mutual labels:  query-builder, sql-query, database, postgresql, postgres
Jet
Type safe SQL builder with code generation and automatic query result data mapping
Stars: ✭ 373 (+554.39%)
Mutual labels:  sql, sql-query, database, postgresql, postgres
Goqu
SQL builder and query library for golang
Stars: ✭ 984 (+1626.32%)
Mutual labels:  sql, sql-query, database, postgresql, postgres
Sqliterally
Lightweight SQL query builder
Stars: ✭ 231 (+305.26%)
Mutual labels:  sql, query-builder, postgresql, postgres
Massive Js
A data mapper for Node.js and PostgreSQL.
Stars: ✭ 2,521 (+4322.81%)
Mutual labels:  sql, database, postgresql, postgres
Eralchemy
Entity Relation Diagrams generation tool
Stars: ✭ 767 (+1245.61%)
Mutual labels:  schema, sql, database, postgresql
Postgui
A React web application to query and share any PostgreSQL database.
Stars: ✭ 260 (+356.14%)
Mutual labels:  query-builder, database, postgresql, postgres
Goose
A database migration tool. Supports SQL migrations and Go functions.
Stars: ✭ 2,112 (+3605.26%)
Mutual labels:  schema, sql, database, postgres
Efcore.pg
Entity Framework Core provider for PostgreSQL
Stars: ✭ 838 (+1370.18%)
Mutual labels:  sql, database, postgresql, postgres
Vscode Sqltools
Database management for VSCode
Stars: ✭ 741 (+1200%)
Mutual labels:  sql, sql-query, postgresql, postgres
Npgsql
Npgsql is the .NET data provider for PostgreSQL.
Stars: ✭ 2,415 (+4136.84%)
Mutual labels:  sql, database, postgresql, postgres
Sqorn
A Javascript library for building SQL queries
Stars: ✭ 1,871 (+3182.46%)
Mutual labels:  sql, query-builder, postgresql, postgres
Ansible Role Postgresql
Ansible Role - PostgreSQL
Stars: ✭ 310 (+443.86%)
Mutual labels:  sql, database, postgresql, postgres
Jooq
jOOQ is the best way to write SQL in Java
Stars: ✭ 4,695 (+8136.84%)
Mutual labels:  sql, sql-query, database, postgresql
Scenic
Scenic is maintained by Derek Prior, Caleb Hearth, and you, our contributors.
Stars: ✭ 2,856 (+4910.53%)
Mutual labels:  schema, sql, database, postgres
Evolutility Server Node
Model-driven REST or GraphQL backend for CRUD and more, written in Javascript, using Node.js, Express, and PostgreSQL.
Stars: ✭ 84 (+47.37%)
Mutual labels:  sql, query-builder, database, postgres
Electrocrud
Database CRUD Application Built on Electron | MySQL, Postgres, SQLite
Stars: ✭ 1,267 (+2122.81%)
Mutual labels:  sql, database, postgresql, postgres
Loukoum
A simple SQL Query Builder
Stars: ✭ 305 (+435.09%)
Mutual labels:  sql, query-builder, postgresql, postgres

squid

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

Build status npm version


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

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].