Databases
Choosing a database, designing tables, migrations, indexes, connection pooling, and backups you have actually restored.
36 posts · page 1 of 2
Zero-Downtime Deploys for a Small App
You don't need Kubernetes to deploy without dropping requests. What actually causes downtime during a deploy — stopping before starting, no health checks, killed requests, and database changes the old code can't handle — and the four practices that fix each one.
What Is CRUD? The Four Operations Behind Almost Every App
Create, Read, Update, Delete: most app features are some combination of these four. What CRUD means, how it maps to SQL and HTTP methods, what a CRUD API looks like, and the details — validation, permissions, pagination — that separate a demo from a real app.
What Is an ORM? Prisma, Drizzle, and Friends Explained
An ORM lets you work with your database using your programming language instead of raw SQL. What ORMs are, what Prisma, Drizzle, and others look like, the schema-and-migration workflow, the N+1 trap, and when writing SQL directly is the better choice.
What Is a Database? A Beginner's Guide for People Building Apps
Almost every app needs somewhere to remember things — users, orders, messages. That's a database. What databases are, how tables and rows work, the difference between SQL and NoSQL, and the three things every beginner should know before real users arrive.
The Transactional Outbox Pattern: Reliable Events Without Dual Writes
Writing to your database and publishing an event can't be made atomic, so one eventually happens without the other. How the transactional outbox fixes it: polling relays vs CDC, ordering, at-least-once delivery, idempotent consumers with an inbox, cleanup, and monitoring.
Supabase vs Firebase: Which Backend for Your First App?
Lovable and Bolt lean on Supabase; many tutorials use Firebase. How the two backends compare — database model, auth, security rules, real-time, pricing shape, and lock-in — and which one fits the app you're building.
Supabase Row-Level Security Explained (for People Who Didn't Write the Policies)
If your app talks to Supabase from the browser, row-level security is the only thing standing between your users' data and anyone who opens the developer tools. What RLS is, how to read the policies your AI tool wrote, the four mistakes that leave data exposed, and how to test it yourself.
How to Stop an AI Agent From Deleting Your Production Database
In July 2025 an AI coding agent deleted a company's production database during a code freeze. It wasn't a freak event — it was the predictable result of giving an agent production credentials. Six controls that make it structurally impossible, not just unlikely.
SQL vs NoSQL: Which Database Should a Beginner Choose?
Postgres or MongoDB? Supabase or Firebase? The real difference between SQL and NoSQL databases, what 'relational' and 'document' mean, where each shines, the myths about scale and flexibility, and why most new apps should start with SQL.
SQL Injection Explained: The Classic Attack and the One-Line Fix
SQL injection lets an attacker rewrite your database queries by typing into a form. How it works with a simple example, what damage it can do, why parameterized queries and ORMs prevent it, the places AI-generated code still gets it wrong, and how to check your app.
SQL for Beginners: The Queries You Need to Understand Your App's Data
You don't need to become a database expert to read your own data. The handful of SQL queries — SELECT, WHERE, ORDER BY, COUNT, JOIN — that let you answer real questions about your app, plus the two commands to be very careful with.
Soft Deletes and Audit Logs: Keeping History Without Making a Mess
Deleting rows is irreversible; hiding them has costs too. When to use soft deletes, how to implement them without leaking 'deleted' data (partial indexes, unique constraints, views, RLS), the privacy tension with erasure requests, and how to build an audit log with triggers or application events.
Redis: When a Small App Actually Needs It (and When Postgres Is Enough)
Redis shows up in every architecture diagram, and AI tools add it by reflex. It's excellent at a few specific jobs — caching, rate limiting, ephemeral state, pub/sub — and unnecessary for many small apps. What it's for, what it isn't, and how to use it without losing data you cared about.
Postgres as a Job Queue: FOR UPDATE SKIP LOCKED Done Properly
You may not need Redis or a broker for background jobs. How SKIP LOCKED makes Postgres a safe concurrent queue: claim/lease/ack, visibility timeouts and crash recovery, retries with backoff, LISTEN/NOTIFY, transactional enqueue, indexing, bloat, and its limits.
Postgres Point-in-Time Recovery: WAL Archiving, Base Backups, and Restore Drills
A nightly dump can lose a day of data. Point-in-time recovery restores to the second before the bad migration. How WAL archiving and base backups combine, the settings that matter, recovery targets and timelines, pgBackRest and WAL-G, and restore drills.
Postgres Migrations on Large Tables Without Downtime
The migration that took 40 ms in staging locked production for minutes. Postgres lock levels and the lock queue, lock_timeout with retries, which ALTER TABLE operations rewrite, CREATE INDEX CONCURRENTLY, NOT VALID constraints, safe NOT NULL, and batched backfills.
Postgres Major Version Upgrades: pg_upgrade, Logical Replication, and Minimal Downtime
Major versions change the on-disk format, so upgrading PostgreSQL isn't a package update. Dump/restore vs pg_upgrade (copy, link, clone) vs logical replication cutover; extension and collation pitfalls; sequences and DDL gaps in logical replication; statistics after upgrade; and a rehearsed runbook.
Postgres JSONB: When to Use It and When to Use Columns
JSONB lets you store flexible documents inside a relational database — and it's easy to overuse. When JSONB is the right tool, the operators you need, GIN vs expression indexes, updating nested values, validating shape with CHECK constraints, and the signs a JSONB field should become real columns.
Postgres Full-Text Search: Good Enough Before You Reach for Elasticsearch
ILIKE '%term%' doesn't scale and doesn't rank. How PostgreSQL full-text search works — tsvector, tsquery, GIN indexes, generated columns, websearch_to_tsquery, ranking, highlighting — plus pg_trgm for typo tolerance, and the point where a dedicated search engine is worth it.
Postgres Connection Pooling Explained: Why 'Too Many Connections' Happens and How to Fix It
"FATAL: sorry, too many clients already" usually appears the day an app gets popular. Why Postgres connections are expensive, how application pools and PgBouncer work, the transaction-mode caveats that break things, and how to size a pool without guessing.
How to Back Up a Postgres Database — and Prove the Backup Works
A backup you've never restored is a guess. The three kinds of Postgres backup, how to take each one, where to store them, and a restore drill you can run in fifteen minutes to find out whether yours actually work.
Password Hashing Explained: Why You Never Store Passwords
A well-built app doesn't know your password — it stores a hash. What hashing is, why fast hashes like MD5 and SHA-256 are wrong for passwords, what salts do, why bcrypt and Argon2 exist, and how to check that your AI-built app got it right.
The N+1 Query Problem: How to Spot It and Fix It
The most common performance bug in ORM-based apps: one query for a list, then one more per item. How N+1 happens in Prisma, Drizzle, Django, Rails, and GraphQL resolvers, how to detect it from logs and pg_stat_statements, and the fixes — eager loading, batching, joins, and DataLoader.
Multi-Tenant SaaS on Postgres: Shared Schema + RLS vs Schema-per-Tenant vs Database-per-Tenant
The tenancy model is the hardest SaaS decision to reverse. Shared schema with RLS vs schema-per-tenant vs database-per-tenant — isolation, migrations, pooling, per-tenant restore — plus the owner-bypass, pooling, and foreign-key traps that silently break row-level security.