sql-flex-query

A lightweight, dialect-aware SQL query builder that enhances base query templates with dynamic WHERE, HAVING, ORDER BY, pagination, and more. Also provides helpers for building INSERT, UPDATE, and DELETE queries with dialect-specific placeholders.

Write once, run on any SQL database — PostgreSQL, MySQL, SQLite, SQL Server, Oracle, CockroachDB, Snowflake. Need another database? Extend BaseDialect to add custom dialect support (see below).

Supported Databases

Database
PostgreSQL
MySQL
SQLite
SQL Server
Oracle
CockroachDB
Snowflake

Why sql-flex-query?

The Problem

Different SQL databases use different placeholder styles, identifier quoting, and pagination syntax:

Database	Placeholders	Identifier Quoting	Pagination
PostgreSQL	`$1, $2`	`"double quotes"`	`LIMIT/OFFSET`
MySQL	`?`	`backticks`	`LIMIT/OFFSET`
SQLite	`?`	`"double quotes"`	`LIMIT/OFFSET`
SQL Server	`@p1, @p2`	`[brackets]`	`OFFSET/FETCH`
Oracle	`:1, :2`	`"double quotes"`	`OFFSET/FETCH`
CockroachDB	`$1, $2`	`"double quotes"`	`LIMIT/OFFSET`
Snowflake	`?`	`"double quotes"`	`LIMIT/OFFSET`

Maintaining separate queries for each dialect is error-prone and hard to maintain.

The Solution

sql-flex-query lets you write one query that automatically adapts to any supported dialect. It handles:

✅ Placeholder conversion ($1 → ? → @p1)
✅ Identifier quoting ("col" → `col` → [col])
✅ Pagination syntax (LIMIT/OFFSET vs OFFSET/FETCH)
✅ TypeScript type safety
✅ Dynamic WHERE, ORDER BY, text search (OR conditions)
✅ GROUP BY and HAVING support
✅ Column mapping for clean code

Quick Comparison

Feature	sql-flex-query	Knex.js	Raw SQL	ORM (Prisma/TypeORM)
Multi-dialect	✅ Automatic	✅ Yes	❌ Manual	✅ Limited
Use existing SQL	✅ Yes	❌ No	✅ Yes	❌ No
TypeScript support	✅ Full	⚠️ Partial	❌ None	✅ Full
Learning curve	🟢 Low	🟡 Medium	🟢 Low	🔴 High
Migrations	❌ No	✅ Yes	❌ No	✅ Yes
Size	~15KB	~100KB	0KB	~200KB+
Flexibility	🟢 High	🟡 Medium	🟢 High	🔴 Low

Choose sql-flex-query if: You need multi-dialect support, want to use your existing SQL, value TypeScript types, and don't need built-in migrations.

Installation

npm install sql-flex-query

Quick Start

1. Simple Query with Filters

const { buildQueries } = require("sql-flex-query");

const BASE = `
  SELECT /*SELECT_COLUMNS*/
  FROM users u
  /*WHERE_CLAUSE*/
  /*ORDER_BY*/
  /*LIMIT_CLAUSE*/
`;

const result = buildQueries(
  BASE,
  [
    { key: "status", operation: "EQ", value: "ACTIVE" },
    { key: "age", operation: "GTE", value: 18 },
  ],
  [],
  [{ key: "createdAt", direction: "DESC" }],
  1,  // page
  10, // page size
  { createdAt: "u.created_at" }, // columnMapper (optional)
  ["id", "name", "email", "createdAt"], // selectColumns (optional)
  "postgres" // dialect (optional, defaults to postgres)
);

console.log(result.searchQuery);
// SELECT u.id AS "id", u.name AS "name", u.email AS "email", u.created_at AS "createdAt"
// FROM users u
// WHERE "status" = $1 AND "age" >= $2
// ORDER BY u.created_at DESC
// LIMIT 10 OFFSET 0

console.log(result.params);
// ['ACTIVE', 18]

That's it! The same code works for MySQL, SQLite, SQL Server, Oracle, CockroachDB, and Snowflake—just change the dialect parameter.

Basic Examples

1. Simple SELECT with WHERE and Pagination

const { buildQueries } = require("sql-flex-query");

const BASE = `
  SELECT /*SELECT_COLUMNS*/
  FROM users u
  /*WHERE_CLAUSE*/
  /*ORDER_BY*/
  /*LIMIT_CLAUSE*/
`;

const result = buildQueries(
  BASE,
  [
    { key: "status", operation: "EQ", value: "ACTIVE" },
    { key: "age", operation: "GTE", value: 18 },
  ],
  [],
  [{ key: "createdAt", direction: "DESC" }],
  1,
  10,
  { createdAt: "u.created_at" },
  ["id", "name", "email", "createdAt"],
);
// searchQuery: SELECT u.id AS "id", ... WHERE "status" = $1 AND u.created_at >= $2 ORDER BY ... LIMIT 10 OFFSET 0
// params: ['ACTIVE', 18]

2. BETWEEN — Range Filtering

Use BETWEEN to filter a column within a half-open range ([min, max)). Pass a two-element array as the value.

const result = buildQueries({
  baseQueryTemplate: `
    SELECT /*SELECT_COLUMNS*/
    FROM orders o
    /*WHERE_CLAUSE*/
    /*ORDER_BY*/
    /*LIMIT_CLAUSE*/
  `,
  whereParams: [
    // Matches: o.total >= 100 AND o.total < 500
    { key: "total", operation: "BETWEEN", value: [100, 500] },
    // Matches: o.created_at >= '2024-01-01' AND o.created_at < '2025-01-01'
    { key: "createdAt", operation: "BETWEEN", value: ["2024-01-01", "2025-01-01"] },
  ],
  columnMapper: { total: "o.total", createdAt: "o.created_at" },
  dialect: "postgres",
});
// WHERE (o.total >= $1 AND o.total < $2) AND (o.created_at >= $3 AND o.created_at < $4)
// params: [100, 500, '2024-01-01', '2025-01-01']

Note: BETWEEN generates (col >= min AND col < max) — inclusive lower bound, exclusive upper bound. The value must be an array of exactly two elements.

3. Text Search (OR) + Filters (AND)

const result = buildQueries({
  baseQueryTemplate: `
    SELECT /*SELECT_COLUMNS*/
    FROM products p
    /*WHERE_CLAUSE*/
    /*ORDER_BY*/
    /*LIMIT_CLAUSE*/
  `,
  textSearchParams: [
    { key: "name", operation: "LIKE", value: "%laptop%", ignoreCase: true },
    {
      key: "description",
      operation: "LIKE",
      value: "%laptop%",
      ignoreCase: true,
    },
  ],
  whereParams: [
    { key: "status", operation: "EQ", value: "PUBLISHED" },
    { key: "price", operation: "LTE", value: 2000 },
    { key: "deleted_at", operation: "NULL" },
  ],
  sortBy: [{ key: "price", direction: "ASC" }],
  page: 1,
  size: 20,
  dialect: "postgres",
});
// WHERE (LOWER("name") LIKE $1 OR LOWER("description") LIKE $2)
//   AND "status" = $3 AND "price" <= $4 AND "deleted_at" IS NULL
// params: ['%laptop%', '%laptop%', 'PUBLISHED', 2000]

Complex Examples

4. Multi-table JOIN with Column Mapping

const { buildQueries } = require("sql-flex-query");

const BASE = `
  SELECT /*SELECT_COLUMNS*/
  FROM orders o
  JOIN customers c ON c.id = o.customer_id
  JOIN order_items oi ON oi.order_id = o.id
  JOIN products p ON p.id = oi.product_id
  /*WHERE_CLAUSE*/
  /*ORDER_BY*/
  /*LIMIT_CLAUSE*/
`;

const columnMapper = {
  orderId: "o.id",
  orderDate: "o.created_at",
  orderStatus: "o.status",
  customerName: "c.name",
  customerEmail: "c.email",
  productName: "p.name",
  quantity: "oi.quantity",
  unitPrice: "oi.unit_price",
};

const result = buildQueries({
  baseQueryTemplate: BASE,
  columnMapper,
  selectColumns: [
    "orderId",
    "orderDate",
    "orderStatus",
    "customerName",
    "customerEmail",
    "productName",
    "quantity",
    "unitPrice",
  ],
  whereParams: [
    { key: "orderStatus", operation: "IN", value: ["SHIPPED", "DELIVERED"] },
    { key: "orderDate", operation: "GTE", value: "2024-01-01" },
    { key: "orderDate", operation: "LTE", value: "2024-12-31" },
    { key: "unitPrice", operation: "GT", value: 0 },
  ],
  textSearchParams: [
    {
      key: "customerName",
      operation: "LIKE",
      value: "%john%",
      ignoreCase: true,
    },
    {
      key: "customerEmail",
      operation: "LIKE",
      value: "%john%",
      ignoreCase: true,
    },
    {
      key: "productName",
      operation: "LIKE",
      value: "%john%",
      ignoreCase: true,
    },
  ],
  sortBy: [
    { key: "orderDate", direction: "DESC" },
    { key: "customerName", direction: "ASC" },
  ],
  page: 1,
  size: 25,
  dialect: "postgres",
});

// searchQuery:
// SELECT o.id AS "orderId", o.created_at AS "orderDate", o.status AS "orderStatus",
//        c.name AS "customerName", c.email AS "customerEmail",
//        p.name AS "productName", oi.quantity AS "quantity", oi.unit_price AS "unitPrice"
// FROM orders o
// JOIN customers c ON c.id = o.customer_id
// JOIN order_items oi ON oi.order_id = o.id
// JOIN products p ON p.id = oi.product_id
// WHERE (LOWER(c.name) LIKE $1 OR LOWER(c.email) LIKE $2 OR LOWER(p.name) LIKE $3)
//   AND o.status IN ($4, $5)
//   AND o.created_at >= $6 AND o.created_at <= $7
//   AND oi.unit_price > $8
// ORDER BY o.created_at DESC, c.name ASC
// LIMIT 25 OFFSET 0
//
// params: ['%john%', '%john%', '%john%', 'SHIPPED', 'DELIVERED', '2024-01-01', '2024-12-31', 0]

5. GROUP BY + HAVING (Aggregation Reports)

const { buildQueries } = require("sql-flex-query");

// GROUP BY is part of the template.
// The builder handles WHERE (before GROUP BY) and HAVING (after GROUP BY) automatically.
const BASE = `
  SELECT /*SELECT_COLUMNS*/
  FROM orders o
  JOIN customers c ON c.id = o.customer_id
  JOIN order_items oi ON oi.order_id = o.id
  /*WHERE_CLAUSE*/
  GROUP BY c.id, c.name, c.email
  /*HAVING_CLAUSE*/
  /*ORDER_BY*/
  /*LIMIT_CLAUSE*/
`;

const columnMapper = {
  customerName: "c.name",
  customerEmail: "c.email",
  orderCount: "COUNT(DISTINCT o.id)",
  totalSpent: "SUM(oi.quantity * oi.unit_price)",
  avgOrderValue: "AVG(oi.quantity * oi.unit_price)",
  orderDate: "o.created_at",
  orderStatus: "o.status",
};

const result = buildQueries({
  baseQueryTemplate: BASE,
  columnMapper,
  selectColumns: [
    "customerName",
    "customerEmail",
    "orderCount",
    "totalSpent",
    "avgOrderValue",
  ],
  whereParams: [
    // WHERE filters — applied BEFORE GROUP BY
    { key: "orderStatus", operation: "IN", value: ["COMPLETED", "DELIVERED"] },
    { key: "orderDate", operation: "GTE", value: "2024-01-01" },
    { key: "orderDate", operation: "LTE", value: "2024-12-31" },

    // HAVING filters — set having: true — applied AFTER GROUP BY
    { key: "orderCount", operation: "GTE", value: 5, having: true },
    { key: "totalSpent", operation: "GTE", value: 1000, having: true },
  ],
  sortBy: [{ key: "totalSpent", direction: "DESC" }],
  page: 1,
  size: 10,
  dialect: "postgres",
});

// searchQuery:
// SELECT c.name AS "customerName", c.email AS "customerEmail",
//        COUNT(DISTINCT o.id) AS "orderCount",
//        SUM(oi.quantity * oi.unit_price) AS "totalSpent",
//        AVG(oi.quantity * oi.unit_price) AS "avgOrderValue"
// FROM orders o
// JOIN customers c ON c.id = o.customer_id
// JOIN order_items oi ON oi.order_id = o.id
// WHERE o.status IN ($1, $2) AND o.created_at >= $3 AND o.created_at <= $4
// GROUP BY c.id, c.name, c.email
// HAVING COUNT(DISTINCT o.id) >= $5 AND SUM(oi.quantity * oi.unit_price) >= $6
// ORDER BY SUM(oi.quantity * oi.unit_price) DESC
// LIMIT 10 OFFSET 0
//
// params: ['COMPLETED', 'DELIVERED', '2024-01-01', '2024-12-31', 5, 1000]

6. GROUP BY with modifyCountQuery

When using GROUP BY, the default COUNT gives wrong results. Use modifyCountQuery to wrap the count:

const BASE_WITH_GROUP = `
  SELECT /*SELECT_COLUMNS*/
  FROM app_messages msg
  LEFT JOIN app_locations loc ON msg.location_id = loc.id
  JOIN app_tasks task ON task.id = msg.task_id
  JOIN app_task_case_link tcl ON tcl.task_id = task.id
  JOIN (
    SELECT DISTINCT task_id, case_ref, org_code
    FROM app_case_snapshot WHERE is_current = true
  ) cs ON cs.task_id = tcl.task_id AND cs.case_ref = tcl.case_ref
  /*WHERE_CLAUSE*/
  GROUP BY DATE(task.created_at), cs.org_code, msg.is_structured
  /*HAVING_CLAUSE*/
  /*ORDER_BY*/ /*LIMIT_CLAUSE*/
`;

const columnMapper = {
  createdDate: "DATE(task.created_at)",
  organizationCode: "cs.org_code",
  structured: "msg.is_structured",
  messageCount: "COUNT(msg.id)",
  locationName: "loc.display_name",
  state: "msg.state",
};

const result = buildQueries({
  baseQueryTemplate: BASE_WITH_GROUP,
  columnMapper,
  selectColumns: [
    "createdDate",
    "organizationCode",
    "structured",
    "messageCount",
  ],
  whereParams: [
    { key: "state", operation: "EQ", value: "PROCESSED" },
    { key: "locationName", operation: "NOT_NULL" },
    { key: "messageCount", operation: "GT", value: 10, having: true },
  ],
  sortBy: [
    { key: "createdDate", direction: "DESC" },
    { key: "messageCount", direction: "DESC" },
  ],
  page: 1,
  size: 20,
  dialect: "postgres",
  // Wrap count query to count groups, not rows within groups
  modifyCountQuery: (query) =>
    `SELECT COUNT(*) AS count FROM (${query}) AS grouped_count`,
});

// countQuery:
// SELECT COUNT(*) AS count FROM (
//   SELECT 1 FROM app_messages msg
//   LEFT JOIN app_locations loc ON msg.location_id = loc.id
//   JOIN app_tasks task ON task.id = msg.task_id
//   JOIN app_task_case_link tcl ON tcl.task_id = task.id
//   JOIN (
//     SELECT DISTINCT task_id, case_ref, org_code
//     FROM app_case_snapshot WHERE is_current = true
//   ) cs ON cs.task_id = tcl.task_id AND cs.case_ref = tcl.case_ref
//   WHERE msg.state = $1 AND loc.display_name IS NOT NULL
//   GROUP BY DATE(task.created_at), cs.org_code, msg.is_structured
//   HAVING COUNT(msg.id) > $2
// ) AS grouped_count

7. LEFT JOIN + DISTINCT (Fluent API)

const { QueryBuilder } = require("sql-flex-query");

const BASE = `
  SELECT /*SELECT_COLUMNS*/
  FROM employees e
  LEFT JOIN departments d ON d.id = e.department_id
  LEFT JOIN employee_skills es ON es.employee_id = e.id
  LEFT JOIN skills s ON s.id = es.skill_id
  /*WHERE_CLAUSE*/
  /*ORDER_BY*/
  /*LIMIT_CLAUSE*/
`;

const result = new QueryBuilder("postgres")
  .baseQuery(BASE)
  .columnMapper({
    employeeId: "e.id",
    employeeName: "e.name",
    employeeEmail: "e.email",
    departmentName: "d.name",
    hireDate: "e.hire_date",
    salary: "e.salary",
    skillName: "s.name",
  })
  .select(["employeeId", "employeeName", "employeeEmail", "departmentName"])
  .distinct()
  .where([
    {
      key: "departmentName",
      operation: "IN",
      value: ["Engineering", "Product", "Design"],
    },
    { key: "hireDate", operation: "GTE", value: "2023-01-01" },
    { key: "salary", operation: "GTE", value: 50000 },
    { key: "salary", operation: "LTE", value: 150000 },
  ])
  .textSearch([
    {
      key: "employeeName",
      operation: "LIKE",
      value: "%sarah%",
      ignoreCase: true,
    },
    {
      key: "employeeEmail",
      operation: "LIKE",
      value: "%sarah%",
      ignoreCase: true,
    },
    { key: "skillName", operation: "LIKE", value: "%sarah%", ignoreCase: true },
  ])
  .orderBy([
    { key: "departmentName", direction: "ASC" },
    { key: "employeeName", direction: "ASC" },
  ])
  .paginate(2, 15)
  .build();

// searchQuery:
// SELECT DISTINCT e.id AS "employeeId", e.name AS "employeeName",
//        e.email AS "employeeEmail", d.name AS "departmentName"
// FROM employees e
// LEFT JOIN departments d ON d.id = e.department_id
// LEFT JOIN employee_skills es ON es.employee_id = e.id
// LEFT JOIN skills s ON s.id = es.skill_id
// WHERE (LOWER(e.name) LIKE $1 OR LOWER(e.email) LIKE $2 OR LOWER(s.name) LIKE $3)
//   AND d.name IN ($4, $5, $6)
//   AND e.hire_date >= $7 AND e.salary >= $8 AND e.salary <= $9
// ORDER BY d.name ASC, e.name ASC
// LIMIT 15 OFFSET 15
//
// params: ['%sarah%', '%sarah%', '%sarah%', 'Engineering', 'Product', 'Design',
//          '2023-01-01', 50000, 150000]

8. Sales Dashboard (Fluent API + GROUP BY + HAVING + MSSQL)

const { QueryBuilder } = require("sql-flex-query");

const BASE = `
  SELECT /*SELECT_COLUMNS*/
  FROM sales s
  JOIN sales_reps sr ON sr.id = s.rep_id
  JOIN regions r ON r.id = sr.region_id
  JOIN products p ON p.id = s.product_id
  JOIN product_categories pc ON pc.id = p.category_id
  /*WHERE_CLAUSE*/
  GROUP BY r.name, sr.name, pc.name, YEAR(s.sale_date), MONTH(s.sale_date)
  /*HAVING_CLAUSE*/
  /*ORDER_BY*/ /*LIMIT_CLAUSE*/
`;

const result = new QueryBuilder("mssql")
  .baseQuery(BASE)
  .columnMapper({
    regionName: "r.name",
    repName: "sr.name",
    categoryName: "pc.name",
    saleYear: "YEAR(s.sale_date)",
    saleMonth: "MONTH(s.sale_date)",
    totalRevenue: "SUM(s.amount)",
    totalUnits: "SUM(s.quantity)",
    dealCount: "COUNT(s.id)",
    avgDealSize: "AVG(s.amount)",
    saleDate: "s.sale_date",
    saleStatus: "s.status",
  })
  .select([
    "regionName",
    "repName",
    "categoryName",
    "saleYear",
    "saleMonth",
    "totalRevenue",
    "totalUnits",
    "dealCount",
    "avgDealSize",
  ])
  .where([
    { key: "saleDate", operation: "GTE", value: "2024-01-01" },
    { key: "saleDate", operation: "LTE", value: "2024-12-31" },
    { key: "saleStatus", operation: "EQ", value: "CLOSED_WON" },
    {
      key: "regionName",
      operation: "IN",
      value: ["North America", "Europe", "APAC"],
    },
  ])
  .having([
    { key: "totalRevenue", operation: "GTE", value: 50000 },
    { key: "dealCount", operation: "GTE", value: 3 },
  ])
  .orderBy([
    { key: "totalRevenue", direction: "DESC" },
    { key: "regionName", direction: "ASC" },
  ])
  .paginate(1, 20)
  .modifyCountQuery((query) => `SELECT COUNT(*) AS count FROM (${query}) AS t`)
  .build();

// searchQuery (SQL Server):
// SELECT r.name AS [regionName], sr.name AS [repName], pc.name AS [categoryName],
//        YEAR(s.sale_date) AS [saleYear], MONTH(s.sale_date) AS [saleMonth],
//        SUM(s.amount) AS [totalRevenue], SUM(s.quantity) AS [totalUnits],
//        COUNT(s.id) AS [dealCount], AVG(s.amount) AS [avgDealSize]
// FROM sales s
// JOIN sales_reps sr ON sr.id = s.rep_id
// JOIN regions r ON r.id = sr.region_id
// JOIN products p ON p.id = s.product_id
// JOIN product_categories pc ON pc.id = p.category_id
// WHERE s.sale_date >= @p1 AND s.sale_date <= @p2
//   AND s.status = @p3 AND r.name IN (@p4, @p5, @p6)
// GROUP BY r.name, sr.name, pc.name, YEAR(s.sale_date), MONTH(s.sale_date)
// HAVING SUM(s.amount) >= @p7 AND COUNT(s.id) >= @p8
// ORDER BY SUM(s.amount) DESC, r.name ASC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY
//
// params: ['2024-01-01', '2024-12-31', 'CLOSED_WON',
//          'North America', 'Europe', 'APAC', 50000, 3]

9. Subquery JOIN with MySQL (Fluent API)

const { QueryBuilder } = require("sql-flex-query");

const BASE = `
  SELECT /*SELECT_COLUMNS*/
  FROM users u
  JOIN (
    SELECT user_id,
           COUNT(*) as login_count,
           MAX(login_at) as last_login
    FROM login_history
    WHERE login_at >= '2024-01-01'
    GROUP BY user_id
  ) lh ON lh.user_id = u.id
  LEFT JOIN user_subscriptions us ON us.user_id = u.id AND us.is_active = true
  LEFT JOIN plans p ON p.id = us.plan_id
  /*WHERE_CLAUSE*/
  /*ORDER_BY*/
  /*LIMIT_CLAUSE*/
`;

const result = new QueryBuilder("mysql")
  .baseQuery(BASE)
  .columnMapper({
    userId: "u.id",
    userName: "u.name",
    userEmail: "u.email",
    loginCount: "lh.login_count",
    lastLogin: "lh.last_login",
    planName: "p.name",
    userStatus: "u.status",
    userRole: "u.role",
  })
  .select([
    "userId",
    "userName",
    "userEmail",
    "loginCount",
    "lastLogin",
    "planName",
  ])
  .where([
    { key: "userStatus", operation: "EQ", value: "ACTIVE" },
    { key: "loginCount", operation: "GTE", value: 10 },
    {
      key: "userRole",
      operation: "IN",
      value: ["ADMIN", "PREMIUM", "ENTERPRISE"],
    },
    { key: "planName", operation: "NOT_NULL" },
  ])
  .textSearch([
    {
      key: "userName",
      operation: "LIKE",
      value: "%search_term%",
      ignoreCase: true,
    },
    {
      key: "userEmail",
      operation: "LIKE",
      value: "%search_term%",
      ignoreCase: true,
    },
  ])
  .orderBy([
    { key: "loginCount", direction: "DESC" },
    { key: "lastLogin", direction: "DESC" },
  ])
  .paginate(1, 50)
  .build();

// searchQuery (MySQL):
// SELECT u.id AS \`userId\`, u.name AS \`userName\`, u.email AS \`userEmail\`,
//        lh.login_count AS \`loginCount\`, lh.last_login AS \`lastLogin\`,
//        p.name AS \`planName\`
// FROM users u
// JOIN (...) lh ON lh.user_id = u.id
// LEFT JOIN user_subscriptions us ON us.user_id = u.id AND us.is_active = true
// LEFT JOIN plans p ON p.id = us.plan_id
// WHERE (\`userName\` LIKE ? OR \`userEmail\` LIKE ?)
//   AND u.status = ? AND lh.login_count >= ?
//   AND u.role IN (?, ?, ?) AND p.name IS NOT NULL
// ORDER BY lh.login_count DESC, lh.last_login DESC
// LIMIT 50 OFFSET 0
//
// params: ['%search_term%', '%search_term%', 'ACTIVE', 10, 'ADMIN', 'PREMIUM', 'ENTERPRISE']

Dialect Helpers for INSERT / UPDATE / DELETE

The dialectHelpers() factory provides dialect-aware utilities for building INSERT, UPDATE, DELETE, and custom queries — without prescribing a specific pattern. You get the building blocks; you compose the final SQL.

10. INSERT Query (Postgres)

const { dialectHelpers } = require("sql-flex-query");

const h = dialectHelpers("postgres");
const columnMapper = { name: "u.name", email: "u.email", role: "u.role" };

const data = { name: "John Doe", email: "john@example.com", role: "ADMIN" };
const { columns, placeholders, params } = h.buildInsertValues(
  data,
  columnMapper,
);

const query = `INSERT INTO users (${columns.join(", ")}) VALUES (${placeholders.join(", ")}) RETURNING *`;
// query:  INSERT INTO users (u.name, u.email, u.role) VALUES ($1, $2, $3) RETURNING *
// params: ['John Doe', 'john@example.com', 'ADMIN']

Same query with MySQL:

const h = dialectHelpers("mysql");
const { columns, placeholders, params } = h.buildInsertValues(
  data,
  columnMapper,
);

const query = `INSERT INTO users (${columns.join(", ")}) VALUES (${placeholders.join(", ")})`;
// query:  INSERT INTO users (u.name, u.email, u.role) VALUES (?, ?, ?)
// params: ['John Doe', 'john@example.com', 'ADMIN']

11. UPDATE Query with WHERE (Postgres & MSSQL)

Use buildWhereClause with existingParams — the same API you use for SELECT WHERE clauses. When you pass the SET params array, placeholder numbering continues automatically.

const { dialectHelpers } = require("sql-flex-query");

const h = dialectHelpers("postgres");
const columnMapper = { name: "u.name", email: "u.email", id: "u.id" };

// 1. Build SET clause
const data = { name: "Jane Doe", email: "jane@example.com" };
const { setClause, params } = h.buildSetClause(data, columnMapper);
// setClause: 'u.name = $1, u.email = $2'
// params:    ['Jane Doe', 'jane@example.com']

// 2. Build WHERE — pass `params` so placeholders continue from $3
const { clause } = h.buildWhereClause(
  [{ key: "id", operation: "EQ", value: 42 }],
  [],
  columnMapper,
  params, // ← same params array
);

const query = `UPDATE users SET ${setClause}${clause} RETURNING *`;
// query:  UPDATE users SET u.name = $1, u.email = $2 WHERE u.id = $3 RETURNING *
// params: ['Jane Doe', 'jane@example.com', 42]

Same query with MSSQL:

const h = dialectHelpers("mssql");
const { setClause, params } = h.buildSetClause(data, columnMapper);

const { clause } = h.buildWhereClause(
  [{ key: "id", operation: "EQ", value: 42 }],
  [],
  columnMapper,
  params,
);

const query = `UPDATE users SET ${setClause}${clause}`;
// query:  UPDATE users SET u.name = @p1, u.email = @p2 WHERE u.id = @p3
// params: ['Jane Doe', 'jane@example.com', 42]

UPDATE with complex WHERE (multiple criteria, IN, etc.):

const h = dialectHelpers("postgres");
const columnMapper = {
  status: "u.status",
  role: "u.role",
  lastLogin: "u.last_login",
};

const { setClause, params } = h.buildSetClause(
  { status: "INACTIVE" },
  columnMapper,
);

// Same buildWhereClause you'd use for SELECT — just pass params to continue numbering
const { clause } = h.buildWhereClause(
  [
    { key: "role", operation: "IN", value: ["GUEST", "TRIAL"] },
    { key: "lastLogin", operation: "LTE", value: "2023-01-01" },
  ],
  [],
  columnMapper,
  params,
);

const query = `UPDATE users SET ${setClause}${clause}`;
// query:  UPDATE users SET u.status = $1 WHERE u.role IN ($2, $3) AND u.last_login <= $4
// params: ['INACTIVE', 'GUEST', 'TRIAL', '2023-01-01']

12. DELETE Query with WHERE (Postgres)

const { dialectHelpers } = require("sql-flex-query");

const h = dialectHelpers("postgres");
const columnMapper = {
  id: "u.id",
  status: "u.status",
  deletedAt: "u.deleted_at",
};

const { clause, params } = h.buildWhereClause(
  [
    { key: "status", operation: "EQ", value: "DEACTIVATED" },
    { key: "deletedAt", operation: "NOT_NULL" },
  ],
  [],
  columnMapper,
);

const query = `DELETE FROM users${clause}`;
// query:  DELETE FROM users WHERE u.status = $1 AND u.deleted_at IS NOT NULL
// params: ['DEACTIVATED']

Low-Level Helpers

You can also import individual utilities for maximum flexibility:

const {
  createDialect,
  getKey,
  prepareWhereClause,
  prepareClause,
} = require("sql-flex-query");

const dialect = createDialect("postgres");
const mapper = { firstName: "u.first_name" };

// Resolve column names
getKey(mapper, "firstName", dialect); // 'u.first_name'
getKey(mapper, "age", dialect); // '"age"' (auto-quoted)

// Generate placeholder
dialect.placeholder(1); // '$1'
dialect.quoteIdentifier("name"); // '"name"'

// Build a single clause
const params = [];
prepareClause(
  { key: "status", operation: "EQ", value: "ACTIVE" },
  params,
  mapper,
  dialect,
);
// returns: '"status" = $1',  params: ['ACTIVE']

Extending with Custom Dialects

You can add support for custom databases by extending the BaseDialect class and registering your dialect with registerDialect().

Creating a Custom Dialect

import { BaseDialect, DialectName } from 'sql-flex-query';

class MyDialect extends BaseDialect {
  name: DialectName = 'mydialect';

  placeholder(position: number): string {
    return `:${position}`; // e.g., :1, :2
  }

  quoteIdentifier(identifier: string): string {
    return `"${identifier}"`; // double quotes
  }

  // Override if your DB has special pagination syntax
  requiresOrderByForPagination: boolean = false;
  mergesPaginationWithOrderBy: boolean = false;
  // Use default LIMIT/OFFSET or override paginationClause()
}

// Register your dialect
import { registerDialect } from 'sql-flex-query';
registerDialect('mydialect', () => new MyDialect());

// Now you can use it
const result = buildQueries({
  baseQueryTemplate: BASE,
  whereParams: [...],
  dialect: 'mydialect',
});

Using Custom Dialect Directly

You can also instantiate dialects directly without registration:

import { MyDialect } from "./my-dialect";
const dialect = new MyDialect();
const helpers = dialectHelpers(dialect); // or use dialect directly

Template Placeholders Reference

Placeholder	Required	Description
`/SELECT_COLUMNS/`	Yes	Replaced with column list or `*`
`/WHERE_CLAUSE/`	Yes	Replaced with `WHERE ...` or empty string
`/ORDER_BY/`	Yes	Replaced with `ORDER BY ...` or empty string
`/LIMIT_CLAUSE/`	Yes	Replaced with pagination clause or empty string
`/HAVING_CLAUSE/`	Optional	Replaced with `HAVING ...` (use with GROUP BY)

Operations Reference

Operation	SQL	Needs Value	Example
`EQ`	`col = ?`	Yes	`{ key: 'status', operation: 'EQ', value: 'ACTIVE' }`
`NEQ`	`col <> ?`	Yes	`{ key: 'role', operation: 'NEQ', value: 'GUEST' }`
`LIKE`	`col LIKE ?`	Yes	`{ key: 'name', operation: 'LIKE', value: '%john%' }`
`NOT_LIKE`	`col NOT LIKE ?`	Yes	`{ key: 'email', operation: 'NOT_LIKE', value: '%spam%' }`
`GT`	`col > ?`	Yes	`{ key: 'age', operation: 'GT', value: 18 }`
`LT`	`col < ?`	Yes	`{ key: 'price', operation: 'LT', value: 100 }`
`GTE`	`col >= ?`	Yes	`{ key: 'score', operation: 'GTE', value: 90 }`
`LTE`	`col <= ?`	Yes	`{ key: 'weight', operation: 'LTE', value: 80 }`
`IN`	`col IN (?, ?)`	Yes (array)	`{ key: 'status', operation: 'IN', value: ['A', 'B'] }`
`NULL`	`col IS NULL`	No	`{ key: 'deleted_at', operation: 'NULL' }`
`NOT_NULL`	`col IS NOT NULL`	No	`{ key: 'verified_at', operation: 'NOT_NULL' }`
`BETWEEN`	`col >= ? AND col < ?`	Yes (2-item array)	`{ key: 'age', operation: 'BETWEEN', value: [18, 65] }`

Demo Projects

Check out the demo repository for complete working examples:

https://github.com/ashishlohia70/sql-flex-query-demo

Community & Support

⭐ Star this repo: If you find this library useful, please give it a star on GitHub — it helps others discover it!
🐛 Report issues: Found a bug or have a feature request? Open an issue.
💬 Ask questions: Use GitHub Discussions for Q&A.
🔧 Contribute: Pull requests are welcome! See CONTRIBUTING.md for guidelines.
📖 Follow updates: Watch the repository or follow @ashishlohia70 on GitHub.

License

MIT

Name	Name	Last commit message	Last commit date
Latest commit History 27 Commits 27 Commits
.github	.github
examples	examples
src	src
tests	tests
.gitignore	.gitignore
.npmignore	.npmignore
CODE_OF_CONDUCT.md	CODE_OF_CONDUCT.md
CONTRIBUTING.md	CONTRIBUTING.md
LICENSE	LICENSE
README.md	README.md
SECURITY.md	SECURITY.md
package-lock.json	package-lock.json
package.json	package.json
tsconfig.json	tsconfig.json
tsup.config.ts	tsup.config.ts
vitest.config.ts	vitest.config.ts

Search code, repositories, users, issues, pull requests...

Folders and files

Latest commit

History

Repository files navigation