@flowblade/sql-tag

Fast and lightweight (~630B) 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 18-22, browser, workers and edge.
🌍 Available in ESM and CJS formats.

Install


npm i @flowblade/sql-tag


pnpm add @flowblade/sql-tag


yarn add @flowblade/sql-tag


bun 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.unsafeRaw	Allow to pass unsafe values in the query.	`ORDER BY ${sql.unsafeRaw('name desc')}`
sql.empty	Helper to represent empty string.	`${isTrue ? sql'1=1' : sql.empty}`

Usage

Basic


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'];

Conditionals


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 ? sql`AND id IN (${sql.join(userIds)})` : sql.empty}
 
   -- 👇 alternative 2: with usage of sql.if helper
   ${sql.if(
     userIds.length,
     () => sql`AND 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];

Query composition

You can nest any query into another one.


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

Bulk inserts

Ease bulk inserts/merge from multi rows arrays.


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

With transact-sql (mssql)


import { sql } from '@flowblade/sql-tag';
 
const products = Array.from({ length: 1000 }, (_, i) => ({
  id: i,
  productName: `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 (
    productId INT,
    name NVARCHAR(255),
  );
 
  -- INSERT
  INSERT INTO #products (productId, productName)
     SELECT productId, productName
       FROM OPENJSON(@InitialData) WITH (
           id INT,
           name NVARCHAR(255)
       );
 
  -- SELECT
  SELECT TOP ${sql.unsafeRaw(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:

Named export for sql: import {sql} from '@flowblade/sql-tag'.
Possibility to type the result of the query (ie sql<Row>).
Utility functions (join…) are directly available from the sql tag.
Add sql.if helper to conditionally add a statement.
Rename sqlRaw to sql.unsafeRaw to prevent misuse.

Bundle size

Bundle size is tracked by a size-limit configuration

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

Compatibility

Level	CI	Description
Node	✅	CI for 18.x, 20.x & 22.x.
Browser	✅	Tested with latest chrome (vitest/playwright)
Browsers	✅	> 96% on 07/2024. Mins to Chrome 96+, Firefox 90+, Edge 19+, iOS 12+, Safari 12+, 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