Databases
Choosing a database, designing tables, migrations, indexes, connection pooling, and backups you have actually restored.
112 posts · page 3 of 5
NULL in SQL Explained: Why = NULL Never Works
NULL in SQL means 'unknown', not zero or an empty string — which is why WHERE column = NULL returns nothing. How IS NULL, COALESCE, NULLIF and IS DISTINCT FROM work, how NULL affects counts, sums, NOT IN and unique constraints, and when to use NOT NULL.
"new row violates row-level security policy" in Supabase: How to Fix It
Supabase (Postgres) blocked an insert or update because no RLS policy allows it. Why it happens, how to write an INSERT policy with WITH CHECK, the user_id default trick, the hidden SELECT-after-insert problem, storage uploads, and why you must never 'fix' it by disabling RLS.
Neon vs Supabase: Which Postgres Platform Should You Use?
Neon is serverless Postgres with branching and scale-to-zero; Supabase is Postgres plus auth, storage, realtime and APIs. How they differ, free tier limits, pricing, cold starts and pausing, and which fits an AI-built app.
How to Import a CSV Into PostgreSQL (COPY, \copy, and GUI Tools)
Four ways to load a CSV into Postgres: the \copy command in psql, server-side COPY, a GUI like pgAdmin or DBeaver, and your platform's dashboard. Creating the table first, headers, delimiters, dates and empty values, and fixing the errors you'll hit.
ECONNREFUSED 127.0.0.1:5432: Why Your App Can't Reach the Database
"connect ECONNREFUSED 127.0.0.1:5432" means nothing was listening where your app tried to connect. The causes: database not running, wrong host inside Docker, wrong port, listening on the wrong address, or a firewall. How to find which, plus the related timeout and authentication errors.
"duplicate key value violates unique constraint": Causes and Fixes
Postgres refused an insert because a value that must be unique already exists. The two very different cases — a real duplicate (like an email that's taken) and an out-of-sync ID sequence after importing data — and how to fix each, including ON CONFLICT and resetting sequences.
Distributed Locks: Redis, Postgres and Why Fencing Tokens Matter
A lock across multiple processes or servers is harder than it looks: processes pause, leases expire and two holders can both believe they own the lock. Redis SET NX PX done right, the Redlock debate, Postgres advisory locks, fencing tokens, and when you don't need a distributed lock at all.
Database Relationships Explained: One-to-One, One-to-Many, Many-to-Many
How tables connect in a relational database: one-to-many with a foreign key, many-to-many with a join table, and one-to-one. With SQL for each, how to query them with joins, how ORMs like Prisma express them, and the design mistakes AI tools commonly make.
Database Normalization Explained: 1NF, 2NF, 3NF in Plain English
Normalization means storing each fact once, so updates can't leave your data contradicting itself. What 1NF, 2NF and 3NF actually require, with one example table taken through each step, the anomalies they prevent, and when deliberately denormalizing is the right call.
The 3-2-1 Backup Rule for Apps and Databases
Three copies of your data, on two kinds of storage, one off-site — and the modern additions: one immutable copy and zero restore errors. What counts as a copy, applying it to a Postgres-backed app, retention, encryption, ransomware and AI-agent mistakes, and testing restores.
What Is Supabase? A Beginner's Guide to the Backend Behind Many AI-Built Apps
Supabase gives your app a Postgres database, logins, file storage and serverless functions from one dashboard. What each part does, how the publishable and secret keys work, why row-level security matters, free-plan limits, and when to use something else.
What Are Embeddings? How AI Turns Meaning Into Numbers
An embedding is a list of numbers that captures what a piece of text means, so a computer can find similar things. How embeddings work, what they're used for (search, RAG, recommendations), how to store them, and practical tips on models, dimensions and cost.
How to View Your Postgres Database: psql, GUIs, and VS Code
Want to see what's actually in your app's database? How to look inside Postgres with psql, desktop tools like pgAdmin, DBeaver and TablePlus, VS Code extensions, and your ORM's studio — plus how to connect safely to a database on a server.
UUID vs Auto-Increment IDs: Which Primary Key Should You Use?
Sequential integers or UUIDs for your primary keys? The real trade-offs — size, index performance, guessability, merging data, leaking business metrics — why UUIDv7 changes the answer, Postgres 18's uuidv7(), and the common hybrid of internal IDs plus public IDs.
Is SQLite Good Enough for Production? When It Works and When It Doesn't
SQLite now runs real production apps. When a single-file database is a great choice, the settings you must change (WAL mode, busy timeout, foreign keys), backups with Litestream, the single-writer limit, and the hosting setups where SQLite will lose your data.
SQL Joins Explained Simply: INNER, LEFT, RIGHT, and FULL JOIN
A beginner's guide to SQL joins with one small example database: what a join does, INNER JOIN vs LEFT JOIN with real output, RIGHT and FULL joins, joining three tables, and the classic mistakes — duplicated rows and WHERE clauses that cancel a LEFT JOIN.
Role-Based Access Control (RBAC) for Your App: A Practical Guide
How to add roles and permissions to a web app without making a mess: roles vs permissions, a simple database schema, checking permissions on the server, multi-tenant roles per organisation, enforcing in the UI and the API, and testing it.
Primary Key vs Foreign Key: What's the Difference?
Primary keys identify each row; foreign keys link rows between tables. What each one does, examples in SQL, composite and unique keys, what ON DELETE CASCADE really means, and why AI-generated schemas sometimes skip foreign keys — and why you shouldn't.
PostgreSQL vs MySQL: Which Database Should You Choose?
Postgres and MySQL are the two most popular open-source databases. How they differ on features, JSON, extensions, performance, hosting and ecosystem — and why most new apps (and AI app builders) default to Postgres.
Postgres VACUUM and Table Bloat: How It Works and How to Keep It Under Control
Why Postgres tables bloat, what VACUUM and autovacuum actually do, tuning autovacuum for large tables, what blocks cleanup (long transactions, replication slots), transaction ID wraparound, and how to reclaim space without VACUUM FULL's exclusive lock.
Postgres Table Partitioning: When It Helps, When It Hurts, and How to Do It
Declarative partitioning in PostgreSQL: range, list and hash partitions, partition pruning, primary key and unique constraint rules, dropping old data instantly, automating new partitions, converting an existing table, and the cases where partitioning makes things slower.
Reading Postgres EXPLAIN ANALYZE: A Practical Guide to Query Plans
How to read a Postgres query plan: EXPLAIN vs EXPLAIN ANALYZE, BUFFERS, costs vs actual times, loops, scan and join types, spotting bad row estimates, sorts spilling to disk — plus pg_stat_statements and auto_explain for finding the queries worth fixing.
Postgres "Deadlock Detected": Why It Happens and How to Prevent It
ERROR: deadlock detected. How Postgres deadlocks happen, how to read the log detail, the common causes — inconsistent lock ordering, batch updates, foreign keys, upserts — and the fixes: consistent ordering, shorter transactions, explicit locking, and safe retries.
Postgres Connection Strings Explained: Format, Examples, and Common Errors
What every part of a PostgreSQL connection string (DATABASE_URL) means, how to write one, special characters in passwords, sslmode options, pooled vs direct connections (including Supabase's ports), and how to fix the errors people hit most.