ORM Setup

Get started with Bunty’s multi-database ORM in just a few minutes. This guide covers installation and database connections for MySQL, PostgreSQL, and SQLite.

Overview

Bunty ORM provides a unified interface for working with multiple database systems while leveraging Bun’s native SQL drivers for maximum performance. The ORM includes:

Multi-Database Support: MySQL, PostgreSQL, and SQLite with a consistent API
Type Safety: Full TypeScript integration with automatic type inference
Performance: Built on Bun’s optimized native drivers
Connection Management: Automatic pooling, SSL, and reconnection handling
Query Builder: Fluent, type-safe query construction
Migrations: Version-controlled schema management
Relationships: Define and query related data with ease

Key benefits of using Bunty ORM:

Zero Runtime Overhead: Compiles to optimized Bun driver calls
Database Agnostic: Write once, run on MySQL, PostgreSQL, or SQLite
TypeScript First: Full type safety from database to application
Bun Optimized: Designed specifically for Bun’s performance characteristics
Schema Management: Table and column definitions with TypeScript inference
Migration Tools: Database version control and schema evolution

Install the ORM package using Bun:

bun add @bunty/orm

Or with npm/yarn:

npm install @bunty/orm
# or
yarn add @bunty/orm

Note: While you can install with npm/yarn, using Bun is recommended for the best performance since the ORM is optimized for Bun’s native SQL drivers.

Database Connection

Each database client wraps Bun’s native SQL drivers with additional ORM functionality. The clients provide:

Connection Management: Automatic connection pooling and lifecycle management
Error Handling: Database-specific error translation and recovery
Type Safety: Full TypeScript integration with your database schema
Performance Optimization: Zero-overhead abstractions over Bun’s drivers

Simple Connection

Choose your database and connect with a connection string. The clients automatically handle connection pooling, SSL configuration, and reconnection logic:

db.ts

import { MysqlClient } from '@bunty/orm';

// Uses Bun's native MySQL driver for optimal performance
const db = new MysqlClient('mysql://user:password@localhost:3306/mydb');

// MySQL Client Features:
// - Built on Bun's optimized MySQL driver
// - Automatic connection pooling (default: 10 connections)
// - SSL support with certificate validation
// - Prepared statement caching for better performance

db.ts

import { PostgresClient } from '@bunty/orm';

// Leverages Bun's PostgreSQL driver with full async support
const db = new PostgresClient('postgresql://user:password@localhost:5432/mydb');

// PostgreSQL Client Features:
// - Built on Bun's async PostgreSQL driver
// - Connection pooling with configurable pool size
// - Advanced PostgreSQL features (arrays, JSON, etc.)
// - Automatic type inference for PostgreSQL-specific types

db.ts

import { SqliteClient } from '@bunty/orm';

// Direct file access with Bun's SQLite implementation
const db = new SqliteClient('./database.db');
// or for in-memory databases:
// const db = new SqliteClient(':memory:');

// SQLite Client Features:
// - File-based database with zero configuration
// - In-memory support for testing and caching
// - Built-in backup and restore capabilities
// - WAL mode support for better concurrency

Configuration Object

For more control over connection settings and Bun’s underlying driver options:

db.ts

import { MysqlClient } from '@bunty/orm';

const db = new MysqlClient({
host: 'localhost',
port: 3306,
user: 'myuser',
password: 'mypassword',
database: 'mydb',

// Bun MySQL driver options
connectionLimit: 20,
acquireTimeout: 60000,
ssl: {
  rejectUnauthorized: true,
  ca: fs.readFileSync('./ca.pem'),
  cert: fs.readFileSync('./client-cert.pem'),
  key: fs.readFileSync('./client-key.pem')
}
});

db.ts

import { PostgresClient } from '@bunty/orm';

const db = new PostgresClient({
host: 'localhost',
port: 5432,
user: 'myuser',
password: 'mypassword',
database: 'mydb',

// Bun PostgreSQL driver options
max: 20,
idleTimeoutMillis: 30000,
connectionTimeoutMillis: 2000,
ssl: {
  mode: 'require',
  ca: fs.readFileSync('./ca.pem'),
  cert: fs.readFileSync('./client-cert.pem'),
  key: fs.readFileSync('./client-key.pem')
}
});

db.ts

import { SqliteClient } from '@bunty/orm';

const db = new SqliteClient({
filename: './data/production.db',

// Bun SQLite driver options
create: true,
readwrite: true,

// Performance tuning
pragmas: {
  journal_mode: 'WAL',
  synchronous: 'NORMAL',
  cache_size: 10000,
  foreign_keys: true,
  busy_timeout: 30000
}
});

Production Setup

For production environments, use master-replica configurations with connection pooling:

db.ts

import { MysqlClient } from '@bunty/orm';

const db = new MysqlClient({
// Master database for writes
master: {
  host: 'mysql-master.example.com',
  port: 3306,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  ssl: { rejectUnauthorized: true }
},

// Read replicas for scaling
replicas: [
  {
    host: 'mysql-replica-1.example.com',
    port: 3306,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
    ssl: { rejectUnauthorized: true }
  },
  {
    host: 'mysql-replica-2.example.com',
    port: 3306,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
    ssl: { rejectUnauthorized: true }
  }
],

// Connection pool settings
connectionLimit: 50,
acquireTimeout: 60000,
idleTimeout: 300000,
queueLimit: 0
});

db.ts

import { PostgresClient } from '@bunty/orm';

const db = new PostgresClient({
// Master database for writes
master: {
  host: 'postgres-master.example.com',
  port: 5432,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  ssl: { mode: 'require' }
},

// Read replicas for scaling
replicas: [
  {
    host: 'postgres-replica-1.example.com',
    port: 5432,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
    ssl: { mode: 'require' }
  },
  {
    host: 'postgres-replica-2.example.com',
    port: 5432,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
    ssl: { mode: 'require' }
  }
],

// Connection pool settings
max: 50,
idleTimeoutMillis: 300000,
connectionTimeoutMillis: 5000
});

db.ts

import { SqliteClient } from '@bunty/orm';

// SQLite doesn't support traditional replication
// Use read-only replicas with backup strategies
const db = new SqliteClient({
filename: process.env.SQLITE_DB_PATH || './data/production.db',

// Production settings
create: false,  // Don't auto-create in production
readwrite: true,

// Optimized for production
pragmas: {
  journal_mode: 'WAL',
  synchronous: 'NORMAL',
  cache_size: 50000,
  temp_store: 'memory',
  mmap_size: 268435456, // 256MB
  foreign_keys: true,
  busy_timeout: 30000
}
});

// Backup strategy for SQLite
setInterval(async () => {
await db.backup('./backups/backup-' + new Date().toISOString() + '.db');
}, 3600000); // Hourly backups

Environment Configuration

Use environment variables for database configuration:

.env

# .env
DATABASE_URL=mysql://user:password@localhost:3306/mydb

.env

# .env
DATABASE_URL=postgresql://user:password@localhost:5432/mydb

.env

# .env
DATABASE_URL=./database.db
# or for in-memory
DATABASE_URL=:memory:

For production with replicas:

.env

# .env
DATABASE_MASTER_URL=mysql://user:pass@master:3306/myapp
DATABASE_REPLICA_1_URL=mysql://user:pass@replica1:3306/myapp
DATABASE_REPLICA_2_URL=mysql://user:pass@replica2:3306/myapp

.env

# .env
DATABASE_MASTER_URL=postgresql://user:pass@master:5432/myapp
DATABASE_REPLICA_1_URL=postgresql://user:pass@replica1:5432/myapp
DATABASE_REPLICA_2_URL=postgresql://user:pass@replica2:5432/myapp

.env

# .env
DATABASE_URL=./data/production.db
# For testing
TEST_DATABASE_URL=:memory:
# Backup location
BACKUP_DIR=./backups

Connection Testing

Verify your connection is working:

test-connection.ts

async function testConnection() {
try {
  const result = await db.$sql`SELECT 1 as test`;
  console.log('MySQL connected successfully!');
  return true;
} catch (error) {
  console.error('MySQL connection failed:', error);
  return false;
}
}

await testConnection();

test-connection.ts

async function testConnection() {
try {
  const result = await db.$sql`SELECT 1 as test`;
  console.log('PostgreSQL connected successfully!');
  return true;
} catch (error) {
  console.error('PostgreSQL connection failed:', error);
  return false;
}
}

await testConnection();

test-connection.ts

async function testConnection() {
try {
  const result = await db.$sql`SELECT 1 as test`;
  console.log('SQLite connected successfully!');
  return true;
} catch (error) {
  console.error('SQLite connection failed:', error);
  return false;
}
}

await testConnection();

Raw SQL Access

While the ORM provides a type-safe query builder, you can always drop down to raw SQL using Bun’s tagged template literals:

raw-queries.ts

// Direct access to Bun's MySQL driver
const users = await db.$sql`
SELECT u.*, p.title as latest_post
FROM users u
LEFT JOIN posts p ON p.author_id = u.id
WHERE u.created_at > ${new Date('2024-01-01')}
ORDER BY u.created_at DESC
`;

// Prepared statements are automatically handled
const userId = 123;
const user = await db.$sql`SELECT * FROM users WHERE id = ${userId}`;

raw-queries.ts

// PostgreSQL-specific features available
const users = await db.$sql`
SELECT u.*, jsonb_agg(p.*) as posts
FROM users u
LEFT JOIN posts p ON p.author_id = u.id
WHERE u.metadata @> ${{ role: 'admin' }}
GROUP BY u.id
`;

// Array operations
const tags = ['tech', 'javascript'];
const posts = await db.$sql`
SELECT * FROM posts 
WHERE tags && ${tags}
`;

raw-queries.ts

// SQLite with JSON1 extension
const users = await db.$sql`
SELECT u.*, 
       json_extract(u.metadata, '$.preferences') as prefs
FROM users u
WHERE json_extract(u.metadata, '$.active') = 1
`;

// Full-text search (if FTS5 enabled)
const results = await db.$sql`
SELECT * FROM posts_fts 
WHERE posts_fts MATCH ${searchTerm}
ORDER BY rank
`;

Benefits of db.$sql:

Full access to database-specific features
Automatic parameter sanitization
Type inference for result sets
Performance equivalent to raw driver usage
No SQL injection vulnerabilities

Performance Monitoring

The ORM provides built-in performance monitoring through Bun’s driver integration:

// Enable query logging and performance metrics
const db = new MysqlClient({
  // ... connection options
  debug: process.env.NODE_ENV === 'development',
  
  // Performance monitoring
  monitor: {
    queryLogging: true,           // Log slow queries
    slowQueryThreshold: 1000,     // Log queries > 1s
    connectionMetrics: true,      // Track connection pool stats
    performanceMetrics: true      // Collect timing data
  }
});

// Access performance metrics
const stats = await db.getConnectionStats();
console.log('Active connections:', stats.active);
console.log('Pool utilization:', stats.utilization);
console.log('Average query time:', stats.avgQueryTime);

Production Monitoring:

Query performance tracking
Connection pool health monitoring
Automatic slow query detection
Memory usage optimization
Error rate monitoring and alerting

Here’s a minimal example to get you started:

app.ts

import { MysqlClient } from '@bunty/orm';

// Initialize connection
const db = new MysqlClient(process.env.DATABASE_URL!);

// Test the connection
async function main() {
try {
  await db.$sql`SELECT 1`;
  console.log('✅ MySQL connected successfully!');
} catch (error) {
  console.error('❌ MySQL connection failed:', error);
  process.exit(1);
}
}

main();

app.ts

import { PostgresClient } from '@bunty/orm';

// Initialize connection
const db = new PostgresClient(process.env.DATABASE_URL!);

// Test the connection
async function main() {
try {
  await db.$sql`SELECT 1`;
  console.log('✅ PostgreSQL connected successfully!');
} catch (error) {
  console.error('❌ PostgreSQL connection failed:', error);
  process.exit(1);
}
}

main();

app.ts

import { SqliteClient } from '@bunty/orm';

// Initialize connection
const db = new SqliteClient(process.env.DATABASE_URL!);

// Test the connection
async function main() {
try {
  await db.$sql`SELECT 1`;
  console.log('✅ SQLite connected successfully!');
} catch (error) {
  console.error('❌ SQLite connection failed:', error);
  process.exit(1);
}
}

main();

What’s Next?

Now that you have the ORM connected, explore these guides:

Table Schemas - Define type-safe database tables
Basic Queries - Learn to query your data
Migrations - Version control your database schema
Query Builder - Advanced query construction
Relationships - Define and query related data

Troubleshooting

Common Connection Issues

**Connection Refused**: Ensure MySQL is running and accessible:
# Check if MySQL is running
brew services list | grep mysql
# or
sudo service mysql status

**Authentication Failed**: Verify credentials and permissions:
-- Create a new user with proper permissions
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';
GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'localhost';
FLUSH PRIVILEGES;

**Database Not Found**: Create the database first:
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

**Connection Refused**: Ensure PostgreSQL is running:
# Check if PostgreSQL is running
brew services list | grep postgresql
# or
sudo service postgresql status

**Authentication Failed**: Verify credentials and permissions:
-- Create a new user with proper permissions
CREATE USER myuser WITH PASSWORD 'mypassword';
CREATE DATABASE mydb OWNER myuser;
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

**Database Not Found**: Create the database first:
CREATE DATABASE mydb WITH ENCODING 'UTF8';

**File Not Found**: Ensure the directory exists:
# Create directory if it doesn't exist
mkdir -p ./data

**Permission Denied**: Check file/directory permissions:
# Make sure the directory is writable
chmod 755 ./data

**Database Locked**: Another process might be using the database:
// Use WAL mode for better concurrency
await db.$sql`PRAGMA journal_mode = WAL`;

Add environment variables to your .gitignore:

.env
.env.local