tessl/npm-sql

SQL query builder for Node.js supporting multiple dialects with fluent, chainable API

—

Pending

Overview

Eval results

Files

Dialect Support

Name: tessl/npm-sql
Author: tessl

This document covers multi-database compatibility with dialect-specific features, optimizations, and configuration options for PostgreSQL, MySQL, Microsoft SQL Server, Oracle, and SQLite.

Supported Dialects

The SQL builder supports five major database dialects with automatic query generation optimization for each.

type SQLDialect = 'postgres' | 'mysql' | 'mssql' | 'oracle' | 'sqlite';

// Default dialect (PostgreSQL)
const DEFAULT_DIALECT = 'postgres';

// Set global dialect
sql.setDialect(dialect: SQLDialect, config?: object): void;

// Create dialect-specific instances
sql.create(dialect?: SQLDialect, config?: object): Sql;

// Get dialect implementation
function getDialect(dialectName: string): DialectImplementation;

Dialect Configuration

Global Dialect Setting

// Set dialect globally (affects default sql instance)
sql.setDialect('mysql');

// All subsequent queries use MySQL syntax
const query = user.select().toQuery();
console.log(query.text); // Uses MySQL quotation marks: `user`.*

Instance-Specific Dialects

// Create separate instances for different databases
const pgSql = sql.create('postgres');
const mysqlSql = sql.create('mysql');
const mssqlSql = sql.create('mssql');

// Each instance generates appropriate SQL
const pgQuery = pgSql.define({ name: 'user', columns: ['id'] }).select().toQuery();
const mysqlQuery = mysqlSql.define({ name: 'user', columns: ['id'] }).select().toQuery();

console.log(pgQuery.text);    // SELECT "user".* FROM "user"
console.log(mysqlQuery.text); // SELECT `user`.* FROM `user`

PostgreSQL (postgres)

PostgreSQL is the default dialect with the richest feature set support.

PostgreSQL-Specific Features

// Advanced PostgreSQL features
query.distinctOn(...columns: any[]): Query;        // DISTINCT ON
query.returning(...columns: any[]): Query;         // RETURNING clause
query.onConflict(action: ConflictAction): Query;    // ON CONFLICT (UPSERT)

// JSON operators
column.key(key: string): BinaryExpression;          // -> operator
column.keyText(key: string): BinaryExpression;      // ->> operator
column.path(path: string[]): BinaryExpression;      // #> operator
column.pathText(path: string[]): BinaryExpression;  // #>> operator

// Array operators
column.contains(value: any): BinaryExpression;      // @> operator
column.containedBy(value: any): BinaryExpression;   // <@ operator
column.overlap(value: any): BinaryExpression;       // && operator

// Text search
column.match(query: string): BinaryExpression;      // @@ operator

// Case-insensitive operations
column.ilike(pattern: string): BinaryExpression;    // ILIKE
column.iregex(pattern: string): BinaryExpression;   // ~* operator

Usage examples:

sql.setDialect('postgres');

// DISTINCT ON (PostgreSQL specific)
const distinctOnQuery = user
  .select(user.id, user.name, user.email)
  .distinctOn(user.email)
  .order(user.email, user.id)
  .toQuery();

// RETURNING clause
const insertReturningQuery = user
  .insert({ name: 'John', email: 'john@example.com' })
  .returning(user.id, user.created_at)
  .toQuery();

// ON CONFLICT (UPSERT)
const upsertQuery = user
  .insert({ email: 'john@example.com', name: 'John Doe' })
  .onConflict({
    target: 'email',
    action: { name: 'John Updated' }
  })
  .toQuery();

// JSON operations
const jsonQuery = user
  .select(
    user.id,
    user.metadata.key('preferences').as('user_prefs'),
    user.settings.path(['ui', 'theme']).as('theme')
  )
  .where(user.metadata.containsKey('preferences'))
  .toQuery();

// Array operations  
const arrayQuery = post
  .select()
  .where(post.tags.contains(['javascript', 'nodejs']))
  .toQuery();

// Full-text search
const textSearchQuery = document
  .select()
  .where(document.search_vector.match('search & terms'))
  .toQuery();

MySQL (mysql)

MySQL dialect with MySQL-specific syntax and limitations.

MySQL-Specific Features

// MySQL features
query.onDuplicate(action: object): Query;           // ON DUPLICATE KEY UPDATE
column.regexp(pattern: string): BinaryExpression;   // REGEXP operator
column.rlike(pattern: string): BinaryExpression;    // RLIKE operator

// MySQL quoting uses backticks
// Identifiers quoted with `identifier`
// Parameter placeholders use ? instead of $1, $2, etc.

Usage examples:

sql.setDialect('mysql');

// MySQL uses backticks for identifiers
const mysqlQuery = user.select().toQuery();
console.log(mysqlQuery.text); // SELECT `user`.* FROM `user`

// ON DUPLICATE KEY UPDATE
const duplicateKeyQuery = user
  .insert({ id: 1, name: 'John', email: 'john@example.com' })
  .onDuplicate({ 
    name: 'John Updated',
    updated_at: new Date()
  })
  .toQuery();

// REGEXP operator
const regexpQuery = user
  .select()
  .where(user.email.regexp('[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}'))
  .toQuery();

// MySQL parameter style (uses ? placeholders)
const paramQuery = user
  .select()
  .where(user.name.equals('John'))
  .toQuery();
console.log(paramQuery.text);   // SELECT `user`.* FROM `user` WHERE (`user`.`name` = ?)
console.log(paramQuery.values); // ['John']

MySQL Functions

// MySQL-specific functions
const mysqlFunctions = {
  CONCAT: sql.function('CONCAT'),
  DATE_FORMAT: sql.function('DATE_FORMAT'),
  SUBSTRING_INDEX: sql.function('SUBSTRING_INDEX'),
  GROUP_CONCAT: sql.function('GROUP_CONCAT'),
  IFNULL: sql.function('IFNULL')
};

const mysqlSpecificQuery = user
  .select(
    user.id,
    mysqlFunctions.CONCAT(user.first_name, ' ', user.last_name).as('full_name'),
    mysqlFunctions.DATE_FORMAT(user.created_at, '%Y-%m-%d').as('formatted_date'),
    mysqlFunctions.IFNULL(user.nick_name, user.first_name).as('display_name')
  )
  .toQuery();

Microsoft SQL Server (mssql)

SQL Server dialect with T-SQL specific features.

MSSQL-Specific Features

// SQL Server features
// Uses square brackets for identifier quoting: [identifier]
// Parameter placeholders use @p1, @p2, etc.
// Supports TOP clause instead of LIMIT
// Temporary tables prefixed with #

Usage examples:

sql.setDialect('mssql');

// SQL Server uses square brackets
const mssqlQuery = user.select().toQuery();
console.log(mssqlQuery.text); // SELECT [user].* FROM [user]

// SQL Server parameter style
const mssqlParamQuery = user
  .select()
  .where(user.name.equals('John'))
  .toQuery();
console.log(mssqlParamQuery.text); // Uses @p1, @p2, etc.

// Temporary tables
const tempTable = sql.define({
  name: 'temp_users',
  isTemporary: true,
  columns: ['id', 'name']
});

const tempTableQuery = tempTable.create().toQuery();
// Creates table with # prefix: #temp_users

SQL Server Functions

// SQL Server specific functions
const mssqlFunctions = {
  ISNULL: sql.function('ISNULL'),
  DATEPART: sql.function('DATEPART'),
  CHARINDEX: sql.function('CHARINDEX'),
  LEN: sql.function('LEN'),
  GETDATE: sql.function('GETDATE')
};

const mssqlSpecificQuery = user
  .select(
    user.id,
    mssqlFunctions.ISNULL(user.middle_name, '').as('middle_name'),
    mssqlFunctions.DATEPART('year', user.created_at).as('year_created'),
    mssqlFunctions.LEN(user.name).as('name_length')
  )
  .toQuery();

Oracle (oracle)

Oracle Database dialect with Oracle SQL specific syntax.

Oracle-Specific Features

// Oracle features
// Uses double quotes for identifier quoting: "identifier"
// Parameter placeholders use :1, :2, etc.
// Supports Oracle-specific functions and syntax

Usage examples:

sql.setDialect('oracle');

// Oracle parameter style
const oracleQuery = user
  .select()
  .where(user.name.equals('John'))
  .toQuery();
console.log(oracleQuery.text); // Uses :1, :2, etc. parameters

// Oracle ROWNUM for limiting (older versions)
const limitQuery = user
  .select()
  .where('ROWNUM <= 10')
  .toQuery();

Oracle Functions

// Oracle-specific functions
const oracleFunctions = {
  NVL: sql.function('NVL'),
  NVL2: sql.function('NVL2'),
  DECODE: sql.function('DECODE'),
  EXTRACT: sql.function('EXTRACT'),
  TO_CHAR: sql.function('TO_CHAR'),
  TO_DATE: sql.function('TO_DATE'),
  SYSDATE: sql.function('SYSDATE')
};

const oracleSpecificQuery = user
  .select(
    user.id,
    oracleFunctions.NVL(user.nick_name, user.first_name).as('display_name'),
    oracleFunctions.TO_CHAR(user.created_at, 'YYYY-MM-DD').as('formatted_date'),
    oracleFunctions.EXTRACT('YEAR FROM', user.created_at).as('year_created')
  )
  .toQuery();

SQLite (sqlite)

SQLite dialect optimized for the lightweight database engine.

SQLite-Specific Features

// SQLite features
// Uses double quotes for identifier quoting: "identifier"
// Parameter placeholders use ?, ?2, etc.
// Limited function support compared to other databases
// No complex joins in some operations

Usage examples:

sql.setDialect('sqlite');

// SQLite parameter style
const sqliteQuery = user
  .select()
  .where(user.name.equals('John'))
  .toQuery();
console.log(sqliteQuery.text); // Uses ? parameters

// SQLite date functions
const dateQuery = user
  .select(
    user.id,
    user.name,
    sql.function('DATE')(user.created_at).as('created_date'),
    sql.function('STRFTIME')('%Y', user.created_at).as('created_year')
  )
  .toQuery();

SQLite Functions

// SQLite-specific functions
const sqliteFunctions = {
  DATE: sql.function('DATE'),
  TIME: sql.function('TIME'),
  DATETIME: sql.function('DATETIME'),
  STRFTIME: sql.function('STRFTIME'),
  JULIANDAY: sql.function('JULIANDAY'),
  IFNULL: sql.function('IFNULL')
};

const sqliteSpecificQuery = user
  .select(
    user.id,
    sqliteFunctions.DATE(user.created_at).as('created_date'),
    sqliteFunctions.STRFTIME('%Y-%m', user.created_at).as('year_month'),
    sqliteFunctions.IFNULL(user.nick_name, user.first_name).as('display_name')
  )
  .toQuery();

Cross-Dialect Compatibility

Writing Portable Queries

// Use standard SQL features for portability
function createPortableQuery(sqlInstance) {
  return user
    .select(
      user.id,
      user.name,
      user.email
    )
    .where(
      user.active.equals(true)
        .and(user.created_at.gt(new Date('2023-01-01')))
    )
    .order(user.name.asc)
    .limit(10)
    .toQuery();
}

// Works across all dialects
const pgQuery = createPortableQuery(sql.create('postgres'));
const mysqlQuery = createPortableQuery(sql.create('mysql'));
const sqliteQuery = createPortableQuery(sql.create('sqlite'));

Dialect-Specific Adaptations

function createAdaptedQuery(sqlInstance) {
  const baseQuery = user.select(user.id, user.name, user.email);
  
  // Add dialect-specific features
  switch (sqlInstance.dialectName) {
    case 'postgres':
      return baseQuery
        .distinctOn(user.email)
        .order(user.email, user.id)
        .toQuery();
        
    case 'mysql':
      return baseQuery
        .where(user.email.regexp('[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}'))
        .toQuery();
        
    case 'mssql':
      return baseQuery
        .where(sql.function('LEN')(user.name).gt(2))
        .toQuery();
        
    default:
      return baseQuery.toQuery();
  }
}

Dialect Configuration Options

Custom Dialect Configuration

interface DialectConfig {
  // Custom configuration options per dialect
  [key: string]: any;
}

// Configure dialect with options
sql.setDialect('postgres', {
  // Custom PostgreSQL configuration
  searchPath: ['public', 'custom_schema'],
  timeZone: 'UTC'
});

sql.setDialect('mysql', {
  // Custom MySQL configuration
  charset: 'utf8mb4',
  timezone: '+00:00'
});

Feature Comparison Matrix

Feature	PostgreSQL	MySQL	SQL Server	Oracle	SQLite
DISTINCT ON	✓	✗	✗	✗	✗
RETURNING	✓	✗	✓	✓	✗
ON CONFLICT	✓	✗	✗	✗	✓
ON DUPLICATE KEY	✗	✓	✗	✗	✗
JSON Operators	✓	✓	✓	✓	✓
Array Types	✓	✗	✗	✓	✗
Full-text Search	✓	✓	✓	✓	✓
Window Functions	✓	✓	✓	✓	✓
CTEs	✓	✓	✓	✓	✓
LIMIT/OFFSET	✓	✓	✓	✓	✓

Best Practices

Dialect Selection

// Choose dialect based on your database
const dbDialect = process.env.DB_TYPE || 'postgres';
sql.setDialect(dbDialect);

// Or create specific instances
const dbInstances = {
  postgres: sql.create('postgres'),
  mysql: sql.create('mysql'),
  mssql: sql.create('mssql'),
  oracle: sql.create('oracle'),
  sqlite: sql.create('sqlite')
};

const currentSql = dbInstances[process.env.DB_TYPE];

Error Handling

// Handle dialect-specific errors
try {
  sql.setDialect('unknown_dialect');
} catch (error) {
  console.error('Unsupported dialect:', error.message);
  // Fallback to default
  sql.setDialect('postgres');
}

Testing Across Dialects

// Test queries across multiple dialects
const dialects = ['postgres', 'mysql', 'sqlite'];
const testResults = {};

dialects.forEach(dialect => {
  const testSql = sql.create(dialect);
  const query = user.select().where(user.active.equals(true)).toQuery();
  testResults[dialect] = {
    text: query.text,
    values: query.values
  };
});

console.log('Cross-dialect test results:', testResults);

Install with Tessl CLI

npx tessl i tessl/npm-sql

docs

tessl/npm-sql

dialect-support.md.css-3qkkll{font-size:var(--chakra-font-sizes-sm);font-weight:var(--chakra-font-weights-normal);color:var(--chakra-colors-gray-300);}docs/

Dialect Support

Supported Dialects

Dialect Configuration

Global Dialect Setting

Instance-Specific Dialects

PostgreSQL (postgres)

PostgreSQL-Specific Features

MySQL (mysql)

MySQL-Specific Features

MySQL Functions

Microsoft SQL Server (mssql)

MSSQL-Specific Features

SQL Server Functions

Oracle (oracle)

Oracle-Specific Features

Oracle Functions

SQLite (sqlite)

SQLite-Specific Features

SQLite Functions

Cross-Dialect Compatibility

Writing Portable Queries

Dialect-Specific Adaptations

Dialect Configuration Options

Custom Dialect Configuration

Feature Comparison Matrix

Best Practices

Dialect Selection

Error Handling

Testing Across Dialects

dialect-support.mddocs/