Postgres for local dev: one container, one Makefile
Most local database setups fail in one of two ways. Either everyone shares a staging database and steps on each other's data, or the repo ships a 200-line compose file with six services that nobody fully understands. You need neither. One Postgres container, plain SQL migrations, and a reset command that runs in seconds will carry you a long way. The container # docker-compose.yml services : db :…
Setting up a local PostgreSQL database doesn't need to be complicated. Instead of using a shared staging database or a complex compose file with multiple services, you can use a single Postgres container with plain SQL migrations and a quick reset command. This approach has several advantages.
First, the container is simple. Use the official PostgreSQL image and pin the major version to avoid surprises during deployment. Customize the configuration for speed, as your local data is disposable. Mount the data volume and set up a health check to ensure the container is ready.
Second, manage migrations through plain SQL files. Number them sequentially and write them in plain SQL without using any ORM-generated diffs or DSLs. This makes it easy to understand the schema by reading the files directly. The reset command is straightforward: bring up the database, drop the development database, create a new one, apply the migrations, and run the seed data.
Third, use a single variable in your `.env` file to store the database connection URL. This simplifies the configuration for your application. Create a set of SQL files for migrations, a `seed.sql` file for initial data, and a `db-reset` command to perform the reset process.
Fourth, set up tests against a separate template database. Clone this database for each test run to ensure isolation and avoid shared state or mocks. Clone the template database once per test run after the migrations are applied, then drop it after the tests are complete.
However, there are some caveats. If you need extensions, consider swapping the PostgreSQL image for one that includes the desired extensions. Performance issues may arise with small datasets, so keep a script to generate a volume with test data and analyze the execution plan before deploying heavy queries. If your production environment uses managed PostgreSQL with restrictions on superuser access or certain extensions, adapt your migrations accordingly.
If you're working on multiple projects, give each its own port and database URL in the compose file.
In summary, a fast reset command and versioned PostgreSQL container can prevent common local development issues. A template database ensures test isolation. This approach, combined with a simple Makefile, can get you up and running in just a few minutes. If your project requires more advanced features, consider exploring other solutions, such as tinbase.dev or RapidNative.
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.
