Full-Stack Architecture Patterns

Chapter 4: Database Integration

4.1 Database Patterns

Database integration patterns define how applications interact with databases. Choosing the right pattern affects performance, maintainability, and scalability.

Repository Pattern:

• Abstracts database access logic
• Provides interface for data operations
• Easier to test and maintain
• Can switch database implementations

// Repository interface
class UserRepository {
    async findById(id) { }
    async create(user) { }
    async update(id, user) { }
    async delete(id) { }
}

// Implementation
class MySQLUserRepository extends UserRepository {
    async findById(id) {
        return await db.query('SELECT * FROM users WHERE id = ?', [id]);
    }
}

Active Record Pattern:

• Model contains database logic
• Each model instance represents a database row
• Simpler but less flexible
• Used in Rails, Laravel Eloquent

// Active Record example
const user = await User.find(1);
user.name = "John";
await user.save();
await user.delete();

Data Mapper Pattern:

• Separates domain objects from database
• Mapper handles database operations
• More flexible than Active Record
• Used in Doctrine, Hibernate

4.2 ORM and Query Builders

ORMs (Object-Relational Mappers) and Query Builders simplify database operations by providing abstraction layers.

ORM Benefits:

• Work with objects instead of SQL
• Automatic query generation
• Type safety and validation
• Relationship management
• Migration support

Popular ORMs:

• Sequelize (Node.js): SQL ORM with good TypeScript support
• TypeORM (Node.js): TypeScript-first ORM
• Eloquent (PHP): Laravel's ORM
• Django ORM (Python): Built into Django framework
• ActiveRecord (Ruby): Rails ORM

ORM Example (Sequelize):

// Define model
const User = sequelize.define('User', {
    name: DataTypes.STRING,
    email: DataTypes.STRING
});

// Create
const user = await User.create({
    name: 'John',
    email: 'john@example.com'
});

// Find
const users = await User.findAll({
    where: { name: 'John' }
});

// Update
await user.update({ name: 'Jane' });

// Delete
await user.destroy();

Query Builders:

• Build SQL queries programmatically
• More control than ORMs
• Still provides abstraction
• Examples: Knex.js, QueryBuilder (PHP)

// Query Builder example (Knex.js)
const users = await knex('users')
    .where('age', '>', 18)
    .where('status', 'active')
    .orderBy('name')
    .limit(10);

4.3 Database Connection Management

Proper connection management ensures efficient database usage and prevents resource leaks.

Connection Pooling:

• Reuse database connections
• Improves performance
• Limits concurrent connections
• Prevents connection exhaustion

Connection Configuration:

// Connection pool settings
{
    host: 'localhost',
    user: 'username',
    password: 'password',
    database: 'mydb',
    connectionLimit: 10,
    queueLimit: 0
}

Error Handling:

• Handle connection errors gracefully
• Implement retry logic
• Log database errors
• Provide user-friendly error messages

4.4 Database Migrations

Migrations manage database schema changes in a version-controlled way.

Migration Benefits:

• Version control for database schema
• Reproducible database setup
• Team collaboration
• Rollback capabilities

Migration Example:

// Create users table migration
exports.up = function(knex) {
    return knex.schema.createTable('users', function(table) {
        table.increments('id');
        table.string('name').notNullable();
        table.string('email').unique();
        table.timestamps();
    });
};

exports.down = function(knex) {
    return knex.schema.dropTable('users');
};