---
title: "@flowblade/sql-tag"
description: "Fast and lightweight sql template tag based on sql-template-tag."
sidebar:
    label: "sql-tag"
---

Fast and lightweight ([~630B](#bundle-size)) sql template tag based on
[sql-template-tag](https://github.com/blakeembrey/sql-template-tag).

[![npm](https://img.shields.io/npm/v/@flowblade/sql-tag?style=for-the-badge&label=Npm&labelColor=444&color=informational)](https://www.npmjs.com/package/@flowblade/sql-tag)
[![changelog](https://img.shields.io/static/v1?label=&message=changelog&logo=github&style=for-the-badge&labelColor=444&color=informational)](https://github.com/belgattitude/flowblade/blob/main/packages/sql-tag/CHANGELOG.md)
[![bundles](https://img.shields.io/static/v1?label=&message=esm@treeshake&logo=rolldown&style=for-the-badge&labelColor=444&color=informational)](https://github.com/belgattitude/flowblade/blob/main/packages/sql-tag/.size-limit.ts)
[![node](https://img.shields.io/static/v1?label=Node&message=22.18%2b&logo=node.js&style=for-the-badge&labelColor=444&color=informational)](#compatibility)
[![browserslist](https://img.shields.io/static/v1?label=Browser&message=%3E96%25&logo=googlechrome&style=for-the-badge&labelColor=444&color=informational)](#compatibility)
[![size](https://img.shields.io/bundlephobia/minzip/@flowblade/sql-tag@latest?label=Max&style=for-the-badge&labelColor=444&color=informational)](https://bundlephobia.com/package/@flowblade/sql-tag@latest)
[![downloads](https://img.shields.io/npm/dm/@flowblade/sql-tag?style=for-the-badge&labelColor=444)](https://www.npmjs.com/package/@flowblade/sql-tag)
[![license](https://img.shields.io/npm/l/@flowblade/sql-tag?style=for-the-badge&labelColor=444)](https://github.com/belgattitude/flowblade/blob/main/LICENSE)

## Features

- 🛡️  Take advantage of
  [template literals](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Template_literals)
  to prevent sql injections.
- 🤲  Facilitate [query composition](#query-composition) and
  [conditional clauses](#conditionals).
- 🦄  Separate actual sql from provided parameters.
- ⚡️  Minimal performance overhead.
- 📐  Lightweight (less than [~700B](#bundle-size))
- ♾️️  Tested on
  [node 20-24, bun, browser, workers and edge](#compatibility).
- 🌍   Available in ESM and CJS formats.

## Install

```package-install
npm i @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

### Basic

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

### Conditionals

```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 ? 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.

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

### Bulk inserts

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

### With transact-sql (mssql)

```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](https://github.com/blakeembrey/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<Row>`).
- [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](https://github.com/belgattitude/flowblade/blob/main/packages/sql-tag/.size-limit.ts) (minified and brotli compressed, bundled with rolldown).

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

## Compatibility

| Level        | CI  | Description                                                                                                                                                                                                                                                                                                                                                      |
| ------------ | --- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Node         | ✅  | Requires 22.18+, CI for 22.x, 24.x & 26.x                                                                                                                                                                                                                                                                                                                        |
| Bun          | ✅  | Bun 1.4+, unit tests run on CI with the latest Bun                                                                                                                                                                                                                                                                                                               |
| Deno         | ✅  | Deno LTS, unit tests run on CI with Deno 2                                                                                                                                                                                                                                                                                                                       |
| Browserslist | ✅  | [> 95%](https://browserslist.dev/?q=ZGVmYXVsdHMsIGNocm9tZSA%2BPSA5NiwgZmlyZWZveCA%2BPSAxMDUsIGVkZ2UgPj0gMTEzLCBzYWZhcmkgPj0gMTUsIGlvcyA%2BPSAxNSwgb3BlcmEgPj0gMTAzLCBub3QgZGVhZA%3D%3D) on 01/2025. [Chrome 96+, Firefox 90+, Edge 19+, ios 15+, Safari 15+ and Opera 77+](https://github.com/belgattitude/flowblade/blob/main/packages/sql-tag/.browserslistrc) |
| Typescript   | ✅  | TS 5.0 + / [are-the-type-wrong](https://github.com/arethetypeswrong/arethetypeswrong.github.io) checks on CI.                                                                                                                                                                                                                                                    |
| ES2022       | ✅  | Dist files checked with [es-check](https://github.com/yowainwright/es-check)                                                                                                                                                                                                                                                                                     |