# 🗄️ Database Integration Tutorial

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

[![Intermediate](https://img.shields.io/badge/📈_Level-Intermediate-orange?style=flat)]()
[![Time](https://img.shields.io/badge/⏱️_Duration-20_min-blue?style=flat)]()
[![Database Focus](https://img.shields.io/badge/🎯_Focus-Databases-4ECDC4?style=flat)]()

## 🎯 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](../Getting-Started.md))
- Basic SQL knowledge
- Understanding of database concepts
- 15-20 minutes available

## 🚀 Step 1: Launch Database Arsenal

```bash
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

```sql
-- 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

```sql
-- 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)
```javascript
// 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)
```javascript
// 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)
```javascript
// 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
```javascript
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
```javascript
// 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
```javascript
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
```javascript
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

```javascript
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

```javascript
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

```javascript
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

```javascript
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

```sql
-- 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

```sql
-- 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

```javascript
// 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](Build-Optimization.md)** - Optimize your build pipeline
- **[Testing Arsenal Tutorial](Testing-Arsenal.md)** - Master comprehensive testing
- **[Integration Examples](../Integration-Guides.md)** - Real-world implementation

### Reference Materials
- **[Database API Reference](../API-Documentation.md#databaseinfrastructurearsenal)**
- **[SQL Examples](../SQL-Examples.md)** - Advanced query patterns
- **[Performance Monitoring](../Analytics.md)** - Database metrics

### External Resources
- **[SQLite Documentation](https://www.sqlite.org/docs.html)**
- **[Redis Commands](https://redis.io/commands)**
- **[AWS S3 API](https://docs.aws.amazon.com/s3/)**

---

**Questions?** Join our [GitHub Discussions](https://github.com/brendadeeznuts1111/Arsenal-Lab/discussions) or check the [Troubleshooting Guide](../Troubleshooting.md).

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