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.
.execute() needed!)bun add bunql
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');
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.
BunQL automatically manages database connections for optimal performance and resource usage:
// ✅ 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!
}
});
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();
// 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();
// Single insert (auto-executes when awaited)
const result = await db.insert('users')
.values({
name: 'John Doe',
email: 'john@example.com',
active: true
});
console.log('Inserted ID:', result.lastInsertRowid);
// Bulk insert
const users = [
{ name: 'Alice', email: 'alice@example.com' },
{ name: 'Bob', email: 'bob@example.com' }
];
await db.insert('users').values(users);
// Update with where clause (auto-executes when awaited)
const result = await db.update('users')
.set({
name: 'John Smith',
email: 'johnsmith@example.com'
})
.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 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'));
// 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]);
BunQL provides two ways to handle transactions:
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: 'john@example.com' });
// 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);
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: 'john@example.com' })
.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);
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.
BunQL automatically detects your database type and adapts the schema operations accordingly:
PRAGMA statements and sqlite_master tableinformation_schema views and pg_* system tablesinformation_schema views and SHOW statements// 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();
// 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();
// 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');
// 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');
// Drop a table
await db.schema.dropTable('old_table');
// Drop table if exists
await db.schema.dropTable('old_table', true);
// 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();
BunQL works with all databases supported by Bun's native SQL bindings:
postgres://user:pass@localhost:5432/dbmysql://user:pass@localhost:3306/dbsqlite://path/to/database.db or :memory:// 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');
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();
}
finally blocks or use try-catch-finallyMain class for building and executing queries.
select(columns?): Create a SELECT queryinsert(table): Create an INSERT queryupdate(table): Create an UPDATE querydelete(table): Create a DELETE queryrun(query, params?): Execute raw SQLall(query, params?): Execute raw SQL and return all resultsget(query, params?): Execute raw SQL and return first resultbegin(callback): Execute queries in a transaction (legacy)transaction(callback): Execute queries in a transaction (recommended)close(): Close the database connectionMethods for building SELECT queries.
from(table): Specify the table to select fromwhere(column, operator, value): Add WHERE conditionwhereIn(column, values): Add WHERE IN conditionwhereNotIn(column, values): Add WHERE NOT IN conditionorderBy(column, direction?): Add ORDER BY clauselimit(count): Add LIMIT clauseoffset(count): Add OFFSET clauseexecute(): Execute the query and return resultsfirst(): Execute the query and return first resultall(): Alias for execute()count(): Execute and return count of matching recordsMethods for building INSERT queries.
values(data): Specify values to insert (object or array)execute(): Execute the insert querycount(): Return count of total records in tableMethods for building UPDATE queries.
set(data): Specify values to updatewhere(column, operator, value): Add WHERE conditionexecute(): Execute the update querycount(): Return count of records that would be affectedMethods for building DELETE queries.
where(column, operator, value): Add WHERE conditionexecute(): Execute the delete querycount(): Return count of records that would be deletedtry {
const result = await db.insert('users')
.values({ email: 'duplicate@example.com' })
.execute();
} catch (error) {
if (error.code === 'ER_DUP_ENTRY') {
console.log('Duplicate email detected');
} else {
console.error('Database error:', error.message);
}
}
Use transactions for bulk operations:
await db.begin(async (tx) => {
for (const user of users) {
await tx.insert('users').values(user).execute();
}
});
Use prepared statements (automatic with parameterized queries):
// This automatically uses prepared statements
const user = await db.select('*')
.from('users')
.where('id', '=', userId)
.first();
Use bulk inserts for multiple records:
await db.insert('users').values(usersArray).execute();
# Run tests
bun test
# Run tests in watch mode
bun test --watch
# Type checking
bun run typecheck
MIT
Contributions are welcome! Please feel free to submit a Pull Request.
FAQs
A fluent SQL query builder for Bun with transaction support
The npm package bunql receives a total of 42 weekly downloads. As such, bunql popularity was classified as not popular.
We found that bunql demonstrated a healthy version release cadence and project activity because the last version was released less than a year ago. It has 1 open source maintainer collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
Multiple high-impact npm maintainers confirm they have been targeted in the same social engineering campaign that compromised Axios.
Security News
Axios compromise traced to social engineering, showing how attacks on maintainers can bypass controls and expose the broader software supply chain.
Security News
Node.js has paused its bug bounty program after funding ended, removing payouts for vulnerability reports but keeping its security process unchanged.