IntegrateDatabase is a free, independent notebook on the architectural side of databases in applications: keeping dev, staging and production schemas in step, organising the data access layer, and sizing a change before you commit to it.
Read the architecture notes Compare two schemasShort, practical notes on decisions every application with a database has to make.
Dev, staging and production schemas drift apart when changes are applied by hand. Keep every change in versioned migration files, apply them in the same order everywhere, and periodically diff the real schemas – the schema comparison tool helps with that last step.
Route all reads and writes through one layer – an ORM, a query builder or a small repository module – rather than scattering SQL across controllers. It makes schema changes searchable and lets you add caching or logging in one place.
Opening a new database connection per request is slow and can exhaust the server's connection limit. Use the pool built into your driver or a proxy such as PgBouncer, and size it to what the database can handle, not to the number of web workers.
When the application and database deploy separately, make schema changes in two steps: first add (new column, new table) and deploy code that uses it, then remove the old structure in a later release. This “expand and contract” pattern avoids broken deploys.
Indexes should follow the queries your application actually runs. Log slow queries, look at their plans, and add or drop indexes based on evidence. The indexing article explains the trade-offs.
Before a migration, count tables, stored procedures, views and data volume, and decide what downtime is acceptable. The migration estimator turns those numbers into a rough effort range to plan around.
An occasional email when new architecture notes or tools are added.