tessl/npm-sql

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

—

Pending

Overview

Eval results

Files

Table Operations

Name: tessl/npm-sql
Author: tessl

This document covers table definition, column management, and basic CRUD operations for database schema modeling and data manipulation.

Table Definition

Create table definitions with columns, constraints, and relationships.

function define(config: TableDefinition): Table;

interface TableDefinition {
  name: string;
  schema?: string;
  columns: (string | ColumnDefinition)[];
  foreignKeys?: ForeignKeyDefinition[];
  isTemporary?: boolean;
  snakeToCamel?: boolean;
  columnWhiteList?: boolean;
}

Basic Table Definition

const user = sql.define({
  name: 'user',
  columns: ['id', 'name', 'email', 'created_at']
});

Advanced Table Definition

const user = sql.define({
  name: 'user',
  schema: 'public',
  columns: [
    { name: 'id', dataType: 'integer', primaryKey: true },
    { name: 'name', dataType: 'varchar(255)', notNull: true },
    { name: 'email', dataType: 'varchar(255)', unique: true },
    { 
      name: 'state_or_province', 
      property: 'state',  // Access as user.state instead of user.state_or_province
      dataType: 'varchar(100)' 
    }
  ],
  foreignKeys: [{
    table: 'department',
    columns: ['dept_id'],
    refColumns: ['id'],
    onDelete: 'cascade'
  }],
  snakeToCamel: true  // Convert snake_case columns to camelCase properties
});

Table Class

The Table class provides methods for schema operations and query building.

class Table {
  // Properties
  getName(): string;
  getSchema(): string;
  setSchema(schema: string): void;
  columns: Column[];
  foreignKeys: ForeignKey[];
  
  // Column management
  addColumn(column: string | ColumnDefinition, options?: AddColumnOptions): Table;
  hasColumn(column: string | Column): boolean;
  getColumn(name: string): Column;
  get(name: string): Column;  // Alias for getColumn
  createColumn(columnDef: string | ColumnDefinition): Column;
  
  // Query operations
  select(...columns: any[]): Query;
  insert(data: object | object[]): Query;
  replace(data: object | object[]): Query;
  update(data: object): Query;
  delete(conditions?: any): Query;
  
  // DDL operations
  create(): CreateQuery;
  drop(): DropQuery;
  alter(): AlterQuery;
  truncate(): Query;
  
  // Utilities
  star(options?: StarOptions): Column;
  literal(statement: string): LiteralNode;
  count(alias?: string): Column;
  as(alias: string): Table;
  clone(config?: Partial<TableDefinition>): Table;
  subQuery(alias?: string): SubQuery;
  toNode(): TableNode;
  
  // Joins
  join(other: Table): JoinNode;
  leftJoin(other: Table): JoinNode;
  joinTo(other: Table): JoinNode;  // Auto-join based on foreign keys
  
  // Index management
  indexes(): IndexQuery;
}

interface AddColumnOptions {
  noisy?: boolean;  // Default true, throws error if column exists
}

interface StarOptions {
  prefix?: string;  // Prefix column names
}

Column Definition

Define columns with data types, constraints, and references.

interface ColumnDefinition {
  name: string;
  property?: string;           // Property name for access (defaults to name)
  dataType?: string;          // SQL data type
  primaryKey?: boolean;       // Primary key constraint
  notNull?: boolean;          // NOT NULL constraint
  unique?: boolean;           // UNIQUE constraint
  defaultValue?: any;         // Default value
  autoGenerated?: boolean;    // Skip in INSERT/UPDATE operations
  references?: {              // Foreign key reference
    table: string;
    column: string;
    onDelete?: ReferentialAction;
    onUpdate?: ReferentialAction;
  };
  subfields?: string[];       // For complex types (arrays/objects)
}

type ReferentialAction = 'restrict' | 'cascade' | 'no action' | 'set null' | 'set default';

CRUD Operations

INSERT Operations

// Insert single record
table.insert(data: object): Query;

// Insert multiple records
table.insert(data: object[]): Query;

// Insert with specific columns
table.insert(column1.value(val1), column2.value(val2)): Query;

Usage examples:

// Insert single user
const insertQuery = user.insert({
  name: 'John Doe',
  email: 'john@example.com'
}).toQuery();

// Insert multiple users
const multiInsertQuery = user.insert([
  { name: 'Alice', email: 'alice@example.com' },
  { name: 'Bob', email: 'bob@example.com' }
]).toQuery();

// Insert with explicit columns
const explicitInsertQuery = user.insert(
  user.name.value('Charlie'),
  user.email.value('charlie@example.com')
).toQuery();

UPDATE Operations

table.update(data: object): Query;

Usage examples:

// Update with WHERE clause
const updateQuery = user
  .update({ name: 'John Smith' })
  .where(user.id.equals(1))
  .toQuery();

// Update multiple columns
const multiUpdateQuery = user
  .update({
    name: 'Jane Doe',
    email: 'jane.doe@example.com'
  })
  .where(user.id.equals(2))
  .toQuery();

DELETE Operations

// Delete with conditions
table.delete(conditions?: any): Query;

// Delete specific tables (for joins)
table.delete(tables: Table | Table[]): Query;

Usage examples:

// Delete with WHERE clause
const deleteQuery = user
  .delete()
  .where(user.id.equals(1))
  .toQuery();

// Delete with conditions shorthand
const shorthandDeleteQuery = user
  .delete(user.inactive.equals(true))
  .toQuery();

// Multi-table delete
const multiDeleteQuery = user
  .delete([user, profile])
  .from(user.join(profile).on(user.id.equals(profile.userId)))
  .where(user.active.equals(false))
  .toQuery();

SELECT Operations

// Select all columns
table.select(): Query;

// Select specific columns
table.select(...columns: any[]): Query;

// Select with star
table.select(table.star()): Query;

Usage examples:

// Select all
const allQuery = user.select().toQuery();

// Select specific columns
const specificQuery = user.select(user.id, user.name).toQuery();

// Select with alias
const aliasQuery = user.select(
  user.id, 
  user.name.as('full_name')
).toQuery();

DDL Operations

CREATE TABLE

interface CreateQuery {
  ifNotExists(): CreateQuery;
  toQuery(): QueryResult;
}

Usage:

// Create table
const createQuery = user.create().toQuery();

// Create table if not exists
const createIfNotExistsQuery = user.create().ifNotExists().toQuery();

DROP TABLE

interface DropQuery {
  ifExists(): DropQuery;
  cascade(): DropQuery;
  restrict(): DropQuery;
  toQuery(): QueryResult;
}

Usage:

// Drop table
const dropQuery = user.drop().toQuery();

// Drop table if exists
const dropIfExistsQuery = user.drop().ifExists().toQuery();

// Drop with cascade
const dropCascadeQuery = user.drop().cascade().toQuery();

ALTER TABLE

interface AlterQuery {
  addColumn(column: Column | string, options?: string): AlterQuery;
  dropColumn(column: Column | string): AlterQuery;
  renameColumn(oldColumn: Column | string, newColumn: Column | string): AlterQuery;
  rename(newName: string): AlterQuery;
  toQuery(): QueryResult;
}

Usage:

// Add column
const addColumnQuery = user.alter()
  .addColumn('middle_name', 'VARCHAR(100)')
  .toQuery();

// Drop column  
const dropColumnQuery = user.alter()
  .dropColumn('middle_name')
  .toQuery();

// Rename column
const renameColumnQuery = user.alter()
  .renameColumn('name', 'full_name')
  .toQuery();

// Rename table
const renameTableQuery = user.alter()
  .rename('users')
  .toQuery();

Index Management

interface IndexQuery {
  create(indexName?: string): IndexCreationQuery;
  drop(indexName: string): Query;
  drop(...columns: Column[]): Query;
}

interface IndexCreationQuery {
  unique(): IndexCreationQuery;
  using(indexType: string): IndexCreationQuery;
  on(...columns: (Column | OrderByValueNode)[]): IndexCreationQuery;
  withParser(parserName: string): IndexCreationQuery;
  fulltext(): IndexCreationQuery;
  spatial(): IndexCreationQuery;
  toQuery(): QueryResult;
}

Usage:

// Create index
const createIndexQuery = user.indexes()
  .create('idx_user_email')
  .on(user.email)
  .toQuery();

// Create unique index
const uniqueIndexQuery = user.indexes()
  .create('idx_user_email_unique')
  .unique()
  .on(user.email)
  .toQuery();

// Create composite index
const compositeIndexQuery = user.indexes()
  .create('idx_user_name_email')
  .on(user.name, user.email)
  .toQuery();

// Drop index
const dropIndexQuery = user.indexes()
  .drop('idx_user_email')
  .toQuery();

Foreign Keys

interface ForeignKeyDefinition {
  table: string;           // Referenced table name
  columns: string[];       // Local columns
  refColumns: string[];    // Referenced columns
  onDelete?: ReferentialAction;
  onUpdate?: ReferentialAction;
}

Usage:

const post = sql.define({
  name: 'post',
  columns: ['id', 'title', 'user_id'],
  foreignKeys: [{
    table: 'user',
    columns: ['user_id'],
    refColumns: ['id'],
    onDelete: 'cascade',
    onUpdate: 'restrict'
  }]
});

Table Relationships and Joins

Auto-Join Based on Foreign Keys

// Automatic join using foreign key relationships
const autoJoinQuery = user.joinTo(post).toQuery();

Manual Joins

// Inner join
const innerJoinQuery = user
  .select(user.name, post.title)
  .from(user.join(post).on(user.id.equals(post.user_id)))
  .toQuery();

// Left join
const leftJoinQuery = user
  .select(user.name, post.title)
  .from(user.leftJoin(post).on(user.id.equals(post.user_id)))
  .toQuery();

Install with Tessl CLI