sqlslice

sqlslice

Composable SQL architecture framework for breaking large relational queries, stored procedures, and materialized views into reusable, maintainable slices.

Installation

npm install sqlslice

Concepts

Term	Description
Slice	A named, reusable SQL unit (query, CTE fragment, view reference, or procedure call).
SliceRegistry	An in-memory store of registered slices.
SqlSliceEngine	Ties a DatabaseConnector to a SliceRegistry; runs and composes slices.
DatabaseConnector	Abstract base class — extend it to add a new database driver.

Quick start

import {
  PostgresConnector,
  SqlSliceEngine,
  Slice,
} from 'sqlslice';

const connector = new PostgresConnector({
  host: 'localhost',
  port: 5432,
  database: 'mydb',
  user: 'admin',
  password: 'secret',
});

const engine = new SqlSliceEngine(connector);
await engine.connect();

// 1. Define slices
const activeUsers = new Slice({
  name: 'active_users',
  type: 'fragment',
  description: 'Users who logged in within the last 30 days',
  sql: `SELECT id, name, email FROM users WHERE last_login >= NOW() - INTERVAL '30 days'`,
});

const premiumUsers = new Slice({
  name: 'premium_users',
  type: 'fragment',
  sql: `SELECT user_id FROM subscriptions WHERE plan = 'premium' AND status = 'active'`,
});

// 2. Register
engine.register(activeUsers).register(premiumUsers);

// 3. Run a single slice
const { rows } = await engine.run('active_users');

// 4. Compose slices as CTEs + a final SELECT
const result = await engine.compose(
  ['active_users', 'premium_users'],
  `SELECT au.* FROM active_users au
   JOIN premium_users pu ON pu.user_id = au.id`,
);
// Emits:
//   WITH active_users AS (...),
//        premium_users AS (...)
//   SELECT au.* FROM active_users au JOIN premium_users pu ON ...

await engine.disconnect();

Dynamic SQL with template params

Pass a function as sql to build the query at runtime:

const recentOrders = new Slice<{ days: number }>({
  name: 'recent_orders',
  type: 'query',
  sql: ({ days }) =>
    `SELECT * FROM orders WHERE created_at >= NOW() - INTERVAL '${days} days'`,
});

engine.register(recentOrders);

const { rows } = await engine.run('recent_orders', { days: 7 });

Positional bind parameters

Use $1, $2, … placeholders and pass bindParams for safe parameterized queries:

const ordersByStatus = new Slice({
  name: 'orders_by_status',
  type: 'query',
  sql: `SELECT * FROM orders WHERE status = $1`,
});

engine.register(ordersByStatus);

const { rows } = await engine.run(
  'orders_by_status',
  undefined,
  ['shipped'],       // bindParams → $1 = 'shipped'
);

OOP connector hierarchy

DatabaseConnector  (abstract)
└── PostgresConnector

To add a new database, extend DatabaseConnector:

import { DatabaseConnector, QueryResult } from 'sqlslice';

export class MySQLConnector extends DatabaseConnector {
  get databaseType() { return 'mysql'; }
  get isConnected() { /* ... */ }

  async connect() { /* ... */ }
  async disconnect() { /* ... */ }
  async query<T>(sql: string, params?: unknown[]): Promise<QueryResult<T>> { /* ... */ }
  async execute(sql: string, params?: unknown[]): Promise<void> { /* ... */ }
}

Then pass it straight into SqlSliceEngine — no other changes needed.

Slice types

type	Use for
'query'	Full SELECT statements
'fragment'	Sub-queries / CTE bodies
'view'	References to materialized / regular views
'procedure'	Stored procedure calls

API reference

new PostgresConnector(config: PostgresConfig)

Option	Type	Default
host	string	—
port	number	5432
database	string	—
user	string	—
password	string	—
ssl	boolean | object	—
poolSize	number	10
idleTimeoutMillis	number	30000
connectionTimeoutMillis	number	2000

SqlSliceEngine

Method	Description
.connect()	Open the underlying connection pool. Returns this (chainable).
.disconnect()	Close the pool.
.register(slice)	Add a slice to the registry. Returns this (chainable).
.run(name, templateParams?, bindParams?)	Execute a single slice.
.compose(names, finalSelect, options?)	Build a WITH … AS (…) query and execute it.
.getRegistry()	Access the SliceRegistry for introspection.

License

MIT