Re: PostgreSQL DB in prod, test, debug

On 2/14/24 02:38, Erik Wienhold wrote:
> On 2024-02-14 10:59 +0100, Simon Connah wrote:

>
> The database schema should be defined by migration scripts that you also
> check into version control with the rest of your application sources.
> Some people also prefer a separate repository just for the database
> schema, depending on how tightly coupled database and application are,
> or if there are other applications relying on the database schema.
>
> I use Sqitch[1] which works well if you want to track an existing
> database schema. Alembic is popular in Python but I don't know how it
> works with an existing schema because Alembic migrations are usually not
> written in plain SQL.

Just to be clear you can start from scratch with Sqitch. The only thing
that needs to be done outside of it is the original CREATE DATABASE
statement. After that you can build the contents of the database from
the beginning. At any point you can move forward(deploy) or
backward(revert). For your use case the important feature is target,
where each target is a different instance of the database. In your case
prod, test and debug. This allow you to make changes in test/debug and
when they are verified good deploy them to prod.

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com