Tiny stand-alone database upgrade/downgrade manager with dependency support.
- Single file, runs on any POSIX shell (sh, ash, dash, ksh, bash...)
- Compatible with stdin-friendly backends (MySQL/MariaDB, SQLite3, PostgreSQL...)
- Each migration is a single SQL file
- No need for numeric versioning: each migration is named arbitrarily
- Migrations can depend on other migrations
- Migrations are verified for success
- Migrations can be reverted
I wanted a migration tool similar in functionality to the excellent https://sqitch.org/ but without the run-time dependencies on Perl and a lot of modules. I looked into https://github.com/mbucc/shmig which is a shell script, but doesn't support dependencies between migrations themselves. I ended up "rolling my own" and gomiSQL was born.
Just download the stand-alone gomisql shell script and make it executable. ;-)
-hDisplay help-dDisplay all executed SQL and back-end output-p <path>Location of the SQL files [default:./migrations/]-b <cmd>Name of back-end command [default:mysql]-a <args>Additional arguments to pass to back-end command (batch mode is already set)
Several environment variables can also be used, although command line options have priority:
GOMISQL_DEBUG(set non-empty to enable)GOMISQL_PATHGOMISQL_BACKENDGOMISQL_ARGS
gomisql -b sqlite3 -a testdb.sqlite list# Assuming you are configured for non-interactive authentication
gomisql -a "--user someuser somedb" listgomisql -b psql -a "-d somedb -h somehost -U someuser" listRun all validations to discover which are and aren't deployed.
Create empty migration name from handy template.
Deploy migration name and dependencies, or all avaliable if not specified.
Revert migration name. Aborts if any deployed migration depends on name, displaying which.
A migration file is a standard SQL file with special comments. Standard multiline /* ... */ comments are ignored. Single-line -- comments are considered to be the start of a new section, so they cannot be used inside of a section.
Optional. Explicitly name other migration file basenames (without the .sql extension) which must be deployed and valid before attempting to deploy this one.
Mandatory. Begins SQL code to run when deploying the migration. Ends at the next single-line comment.
Mandatory. Begins SQL code to run to prove that a migration was successfully deployed. Ends at the next single-line comment. Verifications are run after deployments and whenever dependencies need checking. CAUTION: While you may technically omit this section, doing so means the migration is considered to be already deployed and is thus not deployable!
To test whether a table or some columns exist, just select them with a false condition, as the SQL back-end will fail and let us know:
BEGIN;
/* Prove that foo.col1, foo.col2 and foo.col3 exist in schema. */
SELECT col1, col2, col3 FROM foo WHERE 0;
ROLLBACK;To test whether a specific row exists, make the back-end output #FAIL which will be recognized as a failure. Forcing the presence of results with COUNT(*) does the trick, along with COALESCE(). Be sure to use one distinct SELECT per row to check, so that any missing row causes a failure.
BEGIN;
/* Output '#FAIL' if user 23 does not exist. */
SELECT COALESCE(id, '#FAIL', COUNT(*)) AS id FROM users WHERE id='23';
ROLLBACK;Similarly, to test whether an index exists, at least in MySQL which I could test, use information_schema.statistics:
BEGIN;
SELECT COALESCE(index_schema, '#FAIL', COUNT(*)) AS name FROM information_schema.statistics
WHERE table_schema=DATABASE() AND table_name='users' AND index_name='some_name'
;
ROLLBACK;Optional. Begins SQL code to run to revert the migration. Ends at the next single-line comment.
Given the following files:
./migrations/foo.sql
./migrations/bar.sql
./migrations/baz.sql
...and ./migrations/baz.sql containing the following:
-- #Dependencies: foo bar
This is ignored!
-- #Deploy:
BEGIN;
CREATE TABLE IF NOT EXISTS users (
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL DEFAULT '',
PRIMARY KEY (id)
);
COMMIT;
-- #Verify:
BEGIN;
SELECT id, name FROM users WHERE 0;
ROLLBACK;
-- #Revert:
BEGIN;
DROP TABLE users;
COMMIT;
-- This is ignored
This is ignoredRunning the following would cause migrations foo and bar, then baz to be installed if missing:
gomisql -y -a "--user dbuser" deploy bazDependencies are installed outright during deployment; it would be better if a list was displayed and the user was asked to accept, along with a -y option to assume "yes" (like apt-get).
Similarly, instead of aborting when a reversion is impossible, it would be better to display the list of migrations which would need to be reverted and prompt for approval.
Both the above imply maintaining a list of migrations to be deployed or removed in memory instead of doing it as we go, which while feasible isn't an absolute necessity for now.
Graph X Design Inc. https://www.gxd.ca/ sponsored this project.
Copyright (c) 2018-2022 Stéphane Lavergne https://github.com/vphantom
This program is distributed under the MIT (X11) License: http://www.opensource.org/licenses/mit-license.php
Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.