0.4.6 • Published 1 month ago

pg-lightquery

Licence

ISC

Version

0.4.6

Deps

Size

411 kB

Vulns

Weekly

Stars

Summary Dependency Versions

pg-lightquery

A modern, type-safe PostgreSQL query builder for Node.js with TypeScript support.

Why Choose pg-lightquery?

Better Developer Experience

Query Inspection: See generated SQL and parameters before execution
Type Safety: Full TypeScript support with auto-completion
Zero Learning Curve: Intuitive API that mirrors your mental model
Chained Operations: Fluent CTE-based multi-table inserts and updates
Related Tables Registry: Simplified management of table relationships

Superior Architecture

Deferred Execution: Build queries separately, execute when ready
Composition Over Inheritance: Clean, testable code structure
Security First: Separate concerns for column validation and data projection
Enhanced TableBase: Built-in support for complex table relationships

Advanced Features

Smart Operators: Built-in support for LIKE, IN, date ranges, JSON queries
Complex Joins: Filter by joined/aggregated columns with full type safety
Transaction Builder: Fluent interface for multi-query transactions
Chained Insert/Update Builder: Type-safe CTE operations for complex multi-table operations
Optional Audit Fields: Automatic lastChangedBy tracking with configurable defaults
Minimal Dependencies: Only 4 core dependencies

Installation

npm install pg-lightquery

Quick Start

1. Setup Connection

import PostgresConnection from 'pg-lightquery';

PostgresConnection.initialize({
	host: 'localhost',
	port: 5432,
	user: 'your_user',
	password: 'your_password',
	database: 'your_database',
});

2. Define Your Schema

import {ColumnDefinition, TableDefinition} from 'pg-lightquery';

export const usersColumns = {
	id: {type: 'INTEGER', primaryKey: true, autoIncrement: true},
	name: {type: 'TEXT', notNull: true},
	email: {type: 'TEXT', unique: true},
	createdAt: {type: 'TIMESTAMP WITHOUT TIME ZONE', notNull: true, default: 'NOW()'},
	lastChangedBy: {type: 'TEXT', notNull: false}, // Optional audit field
} as const;

export type UsersSchema = {
	[K in keyof typeof usersColumns]: ColumnDefinition;
};

const usersTable: TableDefinition<UsersSchema> = {
	tableName: 'users',
	schema: {columns: usersColumns},
};

3. Create Your Table Class

import {TableBase, EnhancedTableBase} from 'pg-lightquery';

// Basic table class
class UsersTable extends TableBase<UsersSchema> {
	constructor() {
		super(usersTable);
	}

	// Type-safe insert with query inspection
	insertUser(userData: {name: string; email: string}) {
		return this.insert({
			allowedColumns: ['name', 'email'],
			options: {
				data: userData,
				returnField: 'id',
			},
		});
	}

	// Flexible select with smart operators
	selectUsers(filters?: {name?: string; email?: string}) {
		return this.select({
			allowedColumns: '*',
			options: {
				where: filters,
				columnsToReturn: ['id', 'name', 'email', 'createdAt'],
			},
		});
	}

	// Safe update with required WHERE clause
	updateUser(data: {name?: string; email?: string}, where: {id?: number; email?: string}) {
		return this.update({
			allowedColumns: ['name', 'email'],
			options: {
				data,
				where,
				returnField: 'id',
			},
		});
	}
}

// Enhanced table class with related tables support
class EnhancedUsersTable extends EnhancedTableBase<UsersSchema> {
	constructor() {
		super(usersTable);

		// Register related tables for chained operations
		this.registerRelatedTable('posts', {tableDefinition: postsTable});
		this.registerRelatedTable('addresses', {tableDefinition: addressesTable});
	}

	// Complex multi-table insert with CTE support
	createUserWithProfile(userData: {name: string; email: string}, includePost = false, includeAddress = false) {
		const postData = {title: 'Welcome Post', content: 'Welcome to our platform!'};
		const addressData = {street: '123 Default St', city: 'Default City'};

		return this.createChainedInsert()
			.insert('new_user', this.db, userData, {returnField: '*'})
			.insertWithReferenceIf(includePost, 'user_post', this.getRelatedTable('posts'), postData, {
				from: 'new_user',
				field: 'id',
				to: 'userId',
			})
			.insertWithReferenceIf(includeAddress, 'user_address', this.getRelatedTable('addresses'), addressData, {
				from: 'new_user',
				field: 'id',
				to: 'userId',
			})
			.selectFrom('new_user')
			.build();
	}
}

const users = new UsersTable();
const enhancedUsers = new EnhancedUsersTable();

4. Use It

// Query inspection before execution
const insertQuery = users.insertUser({name: 'John', email: 'john@example.com'});
console.log('SQL:', insertQuery.query.sqlText);
console.log('Values:', insertQuery.query.values);

// Execute when ready
const newUser = await insertQuery.execute();

// Or execute immediately
const allUsers = await users.selectUsers().execute();

// Update with WHERE clause (required for safety)
const updateQuery = users.updateUser({name: 'John Updated'}, {id: 1});
const updatedUser = await updateQuery.execute();

// Complex multi-table operations
const userWithProfile = await enhancedUsers
	.createUserWithProfile(
		{name: 'John', email: 'john@example.com'},
		true, // include post
		true // include address
	)
	.execute();

Core Features

Query Inspection

Every query returns a QueryResult object with .query and .execute() methods:

const query = users.selectUsers({name: 'John'});

// Inspect before execution
console.log(query.query.sqlText); // "SELECT ... FROM users WHERE name = $1"
console.log(query.query.values); // ["John"]

// Execute when ready
const results = await query.execute();

Chained Insert & Update Builder

Build complex multi-table operations with automatic CTE handling, now with full support for updates:

import {createChainedInsert} from 'pg-lightquery';

// Simple chained insert
const result = createChainedInsert()
	.insert('new_user', usersDb, userData, {returnField: '*'})
	.insertWithReference('user_post', postsDb, postData, {
		from: 'new_user',
		field: 'id',
		to: 'userId',
	})
	.selectFrom('new_user')
	.build();

// Conditional inserts
const result = createChainedInsert()
	.insert('new_user', usersDb, userData, {returnField: '*'})
	.insertWithReferenceIf(hasAddress, 'user_address', addressesDb, addressData, {
		from: 'new_user',
		field: 'id',
		to: 'userId',
	})
	.selectFrom('new_user')
	.build();

// UPDATE operations in transactions
const updateResult = createChainedInsert()
	.update('updated_user', usersDb, {name: 'Updated Name'}, {id: userId}, {returnField: '*'})
	.update('updated_post', postsDb, {title: 'Updated Title'}, {id: postId}, {returnField: '*'})
	.selectFrom('updated_user')
	.build();

// Mixed INSERT and UPDATE operations
const mixedResult = createChainedInsert()
	.insert('new_user', usersDb, newUserData, {returnField: '*'})
	.updateWithReference(
		'updated_post',
		postsDb,
		{content: 'Post updated by new user'},
		{id: existingPostId},
		{from: 'new_user', field: 'id', to: 'userId'},
		{returnField: '*'}
	)
	.selectFrom('new_user')
	.build();

// Execute the chained operation
const result = await mixedResult.execute();

Advanced Update Scenarios

// Update multiple related tables in a transaction
const result = createChainedInsert()
	.update('charge_update', chargesDb, {status: 'COMPLETED', amount: 1500}, {idCharge: chargeId}, {returnField: '*'})
	.updateTable('deal_update', 'dealsTable', {status: 'COMPLETED'}, {idDeal: dealId}, {returnField: '*'})
	.updateTable(
		'expense_update',
		'otherExpensesTable',
		{status: 'COMPLETED'},
		{idExpense: expenseId},
		{returnField: '*'}
	)
	.selectFrom('charge_update')
	.build();

// Conditional updates based on business logic
const shouldUpdateDeal = dealStatus === 'PENDING';
const shouldUpdateExpense = expenseAmount > 0;

const conditionalResult = createChainedInsert()
	.insert('new_charge', chargesDb, chargeData, {returnField: '*'})
	.updateIf(shouldUpdateDeal, 'deal_update', dealsDb, {status: 'ACTIVE'}, {idDeal: dealId})
	.updateIf(shouldUpdateExpense, 'expense_update', expensesDb, {amount: newAmount}, {idExpense: expenseId})
	.selectFrom('new_charge')
	.build();

// Update with references from previous operations
const referenceResult = createChainedInsert()
	.insert('new_entity', entitiesDb, entityData, {returnField: '*'})
	.updateWithReference(
		'linked_record',
		recordsDb,
		{lastModifiedBy: 'system'},
		{id: recordId},
		{from: 'new_entity', field: 'id', to: 'entityId'},
		{returnField: '*'}
	)
	.selectFrom('new_entity')
	.build();

// Generated SQL for mixed operations:
// WITH new_entity AS (
//   INSERT INTO entities (...) VALUES (...) RETURNING *
// ),
// linked_record AS (
//   UPDATE records
//   SET "lastModifiedBy" = $1, "entityId" = (SELECT "id" FROM new_entity)
//   WHERE "id" = $2
//   RETURNING *
// )
// SELECT * FROM new_entity;

Enhanced TableBase

Simplify complex table relationships with built-in registry:

class PlacesTable extends EnhancedTableBase<PlacesSchema> {
	constructor() {
		super(placesTable);

		// Register related tables
		this.registerRelatedTable('places_contacts', {tableDefinition: placesContactsTable});
		this.registerRelatedTable('places_contacts_billing', {tableDefinition: placesContactsBillingTable});
	}

	insertPlaceWithRelations(data: PlacesData, idContact: number, isBilling = false) {
		return this.createChainedInsert()
			.insert('place', this.db, data)
			.insertWithReference(
				'place_contact',
				'places_contacts',
				{idContact},
				{
					from: 'place',
					field: 'idPlace',
					to: 'idPlace',
				}
			)
			.insertWithReferenceIf(
				isBilling,
				'billing',
				'places_contacts_billing',
				{},
				{
					from: 'place_contact',
					field: 'idPlaceContact',
					to: 'idPlaceContact',
				}
			)
			.selectFrom('place')
			.build();
	}

	// Update operations with registered tables
	updatePlaceAndContacts(placeId: number, placeData: Partial<PlacesData>, contactData?: any) {
		return this.createChainedInsert()
			.update('updated_place', this.db, placeData, {idPlace: placeId}, {returnField: '*'})
			.updateTableIf(
				!!contactData,
				'updated_contact',
				'places_contacts',
				contactData || {},
				{idPlace: placeId},
				{returnField: '*'}
			)
			.selectFrom('updated_place')
			.build();
	}
}

Smart Query Operators

Built-in support for common SQL patterns:

// Pattern matching
await users.selectUsers({'name.like': 'John%'}).execute();

// Multiple values
await users.selectUsers({'id.in': [1, 2, 3]}).execute();

// Date ranges
await users
	.selectUsers({
		'createdAt.startDate': '2023-01-01',
		'createdAt.endDate': '2023-12-31',
	})
	.execute();

// JSON fields
await users.selectUsers({'settings.theme': 'dark'}).execute();

// NOT conditions
await users.selectUsers({'email.not': null}).execute();

Security & Projection Control

Separate concerns for security (what can be filtered) and projection (what gets returned):

// Public API: Limited filtering, safe data return
const publicUsers = users.select({
	allowedColumns: ['id', 'name'], // Can only filter by these
	options: {
		where: {name: 'John'},
		columnsToReturn: ['id', 'name', 'email'], // But can return these
	},
});

// Admin API: Full access
const adminUsers = users.select({
	allowedColumns: '*', // Can filter by anything
	options: {
		columnsToReturn: '*', // Can return everything
	},
});

Transaction Builder

Fluent interface for complex transactions:

const user1 = users.insertUser({name: 'Alice', email: 'alice@example.com'});
const user2 = users.insertUser({name: 'Bob', email: 'bob@example.com'});

const transaction = users.transaction().add(user1.query).add(user2.query);

// Inspect the entire transaction
console.log('Queries:', transaction.queries.length);

// Execute all or none
const results = await transaction.execute();

Safe Update Operations

Built-in safety features to prevent accidental mass updates:

// Basic update with required WHERE clause
const updateQuery = users.updateUser({name: 'John Updated', email: 'john.new@example.com'}, {id: 1});

// Inspect before execution
console.log(updateQuery.query.sqlText);
// OUTPUT: UPDATE users SET "name" = $1, "email" = $2, "lastChangedBy" = $3 WHERE "id" = $4

// Execute when ready
const updatedUser = await updateQuery.execute();

Safety Features

Required WHERE clause - Prevents accidental mass updates:

// ❌ This will throw an error
users.updateUser({name: 'New Name'}, {}); // Empty WHERE clause
// Error: WHERE clause is required for UPDATE operations

// ✅ Explicit mass update (use with caution)
users.updateUser({lastLoginAt: new Date()}, {}, {allowUpdateAll: true});

Advanced Update Options

// Update with smart operators in WHERE clause
await users.updateUser({status: 'inactive'}, {'email.like': '%@oldcompany.com'}).execute();

// Update with RETURNING clause
const updatedUser = await users.updateUser({name: 'Updated Name'}, {id: 1}, {returnField: 'id'}).execute();

// Track who made the change
await users.updateUser({name: 'Admin Updated'}, {id: 1}, {idUser: 'admin-123'}).execute();

Update Query Inspection

const updateQuery = users.updateUser({name: 'John', email: 'john@new.com'}, {id: 1});

// Full query inspection
console.log('SQL:', updateQuery.query.sqlText);
console.log('Parameters:', updateQuery.query.values);
console.log('Parameter count:', updateQuery.query.values.length);

// Generated SQL:
// UPDATE users
// SET "name" = $1, "email" = $2, "lastChangedBy" = $3
// WHERE "id" = $4
//
// Parameters: ['John', 'john@new.com', 'SERVER', 1]

Optional Audit Fields

Automatic lastChangedBy tracking with configurable defaults:

// Automatic audit field addition (when present in schema)
const insertQuery = users.insertUser({name: 'John', email: 'john@example.com'});
// Automatically includes lastChangedBy: 'SERVER' if field exists in schema

// Custom audit tracking
const insertQuery = users.insertUser({name: 'John', email: 'john@example.com'}, {idUser: 'admin-123'});
// Uses custom idUser value for lastChangedBy

// Update with audit tracking
const updateQuery = users.updateUser({name: 'Updated'}, {id: 1}, {idUser: 'user-456'});
// Tracks who made the change

Complex Joins with Type Safety

Filter by joined/aggregated columns that don't exist in your base table:

// Define custom schema for joined results
interface UserWithPosts {
	id: number;
	name: string;
	email: string;
	posts: Post[];
}

const userWithPostsSchema = {
	id: {type: 'INTEGER'},
	name: {type: 'TEXT'},
	email: {type: 'TEXT'},
	posts: {type: 'JSONB'},
} as const;

class UsersTable extends TableBase<UsersSchema> {
	selectUsersWithPosts(filters?: any) {
		const sql = `
			SELECT u.id, u.name, u.email, 
				   json_agg(p.*) as posts
			FROM users u
			LEFT JOIN posts p ON u.id = p.user_id
			GROUP BY u.id, u.name, u.email
			WHERE 1=1
		`;

		return this.selectWithCustomSchema<UserWithPosts, typeof userWithPostsSchema>({
			allowedColumns: ['id', 'name', 'posts'],
			predefinedSQL: {sqlText: sql},
			options: {where: filters},
		});
	}
}

// Now you can filter by joined columns!
const activeUsers = await users
	.selectUsersWithPosts({
		'posts.not': null, // Filter by posts (doesn't exist in users table)
		'name.like': 'John%', // Combined with regular columns
	})
	.execute();

Testing Made Easy

describe('User Operations', () => {
	it('generates correct SQL', () => {
		const query = users.selectUsers({name: 'John'});

		expect(query.query.sqlText).toContain('SELECT');
		expect(query.query.sqlText).toContain('WHERE "name" = $1');
		expect(query.query.values).toEqual(['John']);
	});

	it('executes and returns data', async () => {
		const result = await users.selectUsers({id: 1}).execute();
		expect(result).toHaveLength(1);
	});

	it('handles chained inserts correctly', async () => {
		const chainedInsert = createChainedInsert()
			.insert('new_user', usersDb, userData, {returnField: '*'})
			.insertWithReference('user_post', postsDb, postData, {
				from: 'new_user',
				field: 'id',
				to: 'userId',
			})
			.selectFrom('new_user')
			.build();

		expect(chainedInsert.queries[0].sqlText).toContain('WITH new_user AS');
		expect(chainedInsert.queries[0].sqlText).toContain('INSERT INTO users');
		expect(chainedInsert.queries[0].sqlText).toContain('INSERT INTO posts');
	});
});

Performance & Dependencies

Minimal footprint: Only 4 dependencies (pg, mocklogs, sql-ddl-to-json-schema, uuid)
Parameterized queries: Built-in SQL injection protection
Efficient execution: Deferred execution prevents unnecessary queries
TypeScript optimized: Full type inference and checking
CTE optimization: Efficient multi-table operations with proper parameter handling

Compared to Other Libraries

Feature	pg-lightquery	Prisma	TypeORM	Raw SQL
Type Safety	Full	Full	Partial	None
Query Inspection	Built-in	No	No	Manual
Chained Inserts	Type-safe	No	No	Manual
Bundle Size	Small	Large	Large	None
Complex Joins	Type-safe	Limited	Limited	Manual
Update Safety	Required WHERE	Manual	Manual	Manual
Audit Fields	Automatic	Manual	Manual	Manual
Learning Curve	Minimal	Steep	Steep	None
Flexibility	High	Medium	Medium	Full

API Reference

Core Types

// All methods return QueryResult<T>
interface QueryResult<T> {
	query: QueryObject; // Inspect SQL and parameters
	execute(): Promise<T>; // Execute when ready
}

interface QueryObject {
	sqlText: string; // Generated SQL
	values: any[]; // Parameterized values
}

Chained Insert & Update Builder

// Create a new chained insert builder
const builder = createChainedInsert();

// INSERT operations
builder.insert(cteName, table, data, options);
builder.insertWithReference(cteName, table, data, reference, options);
builder.insertWithReferenceIf(condition, cteName, table, data, reference, options);

// UPDATE operations
builder.update(cteName, table, data, where, options);
builder.updateWithReference(cteName, table, data, where, reference, options);
builder.updateIf(condition, cteName, table, data, where, options);
builder.updateWithReferenceIf(condition, cteName, table, data, where, reference, options);

// Set final SELECT
builder.selectFrom(cteName, columns);

// Build and execute
const result = builder.build();
const data = await result.execute();

Enhanced TableBase

class MyTable extends EnhancedTableBase<MySchema> {
	constructor() {
		super(tableDefinition);

		// Register related tables
		this.registerRelatedTable('related_table', {tableDefinition: relatedTableDef});
	}

	// Use chained inserts with registered tables
	complexInsertOperation() {
		return this.createChainedInsert()
			.insertIntoTable('main', 'main_table', data)
			.insertIntoTableWithReference('related', 'related_table', relatedData, reference)
			.selectFrom('main')
			.build();
	}

	// Use chained updates with registered tables
	complexUpdateOperation() {
		return this.createChainedInsert()
			.updateTable('main_update', 'main_table', data, where)
			.updateTableWithReference('related_update', 'related_table', relatedData, where, reference)
			.updateTableIf(condition, 'conditional_update', 'other_table', data, where)
			.selectFrom('main_update')
			.build();
	}
}

Query Operators

// Available operators for WHERE conditions
type QueryOperators = {
	'field.like': string; // LIKE pattern matching
	'field.in': any[]; // IN clause
	'field.not': any; // NOT EQUAL
	'field.startDate': string; // Date >= value
	'field.endDate': string; // Date <= value
	'field.orderBy': 'ASC' | 'DESC'; // ORDER BY
	'field.null': boolean; // IS NULL / IS NOT NULL
	// JSON field access
	'jsonField.property': any; // JSON -> 'property' = value
};

Audit Field Configuration

// Optional lastChangedBy field in schema
const schema = {
	// ... other fields
	lastChangedBy: {
		type: 'TEXT',
		notNull: false, // Optional field
	},
};

// Automatic addition with default value
const insertQuery = table.insert(data); // Uses 'SERVER' as default

// Custom audit tracking
const insertQuery = table.insert(data, {idUser: 'custom-user-id'});

Contributing

Contributions are welcome! Please check out our GitHub repository.

License

ISC License

Keywords

postgresql sql