BunQL

A fluent SQL query builder for Bun with transaction support, built on top of Bun's native SQL bindings. PostgreSQL-only for maximum performance and efficiency.

Features

• 🚀 Fluent API: Chainable methods for building SQL queries
• ⚡ Auto-Execute: Queries automatically execute when awaited (no .execute() needed!)
• 🔄 Transaction Support: Built-in transaction handling with rollback support
• 🛡️ SQL Injection Protection: Parameterized queries prevent SQL injection
• 🐘 PostgreSQL Optimized: Built specifically for PostgreSQL for maximum performance
• 📦 Zero Dependencies: Built on Bun's native SQL bindings
• 🧪 TypeScript Support: Full TypeScript support with type safety
• 🏗️ Schema Management: Complete PostgreSQL schema creation and management API
• 🔒 Auto-Close: Connections automatically close after non-transaction queries
• 🔄 Auto-Reconnect: Automatically reconnects when needed for subsequent queries

Installation

bun add bunql

Quick Start

import { BunQL, createQueryBuilder } from 'bunql';

// Create a BunQL instance
const db = new BunQL('postgres://user:pass@localhost:5432/mydb');
// or use the factory function
const db = createQueryBuilder('sqlite://myapp.db');

Auto-Execute Feature

BunQL queries automatically execute when awaited, eliminating the need to call .execute():

// ✅ Auto-executes when awaited
const users = await db.select('*').from('users');

// ✅ Also works with complex queries
const result = await db.update('users')
  .set({ active: false })
  .where('id', '=', 1);

// ✅ You can still use .execute() if you prefer explicit execution
const users = await db.select('*').from('users').execute();

This makes the API more concise and intuitive while maintaining backward compatibility.

Auto-Close & Auto-Reconnect

BunQL automatically manages database connections for optimal performance and resource usage:

Auto-Close

• Non-transaction queries: Connections automatically close after execution
• Transaction queries: Connections stay open during the transaction, then auto-close
• Error handling: Connections auto-close even if queries fail

Auto-Reconnect

• Seamless reconnection: Automatically reconnects when needed for subsequent queries
• No manual management: No need to manually open/close connections
• Resource efficient: Prevents connection leaks and "too many clients" errors

// ✅ Auto-close after each query
const count1 = await db.select('*').from('users').count();
const count2 = await db.select('*').from('users').count(); // Auto-reconnects

// ✅ Transaction keeps connection open
const result = await db.transaction(async (trx) => {
  const user = await trx.insert('users').values({ name: 'John' });
  const profile = await trx.insert('profiles').values({ user_id: user.lastInsertRowid });
  return { user, profile };
}); // Auto-closes after transaction

// ✅ Perfect for Bun.serve applications
Bun.serve({
  port: 3000,
  async fetch(request) {
    const db = new BunQL(process.env.DATABASE_URL!);
    
    try {
      const users = await db.select('*').from('users').all();
      return new Response(JSON.stringify(users));
    } catch (error) {
      return new Response('Error', { status: 500 });
    }
    // Connection automatically closed - no manual cleanup needed!
  }
});

Count Functionality

BunQL provides .count() methods on all query types to get the number of records:

// Count all records in a table
const totalUsers = await db.select('*').from('users').count();

// Count with WHERE conditions
const activeUsers = await db.select('*')
  .from('users')
  .where('active', '=', true)
  .count();

// Count records that would be affected by an update
const usersToUpdate = await db.update('users')
  .set({ active: false })
  .where('last_login', '<', new Date('2023-01-01'))
  .count();

// Count records that would be deleted
const usersToDelete = await db.delete('users')
  .where('active', '=', false)
  .count();

// Count total records in table (for insert queries)
const totalRecords = await db.insert('users').values({ name: 'Test' }).count();

Usage Examples

Select Queries

// Basic select (auto-executes when awaited)
const users = await db.select('*').from('users');

// Select with specific columns
const users = await db.select(['id', 'name', 'email']).from('users');

// Select with where clause
const user = await db.select('*')
  .from('users')
  .where('id', '=', 1)
  .first();

// Select with multiple conditions
const activeUsers = await db.select('*')
  .from('users')
  .where('active', '=', true)
  .where('role', '=', 'admin');

// Select with IN clause
const users = await db.select('*')
  .from('users')
  .whereIn('id', [1, 2, 3]);

// Select with ordering and pagination
const users = await db.select('*')
  .from('users')
  .orderBy('name', 'ASC')
  .limit(10)
  .offset(20);

// You can still use .execute() if you prefer explicit execution
const users = await db.select('*').from('users').execute();

Insert Queries

// Single insert (auto-executes when awaited)
const result = await db.insert('users')
  .values({
    name: 'John Doe',
    email: '[email protected]',
    active: true
  });

console.log('Inserted ID:', result.lastInsertRowid);

// Bulk insert
const users = [
  { name: 'Alice', email: '[email protected]' },
  { name: 'Bob', email: '[email protected]' }
];

await db.insert('users').values(users);

Update Queries

// Update with where clause (auto-executes when awaited)
const result = await db.update('users')
  .set({
    name: 'John Smith',
    email: '[email protected]'
  })
  .where('id', '=', 1);

console.log('Updated rows:', result.affectedRows);

// Update multiple fields
await db.update('users')
  .set({
    active: false,
    updated_at: new Date()
  })
  .where('last_login', '<', new Date('2023-01-01'));

Delete Queries

// Delete with where clause (auto-executes when awaited)
const result = await db.delete('users')
  .where('id', '=', 1);

console.log('Deleted rows:', result.affectedRows);

// Delete with multiple conditions
await db.delete('users')
  .where('active', '=', false)
  .where('created_at', '<', new Date('2023-01-01'));

Raw SQL Queries

// Execute raw SQL
await db.run('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)');

// Execute with parameters
await db.run('UPDATE users SET name = ? WHERE id = ?', ['John', 1]);

// Get all results
const users = await db.all('SELECT * FROM users WHERE active = ?', [true]);

// Get single result
const user = await db.get('SELECT * FROM users WHERE id = ?', [1]);

Transactions

BunQL provides two ways to handle transactions:

Method 1: Using db.transaction() (Recommended)

// Clean transaction API with automatic rollback on error
const result = await db.transaction(async (trx) => {
  // Insert user
  const userResult = await trx.insert('users')
    .values({ name: 'John', email: '[email protected]' });
  
  // Insert user profile
  await trx.insert('user_profiles')
    .values({ 
      user_id: userResult.lastInsertRowid,
      bio: 'Software developer'
    });
  
  // Update user status
  await trx.update('users')
    .set({ active: true })
    .where('id', '=', userResult.lastInsertRowid);
  
  return userResult.lastInsertRowid;
});

console.log('Transaction completed, user ID:', result);

Method 2: Using db.begin() (Legacy)

// Transaction with automatic rollback on error
const result = await db.begin(async (tx) => {
  // Insert user
  const userResult = await tx.insert('users')
    .values({ name: 'John', email: '[email protected]' })
    .execute();
  
  // Insert user profile
  await tx.insert('user_profiles')
    .values({ 
      user_id: userResult.lastInsertRowid,
      bio: 'Software developer'
    })
    .execute();
  
  // Update user status
  await tx.update('users')
    .set({ active: true })
    .where('id', '=', userResult.lastInsertRowid)
    .execute();
  
  return userResult.lastInsertRowid;
});

console.log('Transaction completed, user ID:', result);

Schema Management

BunQL provides a comprehensive schema management API that can replace Bunely for database schema operations. The schema API is database-agnostic and supports SQLite, PostgreSQL, and MySQL.

Database Support

BunQL automatically detects your database type and adapts the schema operations accordingly:

• SQLite: Uses PRAGMA statements and sqlite_master table
• PostgreSQL: Uses information_schema views and pg_* system tables
• MySQL: Uses information_schema views and SHOW statements

Database Detection

// Get database information
const dbInfo = await db.schema.getDatabaseInfo();
console.log(`Database: ${dbInfo.type} ${dbInfo.version}`);

### Database-Specific Features

BunQL handles database differences automatically:

**Column Types:**
- `INTEGER` → `INTEGER` (SQLite), `INTEGER` (PostgreSQL), `INT` (MySQL)
- `BOOLEAN` → `INTEGER` (SQLite), `BOOLEAN` (PostgreSQL/MySQL)
- `BLOB` → `BLOB` (SQLite/MySQL), `BYTEA` (PostgreSQL)

**Auto-increment:**
- SQLite: `AUTOINCREMENT`
- MySQL: `AUTO_INCREMENT`  
- PostgreSQL: Uses `SERIAL` or `IDENTITY` (handled automatically)

**Quoting:**
- SQLite/PostgreSQL: `"table_name"`
- MySQL: `` `table_name` ``

**Schema Introspection:**
- SQLite: `PRAGMA table_info()`, `sqlite_master`
- PostgreSQL: `information_schema.columns`, `pg_indexes`
- MySQL: `information_schema.tables`, `information_schema.statistics`

### Creating Tables

```typescript
// Create a table with columns, indexes, and foreign keys
await db.schema.createTable('users')
  .addColumn({
    name: 'id',
    type: 'INTEGER',
    primaryKey: true,
    autoIncrement: true
  })
  .addColumn({
    name: 'name',
    type: 'TEXT',
    notNull: true
  })
  .addColumn({
    name: 'email',
    type: 'TEXT',
    unique: true,
    notNull: true
  })
  .addColumn({
    name: 'age',
    type: 'INTEGER',
    defaultValue: 0
  })
  .addIndex({
    name: 'idx_users_email',
    columns: ['email'],
    unique: true
  })
  .execute();

// Create table with foreign key constraints
await db.schema.createTable('posts')
  .addColumn({
    name: 'id',
    type: 'INTEGER',
    primaryKey: true,
    autoIncrement: true
  })
  .addColumn({
    name: 'title',
    type: 'TEXT',
    notNull: true
  })
  .addColumn({
    name: 'user_id',
    type: 'INTEGER',
    notNull: true
  })
  .addForeignKey({
    name: 'fk_posts_user_id',
    columns: ['user_id'],
    referencedTable: 'users',
    referencedColumns: ['id'],
    onDelete: 'CASCADE'
  })
  .execute();

Altering Tables

// Add a new column
await db.schema.alterTable('users')
  .addColumn({
    name: 'bio',
    type: 'TEXT',
    notNull: false
  })
  .execute();

// Rename a column
await db.schema.alterTable('posts')
  .renameColumn('content', 'body')
  .execute();

// Add an index
await db.schema.alterTable('users')
  .addIndex({
    name: 'idx_users_age',
    columns: ['age'],
    unique: false
  })
  .execute();

// Drop a column
await db.schema.alterTable('users')
  .dropColumn('old_column')
  .execute();

Schema Introspection

// Check if a table exists
const exists = await db.schema.hasTable('users');

// Get all tables
const tables = await db.schema.getTables();

// Get table information
const tableInfo = await db.schema.getTableInfo('users');
console.log(tableInfo);
// [
//   { name: 'id', type: 'INTEGER', notNull: true, primaryKey: true, ... },
//   { name: 'name', type: 'TEXT', notNull: true, primaryKey: false, ... }
// ]

// Get indexes for a table
const indexes = await db.schema.getIndexes('users');

// Get foreign keys for a table
const foreignKeys = await db.schema.getForeignKeys('posts');

// Get complete table information
const completeInfo = await db.schema.getCompleteTableInfo('users');

Index Management

// Create an index
await db.schema.createIndex('users', {
  name: 'idx_users_name_email',
  columns: ['name', 'email'],
  unique: false
});

// Make columns unique
await db.schema.makeColumnsUnique('posts', ['title', 'user_id']);

// Drop an index
await db.schema.dropIndex('idx_users_email');

Table Management

// Drop a table
await db.schema.dropTable('old_table');

// Drop table if exists
await db.schema.dropTable('old_table', true);

Complex Queries

// Complex select with joins (using raw SQL)
const usersWithProfiles = await db.all(`
  SELECT u.*, p.bio, p.avatar 
  FROM users u 
  LEFT JOIN user_profiles p ON u.id = p.user_id 
  WHERE u.active = ? 
  ORDER BY u.created_at DESC 
  LIMIT ?
`, [true, 10]);

// Using the query builder for complex conditions
const recentUsers = await db.select(['u.id', 'u.name', 'u.email', 'p.bio'])
  .from('users u')
  .where('u.active', '=', true)
  .where('u.created_at', '>', new Date('2023-01-01'))
  .whereIn('u.role', ['admin', 'user'])
  .orderBy('u.created_at', 'DESC')
  .limit(50)
  .execute();

Database Support

BunQL works with all databases supported by Bun's native SQL bindings:

• PostgreSQL: postgres://user:pass@localhost:5432/db
• MySQL: mysql://user:pass@localhost:3306/db
• SQLite: sqlite://path/to/database.db or :memory:

Connection Management

Opening Connections

// SQLite (file)
const db = new BunQL('sqlite://database.db');

// SQLite (in-memory)
const db = new BunQL('sqlite://:memory:');

// PostgreSQL
const db = new BunQL('postgres://user:pass@localhost:5432/mydb');

// MySQL
const db = new BunQL('mysql://user:pass@localhost:3306/mydb');

Closing Connections

Always close database connections when you're done to free up resources:

const db = new BunQL('sqlite://database.db');

try {
  // Your database operations
  await db.insert('users').values({ name: 'John' });
  const users = await db.select('*').from('users').all();
} finally {
  // Always close the connection
  await db.close();
}

Best Practices

• Always close connections in finally blocks or use try-catch-finally
• Use connection pooling for production applications
• Close connections after transactions complete
• Handle connection errors gracefully

API Reference

BunQL

Main class for building and executing queries.

Methods

• select(columns?): Create a SELECT query
• insert(table): Create an INSERT query
• update(table): Create an UPDATE query
• delete(table): Create a DELETE query
• run(query, params?): Execute raw SQL
• all(query, params?): Execute raw SQL and return all results
• get(query, params?): Execute raw SQL and return first result
• begin(callback): Execute queries in a transaction (legacy)
• transaction(callback): Execute queries in a transaction (recommended)
• close(): Close the database connection

SelectQuery

Methods for building SELECT queries.

Methods

• from(table): Specify the table to select from
• where(column, operator, value): Add WHERE condition
• whereIn(column, values): Add WHERE IN condition
• whereNotIn(column, values): Add WHERE NOT IN condition
• orderBy(column, direction?): Add ORDER BY clause
• limit(count): Add LIMIT clause
• offset(count): Add OFFSET clause
• execute(): Execute the query and return results
• first(): Execute the query and return first result
• all(): Alias for execute()
• count(): Execute and return count of matching records

InsertQuery

Methods for building INSERT queries.

Methods

• values(data): Specify values to insert (object or array)
• execute(): Execute the insert query
• count(): Return count of total records in table

UpdateQuery

Methods for building UPDATE queries.

Methods

• set(data): Specify values to update
• where(column, operator, value): Add WHERE condition
• execute(): Execute the update query
• count(): Return count of records that would be affected

DeleteQuery

Methods for building DELETE queries.

Methods

• where(column, operator, value): Add WHERE condition
• execute(): Execute the delete query
• count(): Return count of records that would be deleted

Error Handling

try {
  const result = await db.insert('users')
    .values({ email: '[email protected]' })
    .execute();
} catch (error) {
  if (error.code === 'ER_DUP_ENTRY') {
    console.log('Duplicate email detected');
  } else {
    console.error('Database error:', error.message);
  }
}

Performance Tips

1. Use transactions for bulk operations:

   await db.begin(async (tx) => {
     for (const user of users) {
       await tx.insert('users').values(user).execute();
     }
   });

2. Use prepared statements (automatic with parameterized queries):

   // This automatically uses prepared statements
   const user = await db.select('*')
     .from('users')
     .where('id', '=', userId)
     .first();

3. Use bulk inserts for multiple records:

   await db.insert('users').values(usersArray).execute();

Testing

# Run tests
bun test

# Run tests in watch mode
bun test --watch

# Type checking
bun run typecheck

License

MIT

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.