A friendly PostgreSQL script runner in the spirit of DbUp.
What is Piggy?
Piggy is a simple command-line tool for managing schema and data changes to PostgreSQL databases. Piggy looks for
.sql files in a directory and applies them to the database in order, using transactions and a change log table to ensure each script runs only once per database.
Piggy is available for Windows, macOS and Linux from the releases page. Download the MSI or archive file for your platform. If your platform of choice isn't listed, please raise an issue here so that we can add it.
To manage database updates with Piggy, write SQL scripts to make changes, rather than applying changes directly.
Organize change scripts on the file system under a script root directory. Name files so that they sort lexicographically in the order in which they need to be executed:
001-create-schema.sql 002-create-users-table.sql 003-...
If the scripts are arranged in subdirectories, these must be ordered by name as well:
v1/ 001-create-schema.sql 002-create-users-table.sql 003-... v2/ 001-rename-users-table.sql
.sql files and generates names like
/v1/001-create-schema.sql using each script's filename relative to the script root. These relative names are checked against the change log table to determine which of them need to be run.
To bring a database up to date, run
piggy up, providing the script root, host, database name, username and password:
piggy up -s <scripts> -h <host> -d <database> -u <username> -p <password>
If the database does not exist, Piggy will create it using sensible defaults. To opt out of this behavior, add the
Over time, as your application grows, create new scripts to move the database forwards - don't edit the existing ones, since they've already been applied and will be ignored by Piggy.
Piggy can be used to update from any previous schema version to the current one: scripts that have already been run on a database are ignored, so only necessary scripts are applied.
For more detailed usage information, run
piggy help up; to see all available commands run
Piggy wraps each script in a transaction that also covers the change log table update. A few DDL statements can't run within a transaction in PostgreSQL - in these cases, add:
-- PIGGY NO TRANSACTION
as the first line of the script.
$var$ syntax for replaced variables:
create table $schema$.users (name varchar(140) not null); insert into $schema$.users (name) values ('$admin$');
Values are inserted using pure text substitution: no escaping or other processing is applied. If no value is supplied for a variable that appears in a script, Piggy will leave the script unchanged (undefined variables will not be replaced with the empty string).
Variables are supplied on the command-line with the
piggy up ... -v schema=myapp -v admin=myuser
The change log is stored in the target database in
piggy log command is used to view applied change scripts.
piggy help to see all available commands, and
piggy help <command> for detailed command help.
> piggy help Usage: piggy <command> [<args>] Available commands are: baseline Add scripts to the change log without running them help Show information about available commands log List change scripts that have been applied to a database pending Determine which scripts will be run in an update up Bring a database up to date --version Print the current executable version Type `piggy help <command>` for detailed help