Not an Object Relational Mapper
npm install @hubbit86/db-clisql folder).
db init [version]
`
When in production mode, it creates the DB and fails if it already exists.
In development mode it creates/re-creates the DB and initializes it with the default data.
Optionally you can specify a version number or alias. By default the version "next" is used in development, and the version alias "latest" is used for production.
`
db migrate [version] [...options]
`
Migrates the DB from the current version to the specified version. As with init, by default that is "next" for development mode and "latest" for production.
If the specified version precedes the current one, a backwards migration will be performed.
If no migration path is available, it fails.
`
db release [version]
`
Creates a new release version. This involves renaming "next" to the new version and creating a new clean "next" folder.
Additionally it copies the content of the file sql/preamble.sql to the YYYYMMDD.sql file that will be automatically created in the new next folder.
`
db check [version]
`
`
db generate [version]
`
Docker container for Postgres
`
docker pull postgres:15
docker run --name your-db-name -p 5432:5432 -e POSTGRES_PASSWORD=0000 -d postgres:15
`
Migration strategy
A migration allows you to move from version A to version B of a DB without losing data. You can tell the system that a migration exists by creating a file A.sql in B's folder. Typically migrations are created from latest to next but they can also skip versions or even go backwards. E.g: Create an empty YYYYMMDD.sql file in sql/next/ directory.
Each migration step generally works as follows:
1. Rename all schemas with a prefix "o_", e.g: "o_public".
2. Create and initialize all the schemas for the new version.
3. Copy data from e.g. "o_public" to "public", doing nescesary conversions in the process.
4. Delete the old schemas.
Step 3 will start by executing the A.sql script. For tables untouched by the script or the insert.sql files. The copy will be automated using INSERT INTO "B"."T" SELECT ... FROM "A"."T". It will match by column name.
This means that data from tables affected by insert.sql` scripts or by the migration script will not by copied over automatically. Copying can be automated in the following cases: