ORMs and Query Builders¶

// schema.prisma
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  role      Role     @default(USER)
  posts     Post[]
  createdAt DateTime @default(now())
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
}

enum Role {
  USER
  ADMIN
}

// Generated Prisma Client
const { PrismaClient } = require('@prisma/client')
const prisma = new PrismaClient()

// Create with relation
const user = await prisma.user.create({
  data: {
    email: 'test@example.com',
    name: 'Mahmoud',
    posts: {
      create: { title: 'My first post' }
    }
  },
  include: { posts: true }
})

// Read with filtering and pagination
const users = await prisma.user.findMany({
  where: {
    role: 'ADMIN',
    email: { contains: '@example.com' }
  },
  include: { posts: true },
  skip: 0,
  take: 10,
  orderBy: { createdAt: 'desc' }
})

// Update
await prisma.user.update({
  where: { id: 1 },
  data: { name: 'Ali' }
})

// Delete with cascade
// Defined in schema - Prisma handles cascade automatically
await prisma.user.delete({ where: { id: 1 } })

# Create a migration from schema changes
npx prisma migrate dev --name add_user_role

# Apply to production
npx prisma migrate deploy

# Generate client after schema changes
npx prisma generate

# Reset database (dev only)
npx prisma migrate reset

const { Sequelize, DataTypes } = require('sequelize')
const sequelize = new Sequelize(process.env.DATABASE_URL)

const User = sequelize.define('User', {
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  email: {
    type: DataTypes.STRING,
    allowNull: false,
    unique: true,
    validate: { isEmail: true }
  },
  name: DataTypes.STRING,
  role: {
    type: DataTypes.ENUM('user', 'admin'),
    defaultValue: 'user'
  }
})

const Post = sequelize.define('Post', {
  title: { type: DataTypes.STRING, allowNull: false },
  content: DataTypes.TEXT,
  published: { type: DataTypes.BOOLEAN, defaultValue: false }
})

// Associations
User.hasMany(Post, { foreignKey: 'authorId' })
Post.belongsTo(User, { foreignKey: 'authorId' })

// Sync (dev only - use migrations in prod)
await sequelize.sync()

// Query
const users = await User.findAll({
  where: { role: 'admin' },
  include: [Post],
  order: [['createdAt', 'DESC']]
})

const knex = require('knex')({
  client: 'pg',
  connection: process.env.DATABASE_URL,
  pool: { min: 2, max: 10 }
})

// SELECT
const users = await knex('users')
  .join('posts', 'users.id', 'posts.author_id')
  .where('users.role', 'admin')
  .select('users.*', 'posts.title')
  .limit(10)

// INSERT
const [id] = await knex('users').insert({
  email: 'test@example.com',
  name: 'Mahmoud'
}).returning('id')

// UPDATE
await knex('users')
  .where({ id: 1 })
  .update({ name: 'Ali' })

// Raw SQL when you need it
const results = await knex.raw(`
  SELECT * FROM users
  WHERE email = ?
  AND role = ?
`, [email, role])

import { pgTable, serial, text, timestamp } from 'drizzle-orm/pg-core'
import { drizzle } from 'drizzle-orm/node-postgres'
import { Pool } from 'pg'

const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: text('email').notNull().unique(),
  name: text('name'),
  createdAt: timestamp('created_at').defaultNow()
})

const pool = new Pool({ connectionString: process.env.DATABASE_URL })
const db = drizzle(pool)

// Type-safe queries
const result = await db.select()
  .from(users)
  .where(eq(users.email, 'test@example.com'))

const inserted = await db.insert(users)
  .values({ email: 'test@example.com', name: 'Mahmoud' })
  .returning()

await db.update(users)
  .set({ name: 'Ali' })
  .where(eq(users.id, 1))

// BAD - N+1 queries
const posts = await Post.findAll()  // 1 query
for (const post of posts) {
  const author = await User.findByPk(post.authorId)  // N queries
}

// GOOD - eager loading (Sequelize)
const posts = await Post.findAll({
  include: [User]  // 1 query with JOIN
})

// GOOD - eager loading (Prisma)
const posts = await prisma.post.findMany({
  include: { author: true }  // 1 query or optimized batch
})

// GOOD - batch loading (raw)
const posts = await knex('posts').select()
const authorIds = [...new Set(posts(p => p.authorId))]
const authors = await knex('users').whereIn('id', authorIds)

// Prisma migration
npx prisma migrate dev --name add_user_role

// Knex migration
npx knex migrate:make add_user_role

// Sequelize migration
npx sequelize migration:generate --name add-user-role

// Generated Knex migration file
exports.up = function(knex) {
  return knex.schema.table('users', (table) => {
    table.enu('role', ['user', 'admin']).defaultTo('user')
  })
}

exports.down = function(knex) {
  return knex.schema.table('users', (table) => {
    table.dropColumn('role')
  })
}