@flowblade/sql-tag

Fast and lightweight (~700B) sql template tag based on sql-template-tag.


Features

- 🛡️  Take advantage of template literals to prevent sql injections.
- 🤲  Facilitate query composition and conditional clauses.
- 🦄  Separate actual sql from provided parameters.
- ⚡️  Minimal performance overhead.
- 📐  Lightweight (less than ~700B)
- ♾️️  Tested on node 20-24, bun, browser, workers and edge.
- 🌍   Available in ESM and CJS formats.

Install

``bash
yarn add @flowblade/sql-tag
`

API

| Helpers | Description | Example |
| --------- | ----------------------------------------- | ---------------------------------------------------- |
| sql.join | Join array values with optional separator | AND id IN (${sql.join(['1', '3'])) |
| sql.if | Conditionally add a statement | AND ${sql.if(true, () => sql'deleted_at is null')} |
| sql.bulk | Ease bulk inserts | |
| sql.raw | Allow to pass unsafe values in the query. | ORDER BY ${sql.raw('name desc')} |
| sql.empty | Helper to represent empty string. | ${isTrue ? sql'1=1' : sql.empty} |

Usage

$3

`typescript
import { sql } from "@flowblade/sql-tag";

// 👈 User provided parameters
const params = {
country: "BE",
users: ["John", "Doe"],
};

const query = sql<{
// 👈 optionally type the result
id: number;
username: string;
}>
SELECT id, username FROM users
WHERE country = ${params.country} -- 👈 simple param
AND username IN (${sql.join(params.users)}) -- 👈 array param
;

// query.sql === "SELECT id, username FROM users WHERE country = ? AND username IN (?, ?)";
// query.values === ['BE', 'John', 'Doe'];
`

$3

`typescript
import { sql } from "@flowblade/sql-tag";

// 👈 User provided parameters
const userIds = [1, 2];
const limit = 10;

const query = sql<{
// 👈 optionally type the result
id: number;
username: string;
}>
SELECT id, username FROM users
WHERE 1=1
-- 👇 alternative 2: with ternary operator and sql.empty
${userIds.length > 0 ? sqlAND id IN (${sql.join(userIds)}) : sql.empty}

-- 👇 alternative 2: with usage of sql.if helper
${sql.if(userIds.length, () => sqlAND id IN (${sql.join(userIds)}))}
LIMIT ${limit}
;

// query.sql === "SELECT id, username FROM users WHERE 1=1 AND id IN (?, ?) LIMIT ?";
// query.values === [1, 2, 10];
`

$3

You can nest any query into another one.

`typescript
import { sql } from "@flowblade/sql-tag";

const getSqlUserCountByCountries = (minUsers: number) => sql
SELECT
c.name as country_name,
count(u.id) as user_count
FROM country AS c INNER JOIN user u
ON c.id = u.country_id
GROUP BY c.name
HAVING count(u.id) > ${minUsers}
;

const compression: "zstd" | "snappy" | "gzip" = "zstd";

// Example base on DuckDb COPY statement
// but you can nest into CTE, table aliases, subqueries etc...
const query = sql
COPY
(${getSqlUserCountByCountries(23)})
TO 'usercount_by_countries.parquet'
(FORMAT 'parquet', COMPRESSION ${compression}, ROW_GROUP_SIZE 100000);
;

console.log(query.values); // [23, 'zstd']
console.log(query.sql); // "COPY (SELECT...."
`

$3

Ease bulk inserts/merge from multi rows arrays.

`typescript
import { sql } from "@flowblade/sql-tag";

const insert = sql
INSERT INTO product (name, price, stock, status)
VALUES ${sql.bulk([
["Laptop", 999.99, 50, "active"],
["Keyboard", 79.99, 100, "active"],
])}
;

const { text, sql, statement, values } = insert;

insert.text; //=> "INSERT INTO product (name, price, stock, status) VALUES ($1,$2,$3,$4),($5,$6,$7,$8)"
insert.sql; //=> "INSERT INTO product (name, price, stock, status) VALUES (?,?,?,?),(?,?,?,?),(?,?,?,?)"
insert.values; //=> ["Laptop", 999.99, 50, "active", "Keyboard", 79.99, 100, "active"]

// Example running the query with pglite

const result = await db.query(text, values, {});
`

Recipes

$3

`typescript
import { sql } from "@flowblade/sql-tag";

const products = Array.from({ length: 1000 }, (_, i) => ({
id: i,
name: Product ${i},
}));

const limit = 10;

const sqlRaw = sql<{ id: number; name: string }>
-- TRANSACT-SQL
DECLARE @Products NVARCHAR(MAX); -- WARNING LIMIT TO 2GB
SET @Products = ${JSON.stringify(products)};

-- DDL (# prefix is equivalent to CREATE TEMPORATY TABLE on other db)
CREATE TABLE #products (
id INT,
name NVARCHAR(255),
);

-- INSERT
INSERT INTO #products (id, name)
SELECT id, name
FROM OPENJSON(@Products) WITH (
id INT,
name NVARCHAR(255)
);

-- SELECT
SELECT TOP ${sql.raw(String(limit))} id, name
FROM #products
ORDER BY id;
;
`

Credits

This package won't be possible without the great work of Blake Embrey sql-template-tag.

Some notable differences:

- [x] Named export for sql: import { sql } from '@flowblade/sql-tag'.
- [x] Possibility to type the result of the query (ie sql).
- [x] Utility functions (join...) are directly available from the sql tag.
- [x] Add sql.if helper to conditionally add a statement.

Bundle size

Bundle size is tracked by a size-limit configuration

| Scenario (esm) | Size (compressed) |
| ----------------------------------------- | ----------------: |
| import { sql } from '@flowblade/sql-tag` | ~ 700B |

Compatibility

| Level | CI | Description |
| ------------ | --- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Node | ✅ | CI for 20.x, 22.x, 24.x & 25.x. |
| Bun | ✅ | Tested on latest (1.3.5) |
| Browser | ✅ | Tested with latest chrome (vitest/playwright) |
| Browserslist | ✅ | > 95% on 01/2025. Chrome 96+, Firefox 90+, Edge 19+, ios 15+, Safari 15+ and Opera 77+ |
| Edge | ✅ | Ensured on CI with @vercel/edge-runtime. |
| Cloudflare | ✅ | Ensured with @cloudflare/vitest-pool-workers (see wrangler.toml |
| Typescript | ✅ | TS 5.0 + / are-the-type-wrong checks on CI. |
| ES2022 | ✅ | Dist files checked with es-check |
| Performance | ✅ | Monitored with codspeed.io |

Contributors

Contributions are welcome. Have a look to the CONTRIBUTING document.

Sponsors

Sponsor>), coffee>),
or star – All is spent for quality time with loved ones. Thanks ! 🙏❤️

$3

JetBrains	Embie.be

License

MIT © Sébastien Vanvelthem and contributors.