@flowblade/sql-tag

Fast and lightweight sql template tag based on sql-template-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 20-24, bun, browser, workers and edge.
• 🌍   Available in ESM and CJS formats.

Install

npm install @flowblade/sql-tag

pnpm add @flowblade/sql-tag

yarn add @flowblade/sql-tag

bun add @flowblade/sql-tag

nub add @flowblade/sql-tag

aube add @flowblade/sql-tag

API

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,
    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:

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

Bundle size

Bundle size is tracked by a size-limit configuration (minified and brotli compressed, bundled with rolldown).

Compatibility