DZone
Data Engineering
Databases
Stop Writing Dialect-Specific SQL: A Unified Query Builder for Node.js

Stop Writing Dialect-Specific SQL: A Unified Query Builder for Node.js

Learn how sql-flex-query lets you write once, run anywhere — with full TypeScript support and zero runtime overhead. Support SQL queries for multiple databases

👁 Ashish Lohia user avatar

Ashish Lohia

May. 20, 26 · Tutorial

Likes (0)

Comment

Save

1.8K Views

Join the DZone community and get the full member experience.

Join For Free

The Problem Most Backend Developers Face

You're building a SaaS application that needs to support multiple databases. Or maybe you're migrating from MySQL to PostgreSQL. Or you have different clients using different database engines.

Whatever the reason, you've likely encountered this nightmare:

JavaScript

// PostgreSQL version
const pgQuery = `
 SELECT id, name, email, created_at
 FROM users
 WHERE status = $1 AND age >= $2
 ORDER BY created_at DESC
 LIMIT $3 OFFSET $4
`;

// MySQL version
const mysqlQuery = `
 SELECT id, name, email, created_at
 FROM users
 WHERE status = ? AND age >= ?
 ORDER BY created_at DESC
 LIMIT ?, ?
`;

// SQL Server version
const mssqlQuery = `
 SELECT id, name, email, created_at
 FROM users
 WHERE status = @p1 AND age >= @p2
 ORDER BY created_at DESC
  OFFSET @p3 ROWS FETCH NEXT @p4 ROWS ONLY

Same logic. Three different query strings. Three different parameter styles. Three different pagination syntaxes.

This is not just duplication — it's a maintenance disaster waiting to happen.

What if You Could Write Once, Run Anywhere?

Imagine writing a single query that automatically adapts to any SQL dialect:

JavaScript

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

const BASE = `
 SELECT /*SELECT_COLUMNS*/
 FROM users
 /*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
);

console.log(result.searchQuery);

Output for PostgreSQL:

SQL

SELECT id, name, email, created_at
FROM users
WHERE "status" = $1 AND "age" >= $2
ORDER BY created_at DESC
LIMIT 10 OFFSET 0

Output for MySQL:

SQL

SELECT id, name, email, created_at
FROM users
WHERE `status` = ? AND `age` >= ?
ORDER BY created_at DESC
LIMIT 10, 0

-- params: ['ACTIVE', 18]

Output for SQL Server:

SQL

SELECT id, name, email, created_at
FROM users
WHERE [status] = @p1 AND [age] >= @p2
ORDER BY created_at DESC
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

-- params: ['ACTIVE', 18]

Same code. Three different dialects. Zero manual string concatenation.

Why This Matters in Production

1. Code Maintainability

When you have separate queries for each database:

Bug fixes must be applied to all versions
New features require multiple implementations
Code reviews become 3x harder
Testing complexity multiplies

With a unified query builder, you maintain one codebase that works across all databases.

2. Database Flexibility

Your application can:

Support different databases per customer (multi-tenancy)
Migrate between databases with minimal changes
Use different databases for different environments (Postgres in production, SQLite in tests)
Add support for new databases without rewriting queries

3. Type Safety With TypeScript

sql-flex-query is written in TypeScript and provides full type inference:

TypeScript

interface ColumnMapper {
 userId: "u.id";
 userName: "u.name";
 userEmail: "u.email";
 createdAt: "u.created_at";
}

const result = buildQueries<ColumnMapper>({
 baseQueryTemplate: BASE,
 columnMapper,
 selectColumns: ["userId", "userName", "userEmail"],
  // TypeScript knows these must match keys in ColumnMapper

Autocomplete catches typos. Refactoring is safe. Documentation is built in.

Key Features That Make It Production-Ready

1. Dynamic WHERE Clauses With Automatic Grouping

Build complex conditions without manual parentheses:

JavaScript

const result = buildQueries({
 baseQueryTemplate: BASE,
 textSearchParams: [
 { key: "name", operation: "LIKE", value: "%john%", ignoreCase: true },
 { key: "email", operation: "LIKE", value: "%john%", ignoreCase: true },
 ],
 whereParams: [
 { key: "status", operation: "EQ", value: "ACTIVE" },
 { key: "age", operation: "GTE", value: 18 },
  ],

Generated SQL:

SQL

WHERE (LOWER(name) LIKE $1 OR LOWER(email) LIKE $2)
  AND "status" = $3 AND "age" >= $4

Notice: Text search uses OR (grouped), filters use AND. Automatic.

2. Dialect-Aware Placeholders

No more manual placeholder conversion:

Database	Placeholder	Identifier Quote
PostgreSQL	`$1, $2`	`"double quotes"`
MySQL	`?`	`backticks`
SQLite	`?`	`"double quotes"`
SQL Server	`@p1, @p2`	`[brackets]`
Oracle	`:1, :2`	`"double quotes"`
CockroachDB	`$1, $2`	`"double quotes"`
Snowflake	`?`	`"double quotes"`

The library handles all of this automatically based on the dialect parameter.

3. Pagination That Just Works

Different databases, different pagination syntax. The library abstracts it away:

JavaScript

const result = buildQueries(BASE, [], [], [], page, size);

PostgreSQL/MySQL/SQLite/CockroachDB/Snowflake: LIMIT size OFFSET (page-1)*size
SQL Server/Oracle: OFFSET offset ROWS FETCH NEXT size ROWS ONLY

You specify page and size. The library generates correct SQL for your dialect.

4. Column Mapping for Clean Code

Instead of writing raw SQL column names throughout your code:

JavaScript

const columnMapper = {
 userId: "u.id",
 userName: "u.name",
 userEmail: "u.email",
 createdAt: "u.created_at",
};

const result = buildQueries({
 baseQueryTemplate: BASE,
 columnMapper,
 selectColumns: ["userId", "userName", "userEmail"],
  // Internally maps to u.id, u.name, u.email

Benefits:

Business logic uses semantic names (userId), not database columns (u.id)
Easy to refactor if database schema changes
Self-documenting code
TypeScript ensures consistency

5. GROUP BY and HAVING Support

Aggregation queries are tricky because the default COUNT(*) gives wrong results with GROUP BY. Use modifyCountQuery:

JavaScript

const BASE_WITH_GROUP = `
 SELECT /*SELECT_COLUMNS*/
 FROM orders o
 JOIN customers c ON c.id = o.customer_id
 /*WHERE_CLAUSE*/
 GROUP BY c.id, c.name
 /*HAVING_CLAUSE*/
 /*ORDER_BY*/ /*LIMIT_CLAUSE*/
`;

const columnMapper = {
 customerName: "c.name",
 orderCount: "COUNT(o.id)",
 totalSpent: "SUM(o.amount)",
};

const result = buildQueries({
 baseQueryTemplate: BASE_WITH_GROUP,
 columnMapper,
 selectColumns: ["customerName", "orderCount", "totalSpent"],
 whereParams: [{ key: "orderDate", operation: "GTE", value: "2024-01-01" }],
 havingParams: [{ key: "orderCount", operation: "GTE", value: 5, having: true }],
 page: 1,
 size: 20,
 modifyCountQuery: (query) =>
    `SELECT COUNT(*) AS count FROM (${query}) AS grouped_count`,

The modifyCountQuery wrapper ensures pagination counts groups, not rows.

6. Fluent API for Complex Queries

For programmatic query building, use the QueryBuilder class:

JavaScript

const result = new QueryBuilder("postgres")
 .baseQuery(BASE)
 .columnMapper(columnMapper)
 .select(["userId", "userName"])
 .where([{ key: "status", operation: "EQ", value: "ACTIVE" }])
 .textSearch([{ key: "name", operation: "LIKE", value: "%john%", ignoreCase: true }])
 .orderBy([{ key: "createdAt", direction: "DESC" }])
 .paginate(1, 20)
 .distinct()
  .build();

Perfect for dynamic filters from API requests.

Real-World Example: E-Commerce Product Search

Let's build a product search API with:

Text search across name and description
Filters: category, price range, in-stock only
Sorting: price, name, created date
Pagination

JavaScript

const BASE = `
 SELECT /*SELECT_COLUMNS*/
 FROM products p
 JOIN categories c ON c.id = p.category_id
 /*WHERE_CLAUSE*/
 /*ORDER_BY*/
 /*LIMIT_CLAUSE*/
`;

const columnMapper = {
 productId: "p.id",
 productName: "p.name",
 description: "p.description",
 price: "p.price",
 inStock: "p.stock_quantity > 0",
 categoryName: "c.name",
 createdAt: "p.created_at",
};

const buildProductSearch = (filters) => {
 return buildQueries({
 baseQueryTemplate: BASE,
 columnMapper,
 selectColumns: ["productId", "productName", "price", "categoryName", "createdAt"],
 textSearchParams: filters.searchTerm
 ? [
 { key: "productName", operation: "LIKE", value: `%${filters.searchTerm}%`, ignoreCase: true },
 { key: "description", operation: "LIKE", value: `%${filters.searchTerm}%`, ignoreCase: true },
 ]
 : [],
 whereParams: [
 ...(filters.category ? [{ key: "categoryName", operation: "EQ", value: filters.category }] : []),
 ...(filters.minPrice ? [{ key: "price", operation: "GTE", value: filters.minPrice }] : []),
 ...(filters.maxPrice ? [{ key: "price", operation: "LTE", value: filters.maxPrice }] : []),
 { key: "inStock", operation: "EQ", value: true },
 ],
 sortBy: filters.sortBy
 ? [{ key: filters.sortBy, direction: filters.sortDir || "ASC" }]
 : [{ key: "createdAt", direction: "DESC" }],
 page: filters.page || 1,
 size: filters.size || 20,
 dialect: filters.dialect || "postgres",
 });
};

// Usage
const result = buildProductSearch({
 searchTerm: "laptop",
 category: "Electronics",
 minPrice: 500,
 maxPrice: 2000,
 sortBy: "price",
 sortDir: "ASC",
 page: 1,
 size: 20,
  dialect: "postgres",

Generated SQL:

SQL

SELECT p.id AS "productId", p.name AS "productName", p.price AS "price",
 c.name AS "categoryName", p.created_at AS "createdAt"
FROM products p
JOIN categories c ON c.id = p.category_id
WHERE (LOWER(p.name) LIKE $1 OR LOWER(p.description) LIKE $2)
 AND c.name = $3
 AND p.price >= $4 AND p.price <= $5
 AND p.stock_quantity > 0 = true
ORDER BY price ASC
LIMIT 20 OFFSET 0

Change dialect: "mysql" and the same code generates MySQL-compatible SQL with ? placeholders and backticks.

Comparison With Alternatives

Knex.js

Knex is a popular query builder, but it has different use cases:

Feature	sql-flex-query	Knex.js
Primary Focus	Enhancing existing SQL templates	Building queries programmatically
Multi-Dialect	✅ Automatic placeholder/quote handling	✅ Yes, but you write Knex DSL
SQL Templates	✅ Use your own SQL with placeholders	❌ No, you use Knex's API
Learning Curve	Low (just learn the param format)	Medium (learn Knex's DSL)
Migrations	❌ No (use your own)	✅ Built-in migration system
TypeScript	✅ Full type support	⚠️ Limited, community types
Size	~15KB	~100KB
Best For	Apps with existing SQL, multi-dialect needs	Apps needing migrations, seed data

When to choose sql-flex-query:

You already have SQL queries (from legacy code, DB team, etc.)
You need to support multiple databases with minimal code changes
You want full TypeScript support
You don't need built-in migrations (use your own tooling)

When to choose Knex:

You're starting from scratch and want a fluent API
You need built-in migrations and seed support
You're okay with learning a DSL
Single database dialect is fine

Raw SQL With Manual Placeholders

You might think: "I'll just write parameterized queries myself."

JavaScript

// Manual approach
const query = dialect === "postgres"
 ? `SELECT * FROM users WHERE status = $1 AND age >= $2`
 : dialect === "mysql"
  ? `SELECT * FROM users WHERE status = ? AND age >= ?`

Problems:

Error-prone: Easy to forget a case
Hard to test: Need to test each branch
No abstraction: Business logic mixed with dialect logic
No advanced features: No automatic WHERE grouping, no column mapping, no pagination abstraction

ORMs (Prisma, TypeORM, Sequelize)

ORMs are great for full object-relational mapping, but they come with trade-offs:

Learning curve: Must learn the ORM's API
Performance: N+1 queries if not careful
Flexibility: Complex queries can be awkward
Control: ORM generates SQL, you don't write it

sql-flex-query is not an ORM. It's a query builder that works with your existing SQL. Use it when:

You want full control over SQL
You need complex queries that ORMs struggle with
You have database-specific optimizations
You want to avoid ORM abstraction penalties

Getting Started in 5 Minutes

Installation

Shell

npm install sql-flex-query

Basic Usage

JavaScript

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

const BASE = `
 SELECT /*SELECT_COLUMNS*/
 FROM users
 /*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); // The generated SQL
console.log(result.params);      // Parameter values array

That's it. No configuration. No complex setup.

Supported Databases

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`

All seven dialects are fully supported and tested.

Advanced Patterns

1. Text Search With OR Conditions

JavaScript

const result = buildQueries({
 baseQueryTemplate: BASE,
 textSearchParams: [
 { key: "firstName", operation: "LIKE", value: "%john%", ignoreCase: true },
 { key: "lastName", operation: "LIKE", value: "%doe%", ignoreCase: true },
 { key: "email", operation: "LIKE", value: "%john%", ignoreCase: true },
 ],
 whereParams: [
 { key: "status", operation: "EQ", value: "ACTIVE" },
  ],

Generated:

SQL

WHERE (LOWER(firstName) LIKE $1 OR LOWER(lastName) LIKE $2 OR LOWER(email) LIKE $3)

Text search params are automatically grouped with OR. Filters use AND.

2. IN Operations

JavaScript

const result = buildQueries({
 baseQueryTemplate: BASE,
 whereParams: [
 { key: "status", operation: "IN", value: ["ACTIVE", "PENDING", "VERIFIED"] },
 { key: "role", operation: "IN", value: ["ADMIN", "MODERATOR"] },
  ],

Generated:

SQL

WHERE "status" IN ($1, $2, $3) AND "role" IN ($4, $5)

The builder automatically expands the IN array into the correct number of placeholders.

3. NULL and NOT NULL

JavaScript

const result = buildQueries({
 baseQueryTemplate: BASE,
 whereParams: [
 { key: "deletedAt", operation: "NULL" },
 { key: "email", operation: "NOT_NULL" },
 ],
});

Generated:

SQL

WHERE "deletedAt" IS NULL AND "email" IS NOT NULL

4. Complex JOINs With Column Mapping

JavaScript

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",
 customerName: "c.name",
 productName: "p.name",
 quantity: "oi.quantity",
 unitPrice: "oi.unit_price",
};

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

Generated:

SQL

SELECT o.id AS "orderId", o.created_at AS "orderDate",
 c.name AS "customerName", 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(p.name) LIKE $2)
 AND o.status IN ($3, $4)
 AND o.created_at >= $5
ORDER BY o.created_at DESC
LIMIT 25 OFFSET 0

Testing Strategy

Because sql-flex-query generates SQL, you should test the generated queries:

TypeScript

import { describe, it, expect } from "vitest";
import { buildQueries } from "sql-flex-query";

describe("User search queries", () => {
 it("generates correct PostgreSQL syntax", () => {
 const BASE = `SELECT /*SELECT_COLUMNS*/ FROM users /*WHERE_CLAUSE*/ /*ORDER_BY*/ /*LIMIT_CLAUSE*/`;

 const result = buildQueries(
 BASE,
 [{ key: "status", operation: "EQ", value: "ACTIVE" }],
 [],
 [{ key: "createdAt", direction: "DESC" }],
 1,
 10,
 undefined,
 undefined,
 "postgres"
 );

 expect(result.searchQuery).toContain('"status" = $1');
 expect(result.searchQuery).toContain("LIMIT 10 OFFSET 0");
 expect(result.params).toEqual(["ACTIVE"]);
 });

 it("generates correct MySQL syntax", () => {
 const BASE = `SELECT /*SELECT_COLUMNS*/ FROM users /*WHERE_CLAUSE*/ /*ORDER_BY*/ /*LIMIT_CLAUSE*/`;

 const result = buildQueries(
 BASE,
 [{ key: "status", operation: "EQ", value: "ACTIVE" }],
 [],
 [{ key: "createdAt", direction: "DESC" }],
 1,
 10,
 undefined,
 undefined,
 "mysql"
 );

 expect(result.searchQuery).toContain('`status` = ?');
 expect(result.searchQuery).toContain("LIMIT 10, 0");
 expect(result.params).toEqual(["ACTIVE"]);
  });

The library includes comprehensive tests for all dialects and edge cases.

Performance Considerations

sql-flex-query adds minimal overhead:

Query generation: ~0.1-0.5ms per query (negligible)
No runtime parsing: Direct string manipulation
No connection pooling: Just query generation (use your own pool)
Memory: Lightweight, ~15KB gzipped

The generated SQL is identical to what you'd write by hand (just with different placeholders). Database execution performance is the same as raw SQL.

When NOT to Use sql-flex-query

This library isn't for every situation. Avoid it when:

You only use one database dialect → Just write native SQL
You need full ORM features → Use Prisma, TypeORM, Sequelize
You need migrations → Use Knex or a migration tool
Your queries are extremely complex (window functions, CTEs, recursive queries) → May need manual SQL
You need query caching → Implement at application level

The Bottom Line

If your Node.js application:

Supports multiple databases (or might in the future)
Has complex filtering, sorting, and pagination
Values TypeScript type safety
Wants to reduce code duplication
Needs to maintain existing SQL templates

Then sql-flex-query is worth trying.

One query. Seven databases. Zero dialect headaches.

Next Steps

Install it: npm install sql-flex-query
Try the demo: Check out the GitHub repository for more examples
Read the docs: The README has 15+ detailed examples
Star it on GitHub: If it saves you time, give it a ⭐️

Questions? Open an issue on GitHub. I'm actively maintaining this library and welcome feedback.

URL: https://dzone.com/articles/unified-sql-query-builder-node

⇱ A Unified Query Builder for Node.js

Trending

Stop Writing Dialect-Specific SQL: A Unified Query Builder for Node.js

Learn how sql-flex-query lets you write once, run anywhere — with full TypeScript support and zero runtime overhead. Support SQL queries for multiple databases

The Problem Most Backend Developers Face

What if You Could Write Once, Run Anywhere?

Why This Matters in Production

1. Code Maintainability

2. Database Flexibility

3. Type Safety With TypeScript

Key Features That Make It Production-Ready

1. Dynamic WHERE Clauses With Automatic Grouping

2. Dialect-Aware Placeholders

3. Pagination That Just Works

4. Column Mapping for Clean Code

5. GROUP BY and HAVING Support

6. Fluent API for Complex Queries

Real-World Example: E-Commerce Product Search

Comparison With Alternatives

Knex.js

Raw SQL With Manual Placeholders

ORMs (Prisma, TypeORM, Sequelize)

Getting Started in 5 Minutes

Installation

Basic Usage

Supported Databases

Advanced Patterns

1. Text Search With OR Conditions

2. IN Operations

3. NULL and NOT NULL

4. Complex JOINs With Column Mapping

Testing Strategy

Performance Considerations

When NOT to Use sql-flex-query

The Bottom Line

Next Steps

Further Reading

Related

Partner Resources