Skip to content
<open-source>
Oeiuwq Faith Blog OpenSource Porfolio

hiddentao/squel

:office: SQL query string builder for Javascript
Source code at GitHub https://hiddentao.github.io/squel
hiddentao/squel.json
{
  "createdAt": "2011-12-20T17:02:57Z",
  "defaultBranch": "master",
  "description": ":office: SQL query string builder for Javascript",
  "fullName": "hiddentao/squel",
  "homepage": "https://hiddentao.github.io/squel",
  "language": "TypeScript",
  "name": "squel",
  "pushedAt": "2026-06-17T11:34:48Z",
  "stargazersCount": 1569,
  "topics": [],
  "updatedAt": "2026-07-03T09:12:37Z",
  "url": "https://github.com/hiddentao/squel"
}

squel — SQL query string builder

Section titled “squel — SQL query string builder”

CI Coverage Status npm version NPM downloads License: MIT TypeScript

A flexible and powerful SQL query string builder for JavaScript and TypeScript.

Full guide and API documentation at https://hiddentao.github.io/squel.

Features

Section titled “Features”
  • Written in TypeScript with first-class type definitions.
  • Works in Node.js and the browser.
  • Supports the standard SQL queries: SELECT, UPDATE, INSERT, DELETE.
  • Supports dialect-specific modern SQL: Common Table Expressions (CTEs), Recursive CTEs, Postgres/MySQL UPSERT, MS SQL Server MERGE.
  • Supports Window Functions via the fluent OVER clause builder.
  • Supports dialect-aware JSON value extraction via jsonExtract.
  • Supports Postgres/SQLite RETURNING and SQL Server OUTPUT clauses.
  • Supports non-standard commands for MySQL, PostgreSQL, and Microsoft SQL Server.
  • Supports parameterized queries for safe value escaping.
  • Extensible — build any custom query or command you need.
  • Fluent method-chaining API.
  • Ships as dual ESM + CommonJS with a minified IIFE bundle for CDN use.

Warning: Do not ever pass queries generated on the client side to your web server for execution. Such a configuration would make it trivial for a casual attacker to execute arbitrary queries — as with an SQL-injection vector, but much easier to exploit and practically impossible to protect against.

Squel is suitable for production use. If you want richer ORM features you may also want to consider Knex.

Installation

Section titled “Installation”
Terminal window
# npm
npm install squel


# bun
bun add squel


# yarn
yarn add squel


# pnpm
pnpm add squel

Squel requires Node.js 18 or newer.

Usage

Section titled “Usage”

ESM / TypeScript

Section titled “ESM / TypeScript”
import squel from "squel"


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

CommonJS

Section titled “CommonJS”
const squel = require("squel").default
// or: const { squel } = require("squel")


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

Browser (CDN)

Section titled “Browser (CDN)”
<script src="https://unpkg.com/squel/dist/browser/squel.min.js"></script>
<script>
  // `squel` is available on the global scope
  const query = squel.select().from("books").field("title").toString()
</script>

Package layout

Section titled “Package layout”

The published package contains:

  • dist/esm/ — ES module build (import squel from "squel").
  • dist/cjs/ — CommonJS build (require("squel")).
  • dist/types/ — TypeScript declaration files.
  • dist/browser/squel.min.js — minified IIFE bundle for <script> tags (exposes window.squel).

Examples

Section titled “Examples”

SELECT

Section titled “SELECT”
// SELECT * FROM table
squel.select()
    .from("table")
    .toString()


// SELECT t1.id, t2.name FROM table `t1` LEFT JOIN table2 `t2` ON (t1.id = t2.id) WHERE (t2.name <> 'Mark') AND (t2.name <> 'John') GROUP BY t1.id
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()


// SELECT `t1`.`id`, `t1`.`name` as "My name", `t1`.`started` as "Date" FROM table `t1` WHERE age IN (RANGE(1, 1.2)) ORDER BY id ASC LIMIT 20
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)
    .toString()

You can build parameterized queries:

/*
{
    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()

You can use nested queries:

// SELECT s.id FROM (SELECT * FROM students) `s` INNER JOIN (SELECT id FROM marks) `m` ON (m.id = s.id)
squel.select()
    .from(squel.select().from("students"), "s")
    .field("id")
    .join(squel.select().from("marks").field("id"), "m", "m.id = s.id")
    .toString()

UPDATE

Section titled “UPDATE”
// UPDATE test SET f1 = 1
squel.update()
    .table("test")
    .set("f1", 1)
    .toString()


// UPDATE test, test2, test3 AS `a` SET test.id = 1, test2.val = 1.2, a.name = "Ram", a.email = NULL, a.count = a.count + 1
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()

INSERT

Section titled “INSERT”
// INSERT INTO test (f1, f2) VALUES (1, 1.2)
squel.insert()
    .into("test")
    .set("f1", 1)
    .set("f2", 1.2)
    .toString()

DELETE

Section titled “DELETE”
// DELETE FROM test WHERE (f1 = 2) ORDER BY f2 LIMIT 1
squel.delete()
    .from("test")
    .where("f1 = 2")
    .order("f2")
    .limit(1)
    .toString()

Expression builder

Section titled “Expression builder”
// test = 3 OR test = 4
squel.expr()
    .or("test = 3")
    .or("test = 4")
    .toString()


// test = 3 AND (inner = 1 OR inner = 2) OR (inner = 3 AND inner = 4 OR (inner IN ('str1, 'str2', NULL)))
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()


// SELECT * FROM test INNER JOIN test2 ON (test.id = test2.id) WHERE (test = 3 OR test = 4)
squel.select()
    .join("test2", null, squel.expr().and("test.id = test2.id"))
    .where(squel.expr().or("test = 3").or("test = 4"))
    .toString()

Custom value types

Section titled “Custom value types”

By default Squel does not support the use of object instances as field values. Instead it lets you tell it how you want specific object types to be handled:

// handler for objects of type Date
squel.registerValueHandler(Date, (date) => {
    return `${date.getFullYear()}/${date.getMonth() + 1}/${date.getDate()}`
})


squel.update()
    .table("students")
    .set("start_date", new Date(2013, 5, 1))
    .toString()


// UPDATE students SET start_date = '2013/6/1'

Custom value handlers can also be overridden on a per-instance basis (see the docs).

Custom queries

Section titled “Custom queries”

Squel lets you extend the built-in query builders or create entirely new kinds of queries:

class CommandBlock extends squel.cls.Block {
    command(command, arg) {
        this._command = command
        this._arg = arg
    }
    compress(level) {
        this.command("compress", level)
    }
    _toParamString(options) {
        let text = this._command.toUpperCase()
        const values = []
        if (options.buildParameterized) {
            text += " ?"
            values.push(this._arg)
        } else {
            text += ` ${this._arg}`
        }
        return { text, values }
    }
}


class PragmaQuery extends squel.cls.QueryBuilder {
    constructor(options) {
        super(options, [
            new squel.cls.StringBlock(options, "PRAGMA"),
            new CommandBlock(options),
        ])
    }
}


squel.pragma = (options) => new PragmaQuery(options)


squel.pragma().compress(9).toString()
// 'PRAGMA COMPRESS 9'


squel.pragma().compress(9).toParam()
// { text: 'PRAGMA COMPRESS ?', values: [9] }

Modern SQL Features

Section titled “Modern SQL Features”

Squel includes first-class support for advanced SQL query patterns across dialects.

Common Table Expressions (CTE) & Recursive CTEs

Section titled “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


// Recursive CTE (dialects automatically format, e.g. SQL Server omits RECURSIVE)
squel.select()
    .withRecursive("employee_tree", squel.select().from("employees"))
    .from("employee_tree")
    .toString()

Window Functions (OVER clause)

Section titled “Window Functions (OVER clause)”
squel.select()
    .from("employees")
    .field("name")
    .field(
        squel.over("AVG(salary)")
            .partitionBy("department")
            .orderBy("hire_date", false), // DESC
        "avg_dept_salary"
    )
    .toString()
// SELECT name, AVG(salary) OVER (PARTITION BY department ORDER BY hire_date DESC) AS "avg_dept_salary" FROM employees

JSON extraction helper (jsonExtract)

Section titled “JSON extraction helper (jsonExtract)”

Provides dialect-aware JSON field path extraction:

squel.select()
    .from("users")
    .where(squel.jsonExtract("profile", "$.name") + " = ?", "John")
    .toString()
// Default/MySQL: SELECT * FROM users WHERE (json_extract(profile, '$.name') = 'John')
// Postgres:     SELECT * FROM users WHERE (profile->>'name' = 'John')
// SQL Server:   SELECT * FROM users WHERE (JSON_VALUE(profile, '$.name') = 'John')

Upsert/MERGE

Section titled “Upsert/MERGE”

Fluent Postgres/MySQL UPSERT and SQL Server MERGE support:

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


// MySQL ON DUPLICATE KEY UPDATE
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 & OUTPUT

Section titled “RETURNING & OUTPUT”
// Postgres RETURNING
squel.useFlavour("postgres")
    .insert()
    .into("users")
    .set("name", "John")
    .returning("id")
    .toString()
// INSERT INTO users (name) VALUES ('John') RETURNING id


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

CROSS/OUTER APPLY (MSSQL)

Section titled “CROSS/OUTER APPLY (MSSQL)”

Provides CROSS APPLY and OUTER APPLY support for the MSSQL flavour:

// MSSQL CROSS/OUTER APPLY
squel.useFlavour("mssql")
    .select()
    .from("table1")
    .cross_apply("table2", "t2")
    .outer_apply(
        squel.select().from("bar").where("bar.id = table1.id"),
        "s"
    )
    .toString()
// SELECT * FROM table1 CROSS APPLY table2 t2 OUTER APPLY (SELECT * FROM bar WHERE (bar.id = table1.id)) s

Non-standard SQL flavours

Section titled “Non-standard SQL flavours”

Squel supports the standard SQL commands and reserved words. A number of database engines provide their own non-standard commands; Squel makes it easy to load different “flavours” of SQL that augment the core builders with engine-specific features.

Available flavours: mysql, mssql, postgres (e.g. INSERT ... RETURNING for Postgres, ON DUPLICATE KEY UPDATE for MySQL).

import squel from "squel"


const pg = squel.useFlavour("postgres")
const mysql = squel.useFlavour("mysql")
const mssql = squel.useFlavour("mssql")

For browser use:

<script src="https://unpkg.com/squel/dist/browser/squel.min.js"></script>
<script>
  const pg = squel.useFlavour("postgres")
</script>

See the API docs for a full reference.

Migrating from v5

Section titled “Migrating from v5”

v6.0.0 is a modernization release. The public API is unchanged — all your existing squel.select(), squel.useFlavour('postgres'), and related calls continue to work.

The breaking changes are all about package shape:

  • Engines: engines.node is now >=18. If you need support for older runtimes, stay on v5.
  • Output layout: the squel.js / squel.min.js / squel-basic.js / squel-basic.min.js UMD bundles have been replaced. Consumers now import from dist/esm/, dist/cjs/, or the IIFE at dist/browser/squel.min.js (see the [Package layout]!(#package-layout) section).
  • No separate “basic” bundle. The main entry point includes all flavours. ESM users benefit from tree-shaking; script-tag users get the single IIFE.
  • UMD dropped. If you loaded squel.min.js via <script>, switch to dist/browser/squel.min.js (same global window.squel).

Development

Section titled “Development”
Terminal window
bun install          # install dependencies
bun run check        # lint & format check (biome)
bun run typecheck    # TypeScript type checking
bun test             # run the test suite
bun run build        # produce dist/esm, dist/cjs, dist/types, dist/browser

Releasing

Section titled “Releasing”

Releases are automated. Landing a conventional commit on master triggers the release workflow, which bumps the version, updates CHANGELOG.md, creates a tag, and publishes to npm with provenance. See [RELEASE.md]!(RELEASE.md) for details.

Contributing

Section titled “Contributing”

Contributions are welcome! Please see [CONTRIBUTING.md]!(CONTRIBUTING.md).

Ports to other languages

Section titled “Ports to other languages”

License

Section titled “License”

MIT — see [LICENSE.md]!(LICENSE.md).