Schema design, query optimization, migrations, and connection management for SQL and NoSQL databases.
Follow these principles:
created_at and updated_at timestamps on all tablesuser_accounts, not UserAccount)CREATE TABLE users (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(255) NOT NULL UNIQUE,
name VARCHAR(100) NOT NULL,
role ENUM('user', 'admin', 'moderator') DEFAULT 'user',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_email (email),
INDEX idx_role (role)
);
CREATE TABLE posts (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
user_id BIGINT NOT NULL,
title VARCHAR(255) NOT NULL,
content TEXT,
status ENUM('draft', 'published', 'archived') DEFAULT 'draft',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
INDEX idx_user_status (user_id, status)
);
SELECT * in productionEXPLAIN to analyze query execution plansLIMIT and cursor-based pagination for large datasets-- Instead of N+1 queries
SELECT p.*, u.name as author_name
FROM posts p
JOIN users u ON p.user_id = u.id
WHERE p.status = 'published'
ORDER BY p.created_at DESC
LIMIT 20;
// schema.prisma
model User {
id Int @id @default(autoincrement())
email String @unique
posts Post[]
createdAt DateTime @default(now())
}
model Post {
id Int @id @default(autoincrement())
title String
author User @relation(fields: [userId], references: [id])
userId Int
createdAt DateTime @default(now())
}
// Query with relations
const usersWithPosts = await prisma.user.findMany({
include: { posts: { where: { status: "published" }, take: 5 } },
});
npx prisma migrate dev --name add_posts_table
npx prisma migrate deploy # production
import { Pool } from 'pg';
const pool = new Pool({ max: 20, idleTimeoutMillis: 30000 });
const result = await pool.query('SELECT * FROM users WHERE id = $1', [userId]);