pipehood

Minimal, lightweight ORM for PostgreSQL with dependency injection, fluent API, and TypeScript support.

A production-ready query builder designed with SOLID principles: clean architecture, decoupled components, and extensibility through dependency injection.

Features

✨ Clean Architecture - Decoupled components using dependency injection
🔄 Fluent API - Chainable methods for elegant query building
🛡️ Type-Safe - Full TypeScript support with strict types
⚡ Async/Parallel - Native Promise support with parallel query execution
🔌 Extensible - Implement custom compilers and executors
📊 Parameterized Queries - Protection against SQL injection
🎯 Fail-First - Immediate validation with clear error messages

Installation

npm install pipehood

Quick Start

import { QueryBuilderFactory, PostgresCompiler, SupabaseExecutor } from 'own-orm';
import postgres from 'postgres';

// Initialize database connection
const db = postgres({
  host: process.env.DB_HOST,
  port: parseInt(process.env.DB_PORT || '5432'),
  database: process.env.DB_NAME,
  username: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
});

// Create factory with dependencies
const compiler = new PostgresCompiler();
const executor = new SupabaseExecutor(db);
const factory = new QueryBuilderFactory(compiler, executor);

// Build and execute queries
const result = await factory.create()
  .table('usuarios')
  .select(['id', 'nombre', 'email'])
  .whereEq('activo', true)
  .orderBy('nombre', 'ASC')
  .limit(10)
  .execute();

console.log(result);

Usage Examples

Basic Queries

const qb = factory.create();

// SELECT
const usuarios = await qb.table('usuarios')
  .select(['id', 'nombre', 'email'])
  .execute();

// WHERE with equality
const activos = await qb.table('usuarios')
  .select(['*'])
  .whereEq('estado', 'Activo')
  .execute();

// WHERE with IN clause
const pedidos = await qb.table('pedidos')
  .select(['*'])
  .whereIn('estado', ['Pendiente', 'En tránsito'])
  .execute();

// Raw WHERE clause
const custom = await qb.table('pedidos')
  .select(['*'])
  .whereRaw('monto > ? AND monto < ?', [50, 500])
  .execute();

// Ordering and Limiting
const paginated = await qb.table('usuarios')
  .select(['*'])
  .orderBy('fecha_creacion', 'DESC')
  .limit(10)
  .offset(0)
  .execute();

Parallel Queries

Execute multiple queries concurrently for better performance:

// Run 3 queries in parallel instead of sequentially
const [usuarios, pedidos, clientes] = await Promise.all([
  factory.create()
    .table('usuarios')
    .select(['*'])
    .execute(),
  factory.create()
    .table('pedidos')
    .select(['*'])
    .execute(),
  factory.create()
    .table('clientes')
    .select(['*'])
    .execute(),
]);

// Results are typically 20-30% faster than sequential

Query Cloning

Create query variants without mutations:

const baseQuery = factory.create()
  .table('pedidos')
  .select(['*'])
  .whereEq('estado', 'Pendiente');

// Create variations safely
const query1 = baseQuery.clone().orderBy('fecha', 'ASC').limit(10);
const query2 = baseQuery.clone().orderBy('monto', 'DESC').limit(5);

// Both execute independently
const [result1, result2] = await Promise.all([
  query1.execute(),
  query2.execute(),
]);

CRUD Operations

Complete Create, Read, Update, Delete support:

// CREATE (INSERT)
const newUser = await factory.create()
  .table('usuarios')
  .insert({ nombre: 'Juan', email: '[email protected]', activo: true })
  .execute();

// READ (SELECT) - covered above

// UPDATE
const updated = await factory.create()
  .table('usuarios')
  .update({ nombre: 'María', activo: false })
  .whereEq('id', 1)
  .execute();

// DELETE (requires WHERE for safety)
const deleted = await factory.create()
  .table('usuarios')
  .whereEq('id', 1)
  .delete()
  .execute();

JOINs (v1.2.0)

Support for INNER, LEFT, RIGHT, FULL, and CROSS JOINs:

// INNER JOIN
const usersWithOrders = await factory.create()
  .table('usuarios u')
  .select(['u.id', 'u.nombre', 'o.id_pedido', 'o.monto'])
  .innerJoin('pedidos o', 'u.id = o.id_usuario')
  .execute();

// LEFT JOIN - all users even without orders
const allUsers = await factory.create()
  .table('usuarios u')
  .select(['u.id', 'u.nombre', 'COUNT(o.id_pedido) as total_pedidos'])
  .leftJoin('pedidos o', 'u.id = o.id_usuario')
  .groupBy('u.id', 'u.nombre')
  .execute();

// Multiple JOINs
const complex = await factory.create()
  .table('pedidos p')
  .select(['p.id_pedido', 'u.nombre as usuario', 'c.nombre as cliente'])
  .innerJoin('usuarios u', 'p.id_usuario = u.id')
  .leftJoin('clientes c', 'p.id_cliente = c.id')
  .whereEq('p.estado', 'completado')
  .execute();

GROUP BY & HAVING (v1.2.0)

Aggregate data and filter on group conditions:

// GROUP BY with aggregation
const stats = await factory.create()
  .table('pedidos')
  .select(['estado', 'COUNT(*) as cantidad', 'SUM(monto_total) as total'])
  .groupBy('estado')
  .orderBy('total', 'desc')
  .execute();

// GROUP BY with HAVING filter
const highVolume = await factory.create()
  .table('pedidos')
  .select(['id_usuario', 'COUNT(*) as num_pedidos', 'SUM(monto_total) as total'])
  .groupBy('id_usuario')
  .havingRaw('COUNT(*) > ?', 5)
  .orderBy('num_pedidos', 'desc')
  .execute();

API Reference

QueryBuilder Methods

table(name: string): this

Set the table to query from.

qb.table('usuarios')

select(columns: string[]): this

Select specific columns. Use ['*'] for all columns.

qb.select(['id', 'nombre', 'email'])
qb.select(['*'])  // Select all

whereEq(column: string, value: unknown): this

Add an equality condition.

qb.whereEq('estado', 'Activo')
qb.whereEq('edad', 25)

whereIn(column: string, values: unknown[]): this

Add an IN condition for multiple values.

qb.whereIn('estado', ['Activo', 'Pendiente'])

whereRaw(sql: string, params: unknown[]): this

Add a raw SQL condition with parameters.

qb.whereRaw('edad > ? AND edad < ?', [18, 65])

orderBy(column: string, direction: 'ASC' | 'DESC'): this

Add ordering.

qb.orderBy('nombre', 'ASC')
qb.orderBy('fecha_creacion', 'DESC')

limit(n: number): this

Set result limit.

qb.limit(10)

offset(n: number): this

Set result offset for pagination.

qb.offset(20)  // Skip first 20 rows

clone(): QueryBuilder

Create an independent copy of the query builder.

const query2 = query1.clone().limit(5)

insert(values: Record<string, unknown>): this

Insert a new record. Returns the query builder for chaining.

qb.insert({ nombre: 'Juan', email: '[email protected]', activo: true })

update(values: Record<string, unknown>): this

Update records. Must be combined with where().

qb.update({ nombre: 'María', activo: false }).whereEq('id', 1)

delete(): this

Delete records. Requires where() clause for safety.

qb.whereEq('id', 1).delete()

innerJoin(table: string, on: string): this (v1.2.0)

Add an INNER JOIN clause.

qb.innerJoin('pedidos p', 'usuarios.id = p.id_usuario')

leftJoin(table: string, on: string): this (v1.2.0)

Add a LEFT JOIN clause.

qb.leftJoin('pedidos p', 'usuarios.id = p.id_usuario')

rightJoin(table: string, on: string): this (v1.2.0)

Add a RIGHT JOIN clause.

qb.rightJoin('pedidos p', 'usuarios.id = p.id_usuario')

fullJoin(table: string, on: string): this (v1.2.0)

Add a FULL JOIN clause.

qb.fullJoin('pedidos p', 'usuarios.id = p.id_usuario')

crossJoin(table: string): this (v1.2.0)

Add a CROSS JOIN clause (Cartesian product).

qb.crossJoin('clientes')

groupBy(...columns: string[]): this (v1.2.0)

Add GROUP BY clause for aggregation.

qb.groupBy('estado', 'tipo')

havingEq(column: string, value: unknown): this (v1.2.0)

Add a HAVING equality condition for filtered aggregations.

qb.havingEq('COUNT(*)', 5)

havingRaw(sql: string, ...params: unknown[]): this (v1.2.0)

Add a raw HAVING condition with parameters.

qb.havingRaw('SUM(monto) > ?', 1000)

execute(): Promise<T[]>

Execute the query and return results.

const results = await qb.execute()

Architecture

Component Design

QueryBuilderFactory
    ├── ICompiler (PostgresCompiler)
    │   └── Converts QueryState → SQL string
    └── IExecutor (SupabaseExecutor)
        └── Executes compiled SQL → Results

Dependency Injection

Components are loosely coupled through interfaces:

// QueryBuilder depends on abstractions, not concrete implementations
class QueryBuilder {
  constructor(
    private compiler: ICompiler,
    private executor: IExecutor
  ) {}
}

// Easy to swap implementations
const customExecutor = new LoggingExecutor(new SupabaseExecutor(db));
const factory = new QueryBuilderFactory(compiler, customExecutor);

SOLID Principles

• Single Responsibility: Each class has one reason to change
• Open/Closed: Extend functionality without modifying existing code
• Liskov Substitution: Custom executors implement IExecutor interface
• Interface Segregation: Focused, minimal interfaces
• Dependency Inversion: Depend on abstractions, not concretions

Custom Executors

Implement your own executor for logging, caching, or custom logic:

import { IExecutor } from 'own-orm';

class LoggingExecutor implements IExecutor {
  constructor(private executor: IExecutor) {}

  async execute(sql: string, params: unknown[]): Promise<unknown[]> {
    console.log('SQL:', sql);
    console.log('Params:', params);
    const result = await this.executor.execute(sql, params);
    console.log('Result:', result);
    return result;
  }
}

// Use it
const executor = new LoggingExecutor(new SupabaseExecutor(db));
const factory = new QueryBuilderFactory(compiler, executor);

Environment Setup

Create a .env file in your project root:

DB_HOST=aws-1-us-east-2.pooler.supabase.com
DB_PORT=5432
DB_NAME=postgres
DB_USER=postgres.your-project-id
DB_PASSWORD=your-secure-password

Then load it in your application:

import * as dotenv from 'dotenv';
dotenv.config();

Performance Tips

1. Use Parallel Queries: Execute independent queries with Promise.all()
2. Batch Operations: Group related queries together
3. Pagination: Use limit() and offset() for large result sets
4. Indexes: Ensure database has proper indexes on frequently queried columns
5. Connection Pooling: Supabase Session Pooler is recommended for serverless

Testing

Run included examples to verify functionality:

# Basic connection test
npm run verify

# Run examples
npm run example:connection
npm run example:async
npm run example:complete

Type Safety

All queries are fully typed:

interface User {
  id: number;
  nombre: string;
  email: string;
  activo: boolean;
}

const usuarios = await factory.create()
  .table('usuarios')
  .select(['id', 'nombre', 'email'])
  .execute() as User[];

// TypeScript knows about all properties
console.log(usuarios[0].nombre);

Requirements

• Node.js >= 18.0.0
• PostgreSQL >= 12
• TypeScript >= 5.0 (for development)

Contributing

1. Fork the repository
2. Create a feature branch (git checkout -b feature/amazing-feature)
3. Commit your changes (git commit -m 'Add amazing feature')
4. Push to the branch (git push origin feature/amazing-feature)
5. Open a Pull Request

License

MIT © 2024 Joshua Villegas

Support

• 📖 Full Documentation
• 🐛 Report Issues
• 💬 Discussions

Changelog

v1.0.0 (Initial Release)

• Clean architecture with dependency injection
• Fluent query builder API
• PostgreSQL compiler with parameterized queries
• Supabase executor with async support
• Comprehensive examples and documentation
• SOLID principles applied throughout