[{"_1":2,"_161":-5,"_162":-5},"loaderData",{"_3":4,"_17":18,"_25":26,"_140":141},"root",{"_5":-5,"_6":-7,"_7":8,"_9":-7,"_10":11,"_12":-7,"_13":14,"_15":16},"currentUser","primaryEmail","siteVerification","6LeIxAcTAAAAAJcZVRqyHh71UMIEGNQ_MXjiZKhI","toast","enableDev",false,"incidentStatus","theme","system","contrast","normal","routes/_layout",{"_19":-5,"_20":21,"_22":21,"_23":11,"_24":21},"username","isAskGordonEnabled",true,"isUsingNewTemplate","showMinimalLanding","showHoneypotTrap","routes/_layout.r.$namespace.$reponame",{"_27":28,"_29":-5,"_30":11,"_31":11,"_32":33,"_34":35,"_36":-5,"_37":11,"_38":39,"_88":-5,"_89":90,"_129":-5,"_130":11,"_131":21,"_132":11,"_133":-5,"_134":11,"_135":-5,"_136":137,"_138":91,"_139":-7},"canonicalUrl","https://hub.docker.com/r/jonahgeorge/schema-tool/tags","mcpServer","canSeeDockerfile","canSeeAutobuilds","namespace","jonahgeorge","reponame","schema-tool","cloudBuildSource","isAutobuildsAvailable","repo",{"_40":41,"_42":43,"_45":46,"_47":48,"_49":11,"_50":51,"_52":53,"_54":55,"_57":35,"_32":33,"_58":59,"_60":44,"_61":62,"_63":64,"_65":66,"_67":-5,"_68":62,"_69":70,"_71":11,"_72":33,"_73":74,"_79":21,"_80":81,"_85":33,"_86":87},"categories",[],"content_types",[44],"image","date_registered","2017-09-08T19:20:09.916725Z","description","Schema Tool helps manage database alter history in a framework-agnostic way.","is_private","last_updated","2019-04-02T18:27:55.269954Z","last_modified","2024-10-16T13:48:34.145251Z","media_types",[56],"application/vnd.docker.container.image.v1+json","name","pull_count",308,"repository_type","star_count",0,"status",1,"status_description","active","source","collaborator_count","full_description","# Schema Tool\n\n[![Build Status](https://travis-ci.org/appnexus/schema-tool.svg)](https://travis-ci.org/appnexus/schema-tool)\n[![Code Health](https://landscape.io/github/appnexus/schema-tool/master/landscape.svg?style=flat)](https://landscape.io/github/appnexus/schema-tool/master)\n\n\n\n**Table of Contents** *generated with [DocToc](https://github.com/thlorenz/doctoc)*\n\n- [Requirements](#requirements)\n- [Getting Started](#getting-started)\n- [Configuration](#configuration)\n- [Understanding The Alter Chain](#understanding-the-alter-chain)\n- [Running Alters (up / down / rebuild)](#running-alters-up--down--rebuild)\n- [Running Environment Specific Alters](#running-environment-specific-alters)\n- [Checking and Resolving the Alter Chain](#checking-and-resolving-the-alter-chain)\n- [DBA Alter Generation](#dba-alter-generation)\n- [Recommended Workflow](#recommended-workflow)\n- [Reporting Issues / Feature Requests](#reporting-issues--feature-requests)\n- [Contributing](#contributing)\n- [License](#license)\n\n\n\nSchema Tool helps manage database alter history in a framework-agnostic\nway that works well for both large and small teams. The tool uses the concept of a linear\nalter chain to track the order in which alters should be (or have been) applied. It does so\nby giving each alter a `backref` that points at a previous alter in the alter chain. The\nalter chain must remain linear. (In Git metaphors, this is akin to requiring a fast-forward\nand disallowing a merge.) The features of the tool aim to allow developers to\nwrite alter code in their respective branches, easily and safely merge that\ncode into the main alter chain, and finally run those alters on target databases (dev, QA, production, etc).\n\nCurrently the tool supports MySQL, Postgres, and Vertica.\n\nThe key features of the tool are:\n\n+ Manage alter chains (parents and children) and provide check/resolve tools to enforce these chains are correct\n+ Automatically run alters to setup, teardown, or rebuild your database\n+ Determine what environment alters are allowed to run in\n+ Keep track of a history of what alters have been run in the database for easy programatic usage\n\nOne thing that the tool is not, and does not try to be, is a domain-specific language for making alters. SQL\nis an excellent DSL already, so there is no need for that.\n\n\n## Requirements\n\n+ Python 2.7 (may work with other versions, only tested against 2.7)\n+ [`psycopg2`][3] if planning to use Schema Tool with Postgres\n+ [`vertica-python`][6] and [`psycopg2`][3] (required by Vertica python) if planning to use Schema Tool with Vertica\n+ Hive version `0.11`+ (`beeline` required) and [`pyhs2`][7] if planning to use with Hive\n\n## Getting Started\n\nYou need to download the tool and (preferably) have the `schema` executable somewhere on\nyour path. You can get started with the tool simply by doing:\n\n```shell\nmkdir -p ~/bin\ncd ~/bin\ngit clone git@github.com:appnexus/schema-tool.git schema-tool\n\necho 'export PATH=\"$HOME/bin/schema-tool:$PATH\"' >> ~/.bashrc\n# or\necho 'alias schema=\"$HOME/bin/schema-tool/schema.py\"' >> ~/.bashrc\n\nsource ~/.bashrc\n```\n\nIf you are creating a new project, you can simply do the following:\n\n```shell\nmkdir my-schemas\ncd my-schemas/\ngit init\n\n# for MySQL\ncp ~/bin/schema-tool/conf/config.json.mysql-template config.json\n# or, for Postgres\ncp ~/bin/schema-tool/conf/config.json.pgsql-template config.json\n# or, for Vertica\ncp ~/bin/schema-tool/conf/config.json.vertica-template config.json\n# or, for Hive\ncp ~/bin/schema-tool/conf/config.json.hive-template config.json\n\n# edit appropriately\nvim config.json\n```\n\n\nHere is the content of the MySQL config file template:\n\n```json\n{\n \"type\" : \"mysql\",\n\n \"username\" : \"root\",\n \"password\" : \"root\",\n \"host\" : \"localhost\",\n \"port\" : 3306,\n\n \"revision_db_name\" : \"revision\",\n \"history_table_name\" : \"history\",\n\n \"pre_commit_hook\" : \"relative/path/to-pre-commit-hook.sh\",\n \"static_alter_dir\" : \"relative/path/to-static-alters/\"\n}\n```\n\n\nIt should be pretty self-explanatory except for the revision database and the history table. These fields specify\nwhere the tool will keep track of what alters have been run. You can set these values to whatever\nnames you would like - the tool takes care of creating the database and table. For more information on configuring\nthe tool, see the [Configuration](#configuration) section below.\n\nOnce your configuration file is correct, you are ready to take a tour of the tool and create your first\nalter. You can find all the commands supported by the tool by reading the help-file, which\nyou can get to via `schema -h`. You should see the following:\n\n```text\nUsage: schema command [options]\n\n(ENV)01:alter(master % u=) $ schema -h\nUsage: schema command [options]\n\nCommands:\n new Create a new alter\n check Check that all back-refs constitute a valid chain\n list List the current alter chain\n up Bring up to particular revision\n down Roll back to a particular revision\n rebuild Run the entire database down and back up (hard refresh)\n gen-ref Generate new file-ref\n gen-sql Generate SQL for a given reference, including revision-history alter(s)\n resolve Resolve a divergent-branch conflict (found by 'check' command)\n init Initialize new project\n version Shows current version of tool\n help Show this help message and exit\n\nOptions:\n -h, --help show this help message and exit\n```\n\nAdditionally each command also has its own help file. For example, if you wanted to see what\noptions are available to you when applying an alter, you can could `schema up -h` and see:\n\n```text\nUsage: schema up [options] [ref]\n\nArguments\n ref Run all alters up to, and including, the ref given\n\nOptions:\n -h, --help show this help message and exit\n -n N, --number=N Run N number of up-alters from current state - overrides\n arguments\n -f, --force Continue running up-alters even if an error has occurred\n -v, --verbose Output verbose error-messages when used with -f option if\n errors are encountered\n -u, --no-undo When comparing histories (of what has ran and what is to\n be ran) do not undo any previously ran alters\n```\n\nTo get started and create your first file, simply run:\n\n```shell\nschema init\nschema new -f init\n```\n\nThe `init` will take care of setting up the `revision` database and `history` table (or whatever you\nconfigured them to be) and any janitorial tasks. The `new` will create an `up` and `down` file\nthat will look something like this:\n\n```text\n137823105930-init-down.sql\n137823105930-init-up.sql\n```\n\nThe number at the front is the identifier that is used to keep the alter chain in line (see next\nsection on understanding the chain). You can now edit the files (including whatever alter you have)\nand apply them against your local database by running `schema up`.\n\nNow you're up and running! You can add more files with `schema new` and control the state of your\ndatabase with `up`, `down`, and `rebuild` commands.\n\n\n## Configuration\n\nConfiguration for the schema tool usually exists in a file named `config.json` that is located\nwithin the current working directory, also the directory containing the alter files. However,\nthe tool will also load a _base_ config from `~/.schema-tool`. This file will serve as the _defaults_\nfor any configurations not specified in the local `config.json`. If, for example, you work solely\nMySQL then your user/pass and host/port settings can be stored in this file.\n\nSince the bulk of the config options focus around connecting to the various, supported databases;\nnot all config values apply for each type of database being used. The following chart attempts\nto define each option based on the database type being used.\n\nValue | Type | DB | Description\n------|------|----|------------\n`type` | string | * | Defines the DB type to be used. Possible values: __mysql__, __hive__, __postgres__, __vertica__\n`username` | string | * | Defines the username to use when connecting to the DB.\n`password` | string | * | Defines the password to use when connecting to the DB.\n`host` | string | * | Defines host of DB to connect to. No default provided.\n`port` | int | * | Defines the port of the DB host to connect to. Specific DB support _may_ provide default.\n`db_name` | string | __postgres__, __vertica__ | Determines specific DB to connect to when running alters.\n`schema_name` | string | __postgres__, __vertica__ | Determines specific schema to connect to when running alters.\n`revision_db_name` | string | * | Name of DB to store history information in (for applied alters). __Note__: For Postgres and Vertica, if the DB does not already exist, it will error. Unlike Hive or MySQL, the DB will _not_ be automatically created for you. The _schema_ however will be as defined in __revision_schema_name__.\n`revision_schema_name` | string | __postgres__, __vertica__ | Defines the schema name that the history table will live in.\n`history_table_name` | string | * | Name of table to store history information in (for applied alters).\n`pre_commit_hook` | string | * | Path to script to use as a pre-commit hook. Will be installed when `init` is run.\n`static_alter_dir` | string | * | Path to output \"static alter files\" when using the `gen-sql` command.\n\n\n\n## Understanding The Alter Chain\n\nTo understand how the tool does its job, you have to understand how it thinks about alters.\nWhen working with alters, it's easy to have dependencies between alters such that order is\nvery important. The way that the tool addresses this is by giving each alter a unique reference\nand each alter specifies its parent alter by that reference. The parent relationship\nspecifies that the parent alter should be applied before the child alter. This creates a singly-\nlinked list where the tail is the first alter to be run and the head is the last alter to be\nrun:\n\n```text\nA <----- B <----- C <----- D <----- E <----- F\n```\n\nA valid alter chain does not have any branching such that a parent has multiple children.\nThis can arise in situations where you might have created a separate feature-branch in your\nversion control system and merged it back to the mainline after some time of development in\nwhich the mainline branch had advanced in the interim:\n\n```text\nA <----- B <----- C <----- F\n ^\\\n \\ <----- D <----- E\n```\n\nThe `check` command will alert you to any inconsistencies in your alter chain like that which\nis defined above. The `resolve` command will help you resolve such issues if they are found.\nMore on that later.\n\nNote that each node in the alter chain is actually a pair of alters.\nOne item in the pair is the up alter and the other item is the down alter. The actual structure\nwould look like:\n\n```text\nUp: A <----- B <----- C <----- D <----- E <----- F\n ∧ ∧ ∧ ∧ ∧ ∧\n | | | | | |\n ∨ ∨ ∨ ∨ ∨ ∨\nDown: A <----- B <----- C <----- D <----- E <----- F\n```\n\nThe alters are given refs, rather than incremental numbers, because it makes things a\nlittle easier to track within the database in terms of what has been applied. When working with\nmultiple branches, the incremental numbers may not represent the correct set of alters that\nhave been applied to the database. This is important when updating that the user knows he is up\nto date with the correct alters.\n\n## Running Alters (up / down / rebuild)\n\nYou can run alters by using the `up`, `down`, and `rebuild` commands. The `rebuild` command\nwill run all the way down and all the way back up. There are some common options you should\nbe aware of that each command supports:\n\n+ `-v` outputs verbose error messages\n+ `-f` will ignore errors and move on to running the next alter\n+ `-n` (only on `up` and `down`) specifies the number of alters to run from current point\n+ `ref` you can provide a reference number to run up/down to. For rebuild it will run down to\n this commit and back up (inclusively)\n\nYou may run into errors when switching branches often because the tool will get confused on\nwhat may or may not have ran against your database. Most of the time you can get around this by\nrunning `schema rebuild -fv`.\n\n## Running Environment Specific Alters\n\nWhen working on large software systems, it is common to run it in multiple environments\n(prod, staging, sand, test / eq, dev, etc). It happens occasionally that you need to craft\nan alter to only run on a subset of all environment or all but a few environment. To help\nwith this dilemma, you can add an additional bit of meta-data to your alters to specify the\nenvironments it should or should not be run in.\n\nTo specify where the alter _should_ run, you can add the following bit of meta-data to the\ntop of your alter\n\n```sql\n-- require-env: dev, test\n```\n\nThis implies that the alter will _only_ be run in `dev` or `test` environment. Similarly, we\ncould specify that the alter should _not_ be run in production, which could be defined as\n\n```sql\n-- skip-env: prod\n```\n\nIt is important to note that since `require-env` works as a whitelist and `skip-env` works as\na blacklist, that only one config-value can be used at a time within a single alter. Since the DB\nstate is used to run the 'down' alters, this is only required in the 'up' alter.\n\nTo specify which environment you are currently running with, simply edit your `config.json` to\nindicate with the `env` key. \n\n```json\n{\n \"env\": \"dev\"\n}\n```\n\nFor your local development setup, this is usually a good candidate to put into your global config\nfile `~/.schema-tool`. See [Configuration](#configuration) for more information about the global\nconfig file.\n\n## Checking and Resolving the Alter Chain\n\nRunning the `check` command will let you know if you have a divergent branch. Typically this\nis the result of merging two branches or rebasing against your stable branch from your\nfeature branch. The `check` command will tell you where the branch occurs and list the two\nalters that are in question. If you know which alter is at fault (typically the new alter)\nthen you can simply run:\n\n```shell\nschema resolve OFFENDING_ALTER_HASH\n```\n\nThis will move the offending alter (and any alters that come after it in the sub-chain)\nto the end of the alter chain. One thing to note is that if you resolve the incorrect\nalter, then you may end up with an order-of events that does not make sense.\n\n## DBA Alter Generation\n\nIf you work in a large organization or with a mission-critical RDBMS then your database administrators (DBAs) may,\nunderstandably, be hesitant to use any tool to auto-magically run alters against the\nproduction environment. However, it still would be nice to take advantage of the\nrevision-tracking functionality this tool provides in your production environment. This tool\nprovides the best of both worlds. Your DBAs can use plain alters (and whatever tools\nthey choose) and you can track what revisions have run against your production database. This\ncan be done with the `gen-sql` command.\n\nThe `gen-sql` command generates a set of alters, from your original alters, with SQL\nadded to directly manage insertions and deletions into the revision table. To get started you need to\nensure that your schema project is setup correctly for static alter genration.\n\n> __Assumptions:__\n> + All commands are performed within the root directory of the schema project that\n _you_ created (as per the __Getting Started__ section)\n> + The `config.json` (config file) is located within the project root\n\n+ Edit your config.json to include the following two keys:\n```json\n\"static_alter_dir\": \"DBA_FILES/\"\n\"pre_commit_hook\": \"pre-commit-hook-static-dba-files.sh\"\n```\n+ Copy the pre-commit hook from the schema-tool's contrib to your schema-dir:\n```shell\ncp SCHEMA-TOOL-REPO/contrib/pre-commit-hook-static-dba-files.sh .\n```\n+ Use the schema-tool to install the hook and perform any needed setup:\n```shell\nschema init\n```\n+ Perform initial generation (for any existing alter):\n```shell\nschema gen-sql -qw # Generates static 'up' alters from existing 'up' alters\nschema gen-sql -qwd # Generates static 'down' alters from existing 'down' alters\n```\n\nNow that you have the hook installed, you will see auto-generated files show up\neach time you commit a new (or edit an existing) alter.\n\n\nYou can look at the help-file for `gen-sql` yourself to become familiar with the\nother options of the command.\n\n## Recommended Workflow\n\nYou can use the tool any way that you see fit. However, we've found that it works quite\nwell when you work with feature branches. In this workflow all new work is done on a feature\nbranch and `master` becomes the stable branch. When an atler is completed (tested / reviewed)\nyou can rebase against `master` and then run a `schema check` followed by a `schema resolve`\nif necessary. When you merge to master you ensure that the feature branch is up-to-date.\n\nIn this way you avoid merges with `master` (and thus divergent alter chains) and ensure that\n`master` is clean (and ideally stable). You can further enforce this workflow with testing\n(ensuring that all the alters can be run) such as [Jenkins][1] and [Gerrit][2] (to enforce\nthe rebase-based workflow).\n\n## Reporting Issues / Feature Requests\n\nIf you run into an issue that results in a script error (Python stacktrace) then please\nopen up a ticket in the GitHub issue tracker. Please include the following information\n\n+ Steps to reliably reproduce the issue\n+ Entire error output including stack trace\n\nWe'll work with you to resolve the issue and collect any more information that may be\nrequired to diagnose the issue.\n\n## Contributing\n\nIf you would like to contribute, please see our current list of issues and/or feature requests\nand send us a pull request. If you have something specific that you'd like to add or fix, please\nopen up an issue for discussion. If it is a fix for a bug or everyone agrees that it would be\na useful feature, then submit your pull request. Make sure that your pull request's commit message\nuses one of the [appropriate identifiers][4] to link the pull request to the issue.\n\nIf you are making updates to the documentation, please be sure to run `make` with the project's\n`Makefile` to perform all required pre-processing of the docs. You can run `make setup` to install\nany required pre-processors.\n\n[Current contributors][5]\n\n## License\n\nSee LICENSE file\n\n\n\n\n [1]: http://jenkins-ci.org/\n [2]: https://code.google.com/p/gerrit/\n [3]: https://github.com/psycopg/psycopg2\n [4]: https://github.com/blog/1386-closing-issues-via-commit-messages\n [5]: https://github.com/appnexus/schema-tool/graphs/contributors\n [6]: https://pypi.python.org/pypi/vertica-python/\n [7]: https://github.com/BradRuderman/pyhs2\n","has_starred","hub_user","immutable_tags_settings",{"_75":11,"_76":77},"enabled","rules",[78],".*","is_automated","permissions",{"_82":21,"_83":11,"_84":11},"read","write","admin","user","storage_size",165183495,"orgData","recentTags",[91],{"_92":44,"_93":94,"_95":96,"_97":98,"_99":100,"_50":118,"_119":94,"_120":33,"_121":56,"_57":122,"_123":124,"_125":109,"_126":118,"_127":66,"_128":21},"content_type","creator",67505,"full_size",113081282,"id",15046406,"images",[101],{"_102":103,"_104":105,"_106":107,"_108":109,"_110":111,"_112":113,"_114":107,"_115":-5,"_116":96,"_63":66,"_117":-5},"architecture","amd64","digest","sha256:165d7d141b18934a516ab0c5ecb5b208265212a1ece34ca9c1cb6688663be144","features","","last_pulled","2026-09-25T00:10:21.719438091Z","last_pushed","2019-04-02T18:27:55Z","os","linux","os_features","os_version","size","variant","2019-04-02T18:27:54.952781Z","last_updater","last_updater_username","media_type","latest","repository",1804684,"tag_last_pulled","tag_last_pushed","tag_status","v2","sbxKitMetadata","isPotentialSpam","isHubSbxKitsEnabled","showSbxLogoOverride","sbxTrademarkText","displaySecurityMetadata","tagAttestations","tagsHaveAttestations",[],"selectedTag","attestationsUrl","routes/_layout.r.$namespace.$reponame.tags",{"_38":142,"_149":150,"_136":159,"_160":11},{"_40":143,"_42":144,"_45":46,"_47":48,"_49":11,"_50":51,"_52":53,"_54":145,"_57":35,"_32":33,"_58":59,"_60":44,"_61":62,"_63":64,"_65":66,"_67":-5,"_68":62,"_69":70,"_71":11,"_72":33,"_73":146,"_79":21,"_80":148,"_85":33,"_86":87},[],[44],[56],{"_75":11,"_76":147},[78],{"_82":21,"_83":11,"_84":11},"tags",{"_151":64,"_152":-5,"_153":-5,"_154":155},"count","next","previous","results",[156],{"_92":44,"_93":94,"_95":96,"_97":98,"_99":157,"_50":118,"_119":94,"_120":33,"_121":56,"_57":122,"_123":124,"_125":109,"_126":118,"_127":66,"_128":21},[158],{"_102":103,"_104":105,"_106":107,"_108":109,"_110":111,"_112":113,"_114":107,"_115":-5,"_116":96,"_63":66,"_117":-5},[],"canDeleteTags","actionData","errors"]

jonahgeorge/schema-tool

Tags for jonahgeorge/schema-tool