SQLite in Development, PostgreSQL in Production: What Breaks
Type affinity, limited ALTER TABLE and foreign keys that are off by default: what breaks when you develop on SQLite and deploy to PostgreSQL, and how to avoid it.
11 articles
Databases
PostgreSQL, MySQL, MariaDB, SQL Server, Oracle and SQLite: schema design, types, keys and engine differences.
Articles in this category
SQL Server Schemas: dbo, Ownership and How Migration Tools Handle Them
What a schema is in SQL Server, why everything lands in dbo, how default schemas work, and how Flyway, Liquibase, EF Core, DbUp and FluentMigrator target schemas.
Why Oracle Shows Your Tables in UPPERCASE: Case Folding Explained
Oracle folds unquoted names to upper case, PostgreSQL to lower case, MySQL depends on a setting, SQL Server on collation. The rules, and how to stay out of trouble.
Choosing Column Types: VARCHAR vs TEXT, TIMESTAMPTZ, NUMERIC vs FLOAT
A practical guide to column types in PostgreSQL, MySQL, SQL Server, Oracle and SQLite: strings, timestamps, money, booleans, JSON, enums and what diagrams report.
Transactional DDL: Why PostgreSQL Rolls Back Failed Migrations
Which engines run DDL in transactions (PostgreSQL, SQL Server, SQLite), which commit implicitly (MySQL, MariaDB, Oracle), and how to write migrations for both.
PostgreSQL vs MySQL vs MariaDB: How to Choose a Database
A practical comparison of PostgreSQL, MySQL and MariaDB: data types, SQL conformance, transactional DDL, replication, hosting and what each means for migrations.
Foreign Keys Done Right: ON DELETE Rules, Indexing and Pitfalls
ON DELETE actions explained, why PostgreSQL leaves FK columns unindexed while InnoDB indexes them, deferrable constraints and NOT VALID on big tables.
Primary Keys: Serial, Identity, UUID or ULID?
Integer, UUID or ULID primary keys compared on size, index locality, guessability and generation, with engine specifics and a recommendation per situation.
Many-to-Many Relationships: Designing Join Tables the Right Way
Join table design: naming, composite key or surrogate id, extra columns, indexes both ways and ON DELETE rules, with SQL, Django, Prisma and Rails examples.
Database Normalization Explained with a Real Schema (1NF to BCNF)
Normalization walked through on a vet clinic spreadsheet: 1NF, 2NF, 3NF and BCNF explained in plain words, ending in the PetClinic tables, plus when to denormalize.
Database Naming Conventions: Tables, Columns, Keys and Indexes
A practical naming convention for tables, columns, keys, constraints and indexes, plus the case-folding rules of PostgreSQL, Oracle, MySQL and SQL Server.
Draw your schema in the next five minutes
One HTTP call with your migration files, one poll, one SVG. Free while we build it.