Difficulty: Intermediate
Good database design patterns are essential for building maintainable, scalable Node.js applications. These patterns address common challenges like managing connections efficiently, evolving your schema safely, populating initial data, and organizing your data access code. Understanding these patterns helps you build applications that are easy to develop, deploy, and maintain across different environments.
Connection pooling is a fundamental pattern for database performance. Every database connection consumes server resources (memory, file descriptors), and creating connections is slow (TCP handshake, authentication). A connection pool maintains a fixed number of pre-established connections and lends them out to incoming requests. When a request finishes, the connection is returned to the pool for reuse. The pool size should match your expected concurrency - too few connections create bottlenecks, too many overwhelm the database server. A typical starting point is 5-20 connections per application instance.
Database migrations provide version control for your schema. Instead of manually altering tables in production, you write migration files that describe each change (add column, create table, add index). Migrations run in order and are tracked by the migration tool, so each change is applied exactly once. This ensures every environment (development, staging, production) has the same schema. Both Prisma (prisma migrate) and standalone tools like knex migrations follow this approach. Always review generated migration SQL before applying to production.
Seeders populate your database with initial or test data. They are separate from migrations because they handle data, not schema. Common use cases include creating admin accounts, populating lookup tables (countries, categories), and generating fake data for development. Good seeders are idempotent - running them multiple times does not create duplicates. Use upsert operations (insert or update) and check for existing records before creating new ones.
Environment-based configuration ensures your application connects to the right database in each environment. Development, testing, staging, and production each have their own database with different connection strings, pool sizes, and logging levels. The `dotenv` package loads environment variables from .env files, while libraries like `config` or simple conditional logic select the appropriate settings. Never commit database credentials to source control - use .env files (git-ignored) and environment variables in deployment platforms.
require('dotenv').config();
const dbConfig = {
development: {
connectionString: process.env.DATABASE_URL || 'postgresql://postgres:postgres@localhost:5432/myapp_dev',
pool: { min: 2, max: 10 },
logging: true
},
test: {
connectionString: process.env.TEST_DATABASE_URL || 'postgresql://postgres:postgres@localhost:5432/myapp_test',
pool: { min: 1, max: 5 },
logging: false
},
production: {
connectionString: process.env.DATABASE_URL,
pool: { min: 5, max: 20 },
logging: false,
ssl: { rejectUnauthorized: false }
}
};
const env = process.env.NODE_ENV || 'development';
const config = dbConfig[env];
console.log('Environment:', env);
console.log('Pool size:', config.pool);
console.log('Logging:', config.logging);
console.log('Has SSL:', !!config.ssl);Each environment has different settings. Development uses a local database with logging enabled. Tests use a separate database with minimal connections. Production uses SSL, more connections, and reads the URL from environment variables only (no fallback).
const { PrismaClient } = require('@prisma/client');
const bcrypt = require('bcryptjs');
async function seedAdmin(prisma) {
const adminEmail = 'admin@myapp.com';
// Upsert: create if not exists, update if exists
const admin = await prisma.user.upsert({
where: { email: adminEmail },
update: { name: 'System Admin' }, // Update existing
create: {
email: adminEmail,
name: 'System Admin',
password: await bcrypt.hash('admin123', 10),
role: 'ADMIN'
}
});
console.log('Admin seeded:', admin.email, '(id:', admin.id + ')');
}
async function seedCategories(prisma) {
const categories = ['Engineering', 'Design', 'Marketing', 'Sales', 'Support'];
for (const name of categories) {
await prisma.category.upsert({
where: { name },
update: {},
create: { name }
});
}
console.log('Categories seeded:', categories.length);
}
async function main() {
const prisma = new PrismaClient();
try {
await seedAdmin(prisma);
await seedCategories(prisma);
console.log('Seeding complete');
} catch (error) {
console.error('Seeding failed:', error.message);
} finally {
await prisma.$disconnect();
}
}
console.log('Seeder functions defined');
console.log('Uses upsert for idempotency');Upsert ensures seeders are idempotent - they can run multiple times without creating duplicates. The where clause checks for existing records, update modifies them if found, and create inserts them if not. The main function handles the connection lifecycle.
// Encapsulate data access logic in repository classes
class UserRepository {
constructor(prisma) {
this.prisma = prisma;
}
async findById(id) {
return this.prisma.user.findUnique({ where: { id } });
}
async findByEmail(email) {
return this.prisma.user.findUnique({ where: { email } });
}
async findAll({ page = 1, pageSize = 20, role } = {}) {
const where = role ? { role } : {};
const [users, total] = await Promise.all([
this.prisma.user.findMany({
where,
skip: (page - 1) * pageSize,
take: pageSize,
orderBy: { createdAt: 'desc' },
select: { id: true, name: true, email: true, role: true }
}),
this.prisma.user.count({ where })
]);
return { users, total, pages: Math.ceil(total / pageSize) };
}
async create(data) {
return this.prisma.user.create({ data });
}
async update(id, data) {
return this.prisma.user.update({ where: { id }, data });
}
async softDelete(id) {
return this.prisma.user.update({
where: { id },
data: { isActive: false, deletedAt: new Date() }
});
}
}
// Usage in a service:
// const userRepo = new UserRepository(prisma);
// const { users, total } = await userRepo.findAll({ page: 2, role: 'STUDENT' });
console.log('UserRepository methods:', Object.getOwnPropertyNames(UserRepository.prototype).filter(m => m !== 'constructor'));The repository pattern encapsulates all database queries for a model in one class. This keeps controllers thin, makes queries reusable, and makes it easy to switch databases or add caching. The softDelete method marks records as inactive instead of removing them - useful for data retention and audit trails.
connection pooling, migrations, seeders, environment config, repository pattern, soft deletes