@flowblade/sql-tag v0.1.14

@flowblade/sql-tag

Fast and lightweight (~790B) 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

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

Compatibility

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 ! 🙏❤️

Special thanks to

License

MIT © Sébastien Vanvelthem and contributors.