ORMObject-Relational Mapping (ORMs)
Writing raw SQL inside your JavaScript or Python code works. But it becomes painful quickly. You're constantly writing long strings of SQL, manually mapping result rows to objects, and concatenating parameters carefully to avoid injection. An ORM (Object-Relational Mapper) solves all of this by letting you interact with your database using the programming language you already know — no SQL strings scattered through your codebase.
What an ORM Actually Does
An ORM is a library that sits between your application code and the database. It maps your database tables to Classes (in object-oriented languages), and individual rows to instances of those classes. Instead of writing SQL, you call methods on your objects.
Without ORM — raw SQLconst result = await db.query(
'SELECT * FROM users WHERE email = $1 AND is_verified = $2',
['alice@example.com', true]
);
const user = result.rows[0];
With ORM (Prisma)const user = await prisma.user.findFirst({
where: { email: 'alice@example.com', isVerified: true }
});
The ORM generates the SQL for you, handles parameter escaping (preventing SQL injection automatically), and returns a proper JavaScript object instead of a raw row array.
Prisma — The Modern ORM for Node.js
Prisma is the most popular ORM for JavaScript/TypeScript applications. Its defining feature is a schema file (schema.prisma) that serves as the single source of truth for your database structure, auto-generating TypeScript types so your IDE knows exactly what shape every database result will have.
npm install prisma @prisma/client
npx prisma init
Edit prisma/schema.prisma to define your data models:
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
model User {
id Int @id @default(autoincrement())
username String @unique
email String @unique
age Int?
isVerified Boolean @default(false)
balance Decimal @default(0.00)
createdAt DateTime @default(now())
posts Post[]
}
model Post {
id Int @id @default(autoincrement())
title String
body String?
published Boolean @default(false)
createdAt DateTime @default(now())
userId Int
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
}
Now apply this schema to your actual database:
npx prisma migrate dev --name init
CRUD Operations with Prisma
const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();
// CREATE — Add a new user
const newUser = await prisma.user.create({
data: {
username: 'alice',
email: 'alice@example.com',
age: 28
}
});
console.log(newUser.id);
// READ — Find a user by email
const user = await prisma.user.findUnique({
where: { email: 'alice@example.com' }
});
// READ — Get multiple users with filtering and pagination
const users = await prisma.user.findMany({
where: {
age: { gt: 25 },
isVerified: true
},
orderBy: { createdAt: 'desc' },
skip: 0,
take: 10,
select: {
id: true,
username: true,
email: true
}
});
// UPDATE — Modify an existing user
const updated = await prisma.user.update({
where: { id: 1 },
data: { age: 29, isVerified: true }
});
// DELETE — Remove a user
await prisma.user.delete({
where: { id: 1 }
});
Querying Relationships with Prisma
One of the biggest wins of an ORM is fetching related data. With Prisma's include or select, you get joined data as a clean nested JavaScript object — no SQL JOIN syntax required:
// Fetch a user AND include all their posts in the same query
const userWithPosts = await prisma.user.findUnique({
where: { id: 1 },
include: {
posts: {
where: { published: true },
orderBy: { createdAt: 'desc' },
take: 5
}
}
});
console.log(userWithPosts);
// {
// id: 1,
// username: 'alice',
// email: 'alice@example.com',
// posts: [
// { id: 10, title: 'My Latest Post', published: true, ... },
// ]
// }
// Create a user AND their first post in one atomic operation
const result = await prisma.user.create({
data: {
username: 'bob',
email: 'bob@example.com',
posts: {
create: {
title: 'Hello World',
body: 'My first post!'
}
}
},
include: { posts: true }
});
Transactions
A transaction is a group of database operations that must ALL succeed or ALL fail together. If you are transferring money between two bank accounts, you need to debit one account AND credit the other. If the credit fails after the debit, money disappears. A transaction prevents this by wrapping both operations atomically.
// Transfer money atomically
// If EITHER operation fails, BOTH are rolled back
const result = await prisma.$transaction(async (tx) => {
// Debit sender
const sender = await tx.user.update({
where: { id: fromUserId },
data: { balance: { decrement: amount } }
});
// Check for overdraft
if (sender.balance < 0) {
throw new Error('Insufficient funds'); // Triggers rollback of BOTH operations
}
// Credit receiver
const receiver = await tx.user.update({
where: { id: toUserId },
data: { balance: { increment: amount } }
});
return { sender, receiver };
});
// If the transaction threw, nothing was committed to the database
SQLAlchemy — The Python ORM
In Python, SQLAlchemy is the most comprehensive ORM. It supports both a Core layer (SQL expression language) and an ORM layer. When combined with Flask-SQLAlchemy or FastAPI, it provides elegant database integration:
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://user:pass@localhost/mydb'
db = SQLAlchemy(app)
# Define models as Python classes
class User(db.Model):
__tablename__ = 'users'
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(50), nullable=False, unique=True)
email = db.Column(db.String(255), nullable=False, unique=True)
age = db.Column(db.Integer)
created_at = db.Column(db.DateTime, default=datetime.utcnow)
# Relationship: one-to-many
posts = db.relationship('Post', backref='author', lazy=True)
class Post(db.Model):
__tablename__ = 'posts'
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(255), nullable=False)
body = db.Column(db.Text)
user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
# CRUD with SQLAlchemy
@app.route('/users', methods=['POST'])
def create_user():
data = request.json
user = User(username=data['username'], email=data['email'])
db.session.add(user)
db.session.commit()
return jsonify({'id': user.id}), 201
@app.route('/users/')
def get_user(user_id):
user = User.query.get_or_404(user_id)
return jsonify({'id': user.id, 'username': user.username})