squel

repository·master·Indexed 23 days ago

A flexible SQL query string builder for JavaScript and TypeScript (v6.3.1). It supports standard SQL (SELECT, UPDATE, INSERT, DELETE) and modern dialect-specific features such as CTEs, Window Functions, and UPSERT. Squel provides built-in support for MySQL, PostgreSQL, and MSSQL flavors, and includes utilities for parameterized queries to prevent SQL injection, custom value handlers, and JSON extraction helpers.

Tokens: 4.8K
Snippets: 13
Records: 30
Agent score: 80%

What's inside squel

Use modern SQL features (CTEs, Window Functions, JSON)

master

Squel provides first-class support for advanced SQL patterns.

Common Table Expressions (CTE) & Recursive CTEs

const subQuery = squel.select().from("users").where("active = ?", true);

squel.select()
    .with("active_users", subQuery)
    .from("active_users")
    .toString()
// WITH active_users AS (SELECT * FROM users WHERE (active = TRUE)) SELECT * FROM active_users

squel.select()
    .withRecursive("employee_tree", squel.select().from("employees"))
    .from("employee_tree")
    .toString()

Window Functions (OVER clause)

squel.select()
    .from("employees")
    .field("name")
    .field(
        squel.over("AVG(salary)")
            .partitionBy("department")
            .orderBy("hire_date", false), // false = DESC
        "avg_dept_salary"
    )
    .toString()

JSON extraction helper (`jsonExtract`)

Provides dialect-aware JSON field path extraction (Postgres, MySQL, SQL Server).

squel.select()
    .from("users")
    .where(squel.jsonExtract("profile", "$.name") + " = ?", "John")
    .toString()

squel.select()
    .with("active_users", subQuery)
    .from("active_users")
    .toString()

Use non-standard SQL flavours
master
Squel supports standard SQL but allows you to load specific "flavours" to access engine-specific features (e.g., INSERT ... RETURNING for Postgres or ON DUPLICATE KEY UPDATE for MySQL). You can create flavour-specific instances using squel.useFlavour(name).
Available flavours include:
- mysql
- mssql
- postgres
```
import squel from "squel"

const pg = squel.useFlavour("postgres")
const mysql = squel.useFlavour("mysql")
const mssql = squel.useFlavour("mssql")
```
https://github.com/hiddentao/squel/blob/master/README.md

Use dialect-specific Upsert, MERGE, and RETURNING

master

Squel supports dialect-specific modern SQL via .useFlavour(flavour).

Postgres/MySQL UPSERT

// Postgres
squel.useFlavour("postgres")
    .insert()
    .into("users")
    .set("email", "john@example.com")
    .onConflict("email")
    .doUpdate()
    .set("updated_at", new Date())
    .toString()

// MySQL
squel.useFlavour("mysql")
    .insert()
    .into("users")
    .set("email", "john@example.com")
    .onDuplicateKeyUpdate()
    .set("updated_at", new Date())
    .toString()

SQL Server MERGE

squel.useFlavour("mssql")
    .merge()
    .into("target_table", "t")
    .using("source_table", "s", "t.id = s.id")
    .whenMatched()
        .update({ "t.val": squel.str("s.val") })
    .whenNotMatched()
        .insert({ id: squel.str("s.id"), val: squel.str("s.val") })
    .toString()

RETURNING (Postgres) & OUTPUT (SQL Server)

// Postgres RETURNING
squel.useFlavour("postgres")
    .insert()
    .into("users")
    .set("name", "John")
    .returning("id")
    .toString()

// SQL Server OUTPUT
squel.useFlavour("mssql")
    .insert()
    .into("users")
    .set("name", "John")
    .output("id")
    .toString()

squel.useFlavour("postgres")
    .insert()
    .into("users")
    .set("email", "john@example.com")
    .onConflict("email")
    .doUpdate()
    .set("updated_at", new Date())
    .toString()

Install squel
master
Install squel using your preferred package manager. Squel requires Node.js 18 or newer.
```
# npm
npm install squel

# bun
bun add squel

# yarn
yarn add squel

# pnpm
pnpm add squel
```
https://github.com/hiddentao/squel/blob/master/README.md
Migrate from v5 to v6
master
v6.0.0 is a modernization release. While the public API (e.g., squel.select(), squel.useFlavour()) remains unchanged, there are several breaking changes regarding package shape and runtime support:
- Runtime Support: engines.node now requires Node.js >=18. For older runtimes, continue using v5.
- Output Layout: UMD bundles have been removed. Consumers must now import from dist/esm/, dist/cjs/, or use the IIFE at dist/browser/squel.min.js.
- Bundle Changes: There is no longer a separate "basic" bundle; the main entry point includes all flavours. ESM users can leverage tree-shaking.
- Browser Loading: If you previously loaded squel.min.js via <script>, you must now switch to dist/browser/squel.min.js (this still provides the same window.squel global).
https://github.com/hiddentao/squel/blob/master/README.md
Use Squel in the browser via CDN
master
To use Squel directly in a web browser, include the minified IIFE bundle via a script tag. Once loaded, the squel global object is available, and you can initialize flavours using squel.useFlavour().
```
<script src="https://unpkg.com/squel/dist/browser/squel.min.js"></script>
<script>
  const pg = squel.useFlavour("postgres")
</script>
```
https://github.com/hiddentao/squel/blob/master/README.md

Import and use squel in ESM, CommonJS, or Browser

master

Depending on your environment, import squel differently:

ESM / TypeScript

import squel from "squel"

CommonJS

const squel = require("squel").default
// or: const { squel } = require("squel")

Browser (CDN)

Include the minified IIFE bundle via a <script> tag. squel will be available on the global window scope.

<script src="https://unpkg.com/squel/dist/browser/squel.min.js"></script>
<script>
  const query = squel.select().from("books").field("title").toString()
</script>

import squel from "squel"

const query = squel.select().from("books").field("title").toString()

Enable the PostgreSQL dialect
master
To use PostgreSQL-specific features (like ON CONFLICT or DISTINCT ON), you must register the PostgreSQL flavour with squel. This configures default query builder options such as numbered parameters starting at 1 and specific aliasing behaviors.
https://github.com/hiddentao/squel/blob/master/src/postgres.ts

Build UPDATE queries

master

Use squel.update() to modify existing records. You can set values using strings or objects.

Basic UPDATE

squel.update()
    .table("test")
    .set("f1", 1)
    .toString()

Multi-table UPDATE with object mapping

squel.update()
    .table("test")
    .set("test.id", 1)
    .table("test2")
    .set("test2.val", 1.2)
    .table("test3", "a")
    .setFields({
        "a.name": "Ram",
        "a.email": null,
        "a.count = a.count + 1": undefined,
    })
    .toString()

Build complex expressions with squel.expr()

master

Use squel.expr() to build complex logical expressions (AND/OR) that can be used inside .where() or .join() clauses.

Nested logical expressions

squel.expr()
    .and("test = 3")
    .and(
        squel.expr()
            .or("inner = 1")
            .or("inner = 2")
    )
    .or(
        squel.expr()
            .and("inner = ?", 3)
            .and("inner = ?", 4)
            .or(
                squel.expr()
                    .and("inner IN ?", ["str1", "str2", null])
            )
    )
    .toString()

squel.expr()
    .or("test = 3")
    .or("test = 4")
    .toString()

Build INSERT queries
master
Use squel.insert() to add new records.
```
squel.insert()
    .into("test")
    .set("f1", 1)
    .set("f2", 1.2)
    .toString()
```
https://github.com/hiddentao/squel/blob/master/README.md

Build SELECT queries

master

Use squel.select() to build standard SELECT statements. You can chain methods for fields, joins, grouping, filtering, and ordering.

Basic SELECT

squel.select()
    .from("table")
    .toString()

SELECT with Joins and Where clauses

squel.select()
    .from("table", "t1")
    .field("t1.id")
    .field("t2.name")
    .left_join("table2", "t2", "t1.id = t2.id")
    .group("t1.id")
    .where("t2.name <> 'Mark'")
    .where("t2.name <> 'John'")
    .toString()

Parameterized SELECT

To prevent SQL injection, use .toParam() instead of .toString(). This returns an object containing the SQL string with placeholders and an array of values.

/*
{
    text: "SELECT `t1`.`id`, `t1`.`name` as \"My name\", `t1`.`started` as \"Date\" FROM table `t1` WHERE age IN (RANGE(?, ?)) ORDER BY id ASC LIMIT 20",
    values: [1, 1.2]
}
*/
squel.select({ autoQuoteFieldNames: true })
    .from("table", "t1")
    .field("t1.id")
    .field("t1.name", "My name")
    .field("t1.started", "Date")
    .where("age IN ?", squel.str('RANGE(?, ?)', 1, 1.2))
    .order("id")
    .limit(20)
    .toParam()

Nested Queries

Pass a squel query instance into .from() or .join() to create subqueries.

squel.select()
    .from(squel.select().from("students"), "s")
    .field("id")
    .join(squel.select().from("marks").field("id"), "m", "m.id = s.id")
    .toString()

squel

What's inside squel

Common Table Expressions (CTE) & Recursive CTEs

Window Functions (OVER clause)

JSON extraction helper (jsonExtract)

Postgres/MySQL UPSERT

SQL Server MERGE

RETURNING (Postgres) & OUTPUT (SQL Server)

ESM / TypeScript

CommonJS

Browser (CDN)

Basic UPDATE

Multi-table UPDATE with object mapping

Nested logical expressions

Basic SELECT

SELECT with Joins and Where clauses

Parameterized SELECT

Nested Queries

JSON extraction helper (`jsonExtract`)