Basic Queries

Learn the fundamentals of querying your database with Bunty’s type-safe query builder. This guide covers the essential CRUD operations and error handling patterns.

Select Queries

Simple SELECT

Query all records from a table:

// Get all users
const allUsers = await db.select().from('users').getMany();

// Get all published posts
const publishedPosts = await db.select()
  .from('posts')
  .where('status', '=', 'published')
  .getMany();

Selecting Specific Columns

Control which columns to return:

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

// Select with aliases
const users = await db.select([
  'name as full_name',
  'email as email_address',
  'created_at as joined_date'
])
  .from('users')
  .getMany();

WHERE Conditions

Filter records with various conditions:

// Simple equality
const user = await db.select()
  .from('users')
  .where('email', '=', 'john@example.com')
  .first();

// Multiple conditions (AND)
const activeUsers = await db.select()
  .from('users')
  .where('is_active', '=', true)
  .where('email_verified', '=', true)
  .getMany();

// OR conditions
const users = await db.select()
  .from('users')
  .where('role', '=', 'admin')
  .orWhere('role', '=', 'editor')
  .getMany();

// Comparison operators
const recentPosts = await db.select()
  .from('posts')
  .where('created_at', '>', '2024-01-01')
  .where('view_count', '>=', 100)
  .getMany();

// IN operator
const specificUsers = await db.select()
  .from('users')
  .where('id', 'IN', [1, 2, 3, 4, 5])
  .getMany();

// LIKE operator for pattern matching
const users = await db.select()
  .from('users')
  .where('name', 'LIKE', 'John%')
  .getMany();

// NULL checks
const unverifiedUsers = await db.select()
  .from('users')
  .where('email_verified_at', 'IS NULL')
  .getMany();

Ordering and Limiting

Sort and limit results:

// Order by single column
const users = await db.select()
  .from('users')
  .orderBy('created_at', 'DESC')
  .getMany();

// Order by multiple columns
const posts = await db.select()
  .from('posts')
  .orderBy('published_at', 'DESC')
  .orderBy('title', 'ASC')
  .getMany();

// Limit results
const latestPosts = await db.select()
  .from('posts')
  .orderBy('created_at', 'DESC')
  .limit(10)
  .getMany();

// Offset for pagination
const page2Users = await db.select()
  .from('users')
  .orderBy('id', 'ASC')
  .limit(20)
  .offset(20)
  .getMany();

Aggregation Functions

Use SQL aggregation functions:

// Count records
const userCount = await db.select(['COUNT(*) as total'])
  .from('users')
  .first();

// Sum values
const totalViews = await db.select(['SUM(view_count) as total_views'])
  .from('posts')
  .first();

// Average values
const avgRating = await db.select(['AVG(rating) as average_rating'])
  .from('reviews')
  .first();

// Min/Max values
const stats = await db.select([
  'MIN(created_at) as first_post',
  'MAX(created_at) as latest_post',
  'COUNT(*) as total_posts'
])
  .from('posts')
  .first();

// Group by with aggregation
const postsByCategory = await db.select([
  'category_id',
  'COUNT(*) as post_count'
])
  .from('posts')
  .groupBy('category_id')
  .getMany();

JOIN Operations

Query related data across tables:

// Inner join
const usersWithPosts = await db.select([
  'u.name',
  'u.email', 
  'p.title',
  'p.created_at'
])
  .from('users', 'u')
  .innerJoin('posts', 'p.author_id', 'u.id', 'p')
  .getMany();

// Left join (include users without posts)
const allUsersWithPosts = await db.select([
  'u.name',
  'u.email',
  'p.title'
])
  .from('users', 'u')
  .leftJoin('posts', 'p.author_id', 'u.id', 'p')
  .getMany();

// Multiple joins
const postsWithAuthorAndCategory = await db.select([
  'p.title',
  'p.content',
  'u.name as author_name',
  'c.name as category_name'
])
  .from('posts', 'p')
  .innerJoin('users', 'u.id', 'p.author_id', 'u')
  .leftJoin('categories', 'c.id', 'p.category_id', 'c')
  .where('p.status', '=', 'published')
  .getMany();

INSERT Queries

Single Record Insert

Insert one record at a time:

// Insert with individual values
const result = await db.insertInto('users')
  .value('name', 'John Doe')
  .value('email', 'john@example.com')
  .value('created_at', new Date())
  .execute();

console.log(`Created user with ID: ${result.insertId}`);

// Insert with object
const user = await db.insertInto('users')
  .values({
    name: 'Jane Smith',
    email: 'jane@example.com',
    role: 'editor',
    is_active: true,
    created_at: new Date()
  })
  .execute();

Multiple Record Insert

Insert multiple records efficiently:

// Batch insert
const users = await db.insertInto('users')
  .values([
    {
      name: 'Alice Johnson',
      email: 'alice@example.com',
      role: 'user'
    },
    {
      name: 'Bob Wilson',
      email: 'bob@example.com', 
      role: 'user'
    },
    {
      name: 'Charlie Brown',
      email: 'charlie@example.com',
      role: 'editor'
    }
  ])
  .execute();

console.log(`Created ${users.affectedRows} users`);

Insert with Default Values

Let the database handle defaults:

// Minimal insert (let DB handle defaults)
const user = await db.insertInto('users')
  .value('name', 'Minimal User')
  .value('email', 'minimal@example.com')
  // created_at, updated_at, is_active will use defaults
  .execute();

UPDATE Queries

Simple Updates

Update records with conditions:

// Update specific record
await db.updateTable('users')
  .set('email_verified', true)
  .set('email_verified_at', new Date())
  .where('email', '=', 'john@example.com')
  .execute();

// Update multiple fields
await db.updateTable('posts')
  .set('status', 'published')
  .set('published_at', new Date())
  .set('updated_at', new Date())
  .where('id', '=', 123)
  .execute();

// Update with object
await db.updateTable('users')
  .set({
    last_login_at: new Date(),
    login_count: db.sql`login_count + 1`
  })
  .where('id', '=', userId)
  .execute();

Conditional Updates

Update based on complex conditions:

// Update multiple records
const result = await db.updateTable('posts')
  .set('status', 'archived')
  .where('created_at', '<', '2023-01-01')
  .where('view_count', '<', 10)
  .execute();

console.log(`Archived ${result.affectedRows} old posts`);

// Update with IN clause
await db.updateTable('users')
  .set('role', 'verified_user')
  .where('id', 'IN', [1, 2, 3, 4, 5])
  .where('email_verified', '=', true)
  .execute();

Update with JOIN

Update based on related table data:

// Update posts based on author data
await db.updateTable('posts')
  .set('featured', true)
  .join('users', 'users.id', 'posts.author_id')
  .where('users.role', '=', 'premium')
  .where('posts.view_count', '>', 1000)
  .execute();

DELETE Queries

Simple Deletes

Remove records with conditions:

// Delete specific record
await db.deleteFrom('users')
  .where('email', '=', 'user@example.com')
  .execute();

// Delete with multiple conditions
const result = await db.deleteFrom('posts')
  .where('status', '=', 'draft')
  .where('created_at', '<', '2023-01-01')
  .execute();

console.log(`Deleted ${result.affectedRows} draft posts`);

// Delete with IN clause
await db.deleteFrom('sessions')
  .where('expires_at', '<', new Date())
  .execute();

Soft Deletes

Implement soft deletes instead of hard deletes:

// Soft delete by setting deleted_at timestamp
await db.updateTable('users')
  .set('deleted_at', new Date())
  .where('id', '=', userId)
  .execute();

// Query excluding soft-deleted records
const activeUsers = await db.select()
  .from('users')
  .where('deleted_at', 'IS NULL')
  .getMany();

// Restore soft-deleted record
await db.updateTable('users')
  .set('deleted_at', null)
  .where('id', '=', userId)
  .execute();

Advanced Query Patterns

Subqueries

Use subqueries for complex filtering:

// Find users with no posts
const usersWithoutPosts = await db.select()
  .from('users')
  .where('id', 'NOT IN', 
    db.select(['author_id']).from('posts')
  )
  .getMany();

// Find posts with above-average view count
const popularPosts = await db.select()
  .from('posts')
  .where('view_count', '>', 
    db.select(['AVG(view_count)']).from('posts')
  )
  .getMany();

EXISTS Queries

Check for existence in related tables:

// Find users who have published posts
const authorsWithPosts = await db.select()
  .from('users', 'u')
  .where('EXISTS', 
    db.select(['1'])
      .from('posts', 'p')
      .where('p.author_id', '=', db.raw('u.id'))
      .where('p.status', '=', 'published')
  )
  .getMany();

Window Functions

Use MySQL 8.0+ window functions:

// Rank posts by view count within each category
const rankedPosts = await db.select([
  'title',
  'category_id',
  'view_count',
  'RANK() OVER (PARTITION BY category_id ORDER BY view_count DESC) as rank'
])
  .from('posts')
  .getMany();

Error Handling

Try-Catch Pattern

Always wrap database operations in try-catch:

async function createUser(userData: { name: string; email: string }) {
  try {
    const result = await db.insertInto('users')
      .values(userData)
      .execute();
    
    return { success: true, userId: result.insertId };
  } catch (error) {
    console.error('Failed to create user:', error);
    
    // Handle specific MySQL errors
    if (error.code === 'ER_DUP_ENTRY') {
      return { success: false, error: 'Email already exists' };
    }
    
    if (error.code === 'ER_DATA_TOO_LONG') {
      return { success: false, error: 'Data too long for column' };
    }
    
    // Generic error
    return { success: false, error: 'Database error occurred' };
  }
}

Common MySQL Error Codes

Handle common database errors:

function handleDatabaseError(error: any) {
  switch (error.code) {
    case 'ER_DUP_ENTRY':
      return 'Record already exists';
    
    case 'ER_NO_REFERENCED_ROW_2':
      return 'Referenced record does not exist';
    
    case 'ER_ROW_IS_REFERENCED_2':
      return 'Cannot delete record that is referenced by other records';
    
    case 'ER_DATA_TOO_LONG':
      return 'Data too long for column';
    
    case 'ER_BAD_NULL_ERROR':
      return 'Column cannot be null';
    
    case 'ER_PARSE_ERROR':
      return 'SQL syntax error';
    
    case 'ER_ACCESS_DENIED_ERROR':
      return 'Access denied';
    
    case 'ER_BAD_DB_ERROR':
      return 'Database does not exist';
    
    case 'ECONNREFUSED':
      return 'Cannot connect to database server';
    
    default:
      return `Database error: ${error.message}`;
  }
}

Transaction Error Handling

Handle errors within transactions:

async function transferFunds(fromUserId: number, toUserId: number, amount: number) {
  const transaction = await db.transaction();
  
  try {
    // Debit from sender
    await transaction.updateTable('accounts')
      .set('balance', db.sql`balance - ${amount}`)
      .where('user_id', '=', fromUserId)
      .where('balance', '>=', amount)  // Ensure sufficient funds
      .execute();
    
    // Credit to receiver
    await transaction.updateTable('accounts')
      .set('balance', db.sql`balance + ${amount}`)
      .where('user_id', '=', toUserId)
      .execute();
    
    // Record transaction
    await transaction.insertInto('transactions')
      .values({
        from_user_id: fromUserId,
        to_user_id: toUserId,
        amount: amount,
        type: 'transfer',
        created_at: new Date()
      })
      .execute();
    
    await transaction.commit();
    return { success: true };
    
  } catch (error) {
    await transaction.rollback();
    console.error('Transfer failed:', error);
    return { success: false, error: handleDatabaseError(error) };
  }
}

Performance Tips

Query Optimization

Write efficient queries:

// Use indexes effectively
const user = await db.select()
  .from('users')
  .where('email', '=', email)  // Assuming email has unique index
  .first();

// Limit columns in large tables
const posts = await db.select(['id', 'title', 'created_at'])  // Don't select large content field
  .from('posts')
  .where('status', '=', 'published')
  .orderBy('created_at', 'DESC')
  .limit(20)
  .getMany();

// Use appropriate LIMIT for pagination
const pageSize = 20;
const page = 3;
const posts = await db.select()
  .from('posts')
  .orderBy('id', 'DESC')
  .limit(pageSize)
  .offset((page - 1) * pageSize)
  .getMany();

Batch Operations

Use batch operations for multiple records:

// Instead of multiple single inserts
for (const user of users) {
  await db.insertInto('users').values(user).execute(); // DON'T DO THIS
}

// Use batch insert
await db.insertInto('users').values(users).execute(); // DO THIS

Connection Reuse

Reuse database connections:

// Don't create new connections for each query
const db = new MysqlClient(process.env.DATABASE_URL!); // Create once

// Use the same connection for multiple queries
const users = await db.select().from('users').getMany();
const posts = await db.select().from('posts').getMany();

This guide covers the essential query operations you’ll use daily with the Bunty ORM. The type-safe query builder ensures your queries are both correct and performant while providing excellent TypeScript integration.

Next Steps

• Query Builder - Advanced query construction techniques
• Relationships - Define and query related data
• Migrations - Version control your database schema
• Performance - Optimize your database queries