Database Integration

🗄️ Database Integration Tutorial

Master database performance testing - Learn to benchmark SQLite, Redis, and cloud storage integrations with real-world examples and optimization techniques.

🎯 Tutorial Overview

This tutorial covers:

• SQLite performance testing and optimization
• Redis integration and caching patterns
• Cloud storage benchmarking (S3-compatible)
• Database connection pooling
• Query optimization techniques
• Migration testing strategies

📋 Prerequisites

• Arsenal Lab installed (Getting Started)
• Basic SQL knowledge
• Understanding of database concepts
• 15-20 minutes available

🚀 Step 1: Launch Database Arsenal

cd Arsenal-Lab
bun run dev

Navigate to http://localhost:3655 and select "Database Infrastructure Arsenal".

Interface Components

• SQLite Demo: In-memory database testing
• Redis Demo: Cache performance testing
• Performance Metrics: Real-time database stats
• Code Examples: Copy-paste ready snippets

🗄️ Step 2: SQLite Performance Testing

Basic SQLite Operations

1. Click "SQLite Demo"
2. Click "Create Test Database"
3. Run "Basic CRUD Operations"

What you'll see:

✅ Connected to SQLite database
✅ Created users table
✅ Inserted 100 test records
✅ Query executed in 45ms
✅ Retrieved 100 records

Understanding SQLite Results

• Connection time: How fast SQLite initializes
• Query execution: Time to run SELECT/INSERT/UPDATE/DELETE
• Memory usage: RAM consumption for in-memory databases
• Transaction speed: Bulk operation performance

Advanced SQLite Testing

Bulk Insert Performance

-- Test different batch sizes
INSERT INTO users (name, email, created_at) VALUES
  ('User 1', 'user1@example.com', datetime('now')),
  ('User 2', 'user2@example.com', datetime('now')),
  -- ... up to 1000 records

Performance insights:

• Transaction grouping reduces overhead
• Prepared statements improve performance
• Indexes speed up SELECT queries

Query Optimization

-- Test indexed vs non-indexed queries
CREATE INDEX idx_users_email ON users(email);

-- Benchmark these queries:
SELECT * FROM users WHERE email = 'test@example.com';  -- Uses index
SELECT * FROM users WHERE name LIKE '%test%';         -- No index

🔴 Step 3: Redis Integration Testing

Redis Connection Setup

1. Switch to "Redis Demo" tab
2. Click "Connect to Redis"
3. Test basic "SET/GET Operations"

Expected output:

✅ Connected to Redis server
✅ SET operation: 1.2ms
✅ GET operation: 0.8ms
✅ Pipeline operations: 2.8ms for 10 commands

Redis Data Structures

Strings (SET/GET)

// Basic key-value operations
await redis.set('user:123:name', 'John Doe');
await redis.set('user:123:email', 'john@example.com');
const name = await redis.get('user:123:name');

Hashes (HSET/HGET)

// Store structured data
await redis.hset('user:123', {
  name: 'John Doe',
  email: 'john@example.com',
  age: 30,
  active: true
});

const user = await redis.hgetall('user:123');

Sorted Sets (ZADD/ZRANGE)

// Leaderboards and ranking
await redis.zadd('leaderboard', [
  { score: 1500, member: 'player1' },
  { score: 1200, member: 'player2' },
  { score: 1800, member: 'player3' }
]);

const topPlayers = await redis.zrevrange('leaderboard', 0, 2, 'WITHSCORES');

Redis Performance Patterns

Caching Strategy

class RedisCache {
  constructor(redis) {
    this.redis = redis;
    this.ttl = 3600; // 1 hour
  }

  async get(key) {
    const cached = await this.redis.get(key);
    if (cached) {
      return JSON.parse(cached);
    }
    return null;
  }

  async set(key, value, ttl = this.ttl) {
    await this.redis.setex(key, ttl, JSON.stringify(value));
  }

  async invalidate(pattern) {
    const keys = await this.redis.keys(pattern);
    if (keys.length > 0) {
      await this.redis.del(keys);
    }
  }
}

Pub/Sub Messaging

// Publisher
await redis.publish('events', JSON.stringify({
  type: 'user_login',
  userId: 123,
  timestamp: Date.now()
}));

// Subscriber
const subscriber = redis.duplicate();
await subscriber.subscribe('events');

subscriber.on('message', (channel, message) => {
  const event = JSON.parse(message);
  console.log(`Received: ${event.type}`);
});

☁️ Step 4: Cloud Storage Benchmarking

S3-Compatible Testing

1. Click "Cloud Storage Demo"
2. Test "Upload Performance" with different file sizes
3. Run "Download Tests" for various scenarios

Performance metrics:

File Size: 1MB
Upload: 45ms (22 MB/s)
Download: 32ms (31 MB/s)

File Size: 10MB
Upload: 380ms (26 MB/s)
Download: 290ms (34 MB/s)

Storage Patterns

File Upload with Metadata

class CloudStorage {
  async uploadFile(file, metadata = {}) {
    const key = `uploads/${Date.now()}-${file.name}`;
    const buffer = await file.arrayBuffer();

    const result = await this.s3.putObject(this.bucket, key, buffer, {
      metadata: {
        'content-type': file.type,
        'original-name': file.name,
        ...metadata
      }
    });

    return {
      key,
      etag: result.etag,
      size: buffer.byteLength,
      url: this.getPublicUrl(key)
    };
  }

  async downloadFile(key) {
    const object = await this.s3.getObject(this.bucket, key);
    return new Blob([object], {
      type: object.metadata?.['content-type']
    });
  }
}

CDN Integration

class CDNStorage extends CloudStorage {
  constructor(s3Client, cdnDomain) {
    super(s3Client);
    this.cdnDomain = cdnDomain;
  }

  getPublicUrl(key) {
    return `https://${this.cdnDomain}/${key}`;
  }

  async uploadWithCDN(file, options = {}) {
    const result = await this.uploadFile(file, options);

    // Invalidate CDN cache if needed
    if (options.invalidateCDN) {
      await this.invalidateCDN(result.key);
    }

    return {
      ...result,
      cdnUrl: this.getPublicUrl(result.key)
    };
  }
}

🧪 Step 5: Advanced Database Patterns

Connection Pooling

class DatabasePool {
  constructor(maxConnections = 10) {
    this.maxConnections = maxConnections;
    this.connections = [];
    this.waitingQueue = [];
  }

  async getConnection() {
    if (this.connections.length > 0) {
      return this.connections.pop();
    }

    if (this.connections.length < this.maxConnections) {
      return this.createConnection();
    }

    // Wait for connection to be returned
    return new Promise((resolve) => {
      this.waitingQueue.push(resolve);
    });
  }

  returnConnection(connection) {
    if (this.waitingQueue.length > 0) {
      const resolver = this.waitingQueue.shift();
      resolver(connection);
    } else {
      this.connections.push(connection);
    }
  }

  async withConnection(callback) {
    const connection = await this.getConnection();
    try {
      return await callback(connection);
    } finally {
      this.returnConnection(connection);
    }
  }
}

Transaction Management

class TransactionManager {
  constructor(database) {
    this.database = database;
  }

  async runInTransaction(callback) {
    const db = await this.database.getConnection();

    try {
      await db.run('BEGIN TRANSACTION');

      const result = await callback(db);

      await db.run('COMMIT');
      return result;

    } catch (error) {
      await db.run('ROLLBACK');
      throw error;

    } finally {
      this.database.returnConnection(db);
    }
  }

  // Example usage
  async transferMoney(fromId, toId, amount) {
    return this.runInTransaction(async (db) => {
      // Check balance
      const fromAccount = await db.prepare(
        'SELECT balance FROM accounts WHERE id = ?'
      ).get(fromId);

      if (fromAccount.balance < amount) {
        throw new Error('Insufficient funds');
      }

      // Perform transfer
      await db.prepare(
        'UPDATE accounts SET balance = balance - ? WHERE id = ?'
      ).run(amount, fromId);

      await db.prepare(
        'UPDATE accounts SET balance = balance + ? WHERE id = ?'
      ).run(amount, toId);

      return { success: true, amount };
    });
  }
}

📊 Step 6: Performance Monitoring

Database Metrics Collection

class DatabaseMonitor {
  constructor() {
    this.metrics = {
      connections: { active: 0, idle: 0, total: 0 },
      queries: { total: 0, slow: 0, failed: 0, avgDuration: 0 },
      cache: { hits: 0, misses: 0, hitRate: 0 }
    };
  }

  recordQuery(duration, success = true) {
    this.metrics.queries.total++;
    this.metrics.queries.avgDuration =
      (this.metrics.queries.avgDuration + duration) / 2;

    if (!success) {
      this.metrics.queries.failed++;
    }

    if (duration > 100) { // Slow query threshold
      this.metrics.queries.slow++;
    }
  }

  recordCacheAccess(hit) {
    if (hit) {
      this.metrics.cache.hits++;
    } else {
      this.metrics.cache.misses++;
    }

    this.metrics.cache.hitRate =
      this.metrics.cache.hits /
      (this.metrics.cache.hits + this.metrics.cache.misses);
  }

  getMetrics() {
    return { ...this.metrics };
  }
}

Query Profiling

class QueryProfiler {
  constructor(database) {
    this.database = database;
    this.queries = [];
  }

  async profile(sql, params, operation) {
    const start = performance.now();

    try {
      const result = await operation();
      const duration = performance.now() - start;

      this.queries.push({
        sql,
        params,
        duration,
        success: true,
        timestamp: new Date()
      });

      return result;

    } catch (error) {
      const duration = performance.now() - start;

      this.queries.push({
        sql,
        params,
        duration,
        success: false,
        error: error.message,
        timestamp: new Date()
      });

      throw error;
    }
  }

  getSlowQueries(threshold = 100) {
    return this.queries.filter(q => q.duration > threshold);
  }

  getQueryStats() {
    const completed = this.queries.filter(q => q.success);
    const failed = this.queries.filter(q => !q.success);

    return {
      totalQueries: this.queries.length,
      successfulQueries: completed.length,
      failedQueries: failed.length,
      avgDuration: completed.reduce((sum, q) => sum + q.duration, 0) / completed.length,
      maxDuration: Math.max(...completed.map(q => q.duration)),
      slowQueries: this.getSlowQueries().length
    };
  }
}

🔧 Step 7: Optimization Strategies

Indexing Strategy

-- Analyze query patterns first
EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = 'test@example.com';

-- Create appropriate indexes
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created_at ON users(created_at);
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- Composite indexes for multi-column queries
CREATE INDEX idx_users_active_email ON users(is_active, email)
WHERE is_active = true;

-- Partial indexes for specific use cases
CREATE INDEX idx_recent_orders ON orders(created_at)
WHERE created_at >= date('now', '-30 days');

Query Optimization

-- Use EXISTS instead of COUNT for existence checks
SELECT EXISTS(SELECT 1 FROM users WHERE email = ?) as user_exists;

-- Prefer JOINs over subqueries for better performance
SELECT u.name, o.total_amount
FROM users u
JOIN orders o ON u.id = o.user_id;

-- Use LIMIT for pagination
SELECT * FROM products
ORDER BY created_at DESC
LIMIT 50 OFFSET 0;

-- Optimize with UNION ALL when possible
SELECT id, 'user' as type FROM users
UNION ALL
SELECT id, 'product' as type FROM products;

Memory Optimization

// Use streaming for large datasets
const stream = db.prepare('SELECT * FROM large_table').iterate();

for await (const row of stream) {
  processRow(row);
}

// Batch processing
const BATCH_SIZE = 1000;
let offset = 0;

while (true) {
  const batch = db.prepare('SELECT * FROM table LIMIT ? OFFSET ?')
    .all(BATCH_SIZE, offset);

  if (batch.length === 0) break;

  await processBatch(batch);
  offset += BATCH_SIZE;

  // Allow GC between batches
  if (Bun.gc) Bun.gc();
}

🎯 Tutorial Summary

What You Mastered

✅ SQLite performance testing and optimization ✅ Redis integration patterns and caching ✅ Cloud storage benchmarking techniques ✅ Connection pooling and transaction management ✅ Query optimization strategies ✅ Database monitoring and profiling

Key Takeaways

1. Database choice matters - Select the right database for your use case
2. Connection pooling prevents resource exhaustion
3. Indexing strategy dramatically impacts query performance
4. Transactions ensure data consistency in complex operations
5. Monitoring provides insights for continuous optimization

Real-World Applications

• Web applications with user management and sessions
• E-commerce platforms with product catalogs and orders
• Content management systems with rich media storage
• Analytics platforms requiring fast data aggregation
• Real-time applications needing pub/sub messaging

🏆 Achievement Unlocked!

Database Integration Expert 🏆 You've completed advanced database testing and learned to:

• Benchmark multiple database systems
• Implement caching strategies
• Optimize query performance
• Monitor database health
• Scale applications effectively

📚 Next Steps & Resources

Continue Learning

• Build Optimization Tutorial - Optimize your build pipeline
• Testing Arsenal Tutorial - Master comprehensive testing
• Integration Examples - Real-world implementation

Reference Materials

• Database API Reference
• SQL Examples - Advanced query patterns
• Performance Monitoring - Database metrics

External Resources

• SQLite Documentation
• Redis Commands
• AWS S3 API

---

Questions? Join our GitHub Discussions or check the Troubleshooting Guide.

Built with ❤️ for the Bun ecosystem • Last updated: October 21, 2025