minimal sql change scripter in bash.
This tool helps you write database change scripts (aka "sql evolutions" or "db migrations"). It works with postgresql but can be easily adapted.
All you need is a version table with a version field and then your sql scripts in <version>.sql files.
- have a
versiontable with aversioninteger column (will be a scalar)create table version (version integer primary key);insert into version values (0); - write your sql evolutions in named files
<version>.sqlstarting from version1. - format of the sql scripts: An
-- UPline followed by the sql statements, then a-- DOWNline followed by the "opposite" sql statements. - run sqlevolve where your scripts are; it assumes the
<version>.sqlfiles are in your cwd:
sqlevolve [version]
# version: the target script version to run up or down to
# If no version is given, the target is the highest <version>.sql found
-- 34.sql
-- UP
create table foo ( name varchar(32) primary key );
create table bar ( id serial primary key );
-- DOWN
drop table foo;
drop table bar;
If you want to configure the arguments read by psql, drop a sqlevolveconf file along with your migrations.
It supports two variables, dbname and psqlargs.
# sqlevolveconf
dbname='mydb'
psqlargs='-U my-favourite-user -h 127.0.0.1'
cd my-proj/migrations
# use psqlargs to specify the user and pass the password in a variable
PGPASSWORD='my-favourite-password' ../sqlevolve
# or use PEER authentication
sudo -u username ../sqlevolve