Output normalised diffable database schemas
Project description
Diffable SQL
This is a small tool that outputs SQL from a number of databases in a sorted, diff-friendly order.
Install
The tool supports any database that SQLAlchemy supports. You must install the correct connector for your database:
$ pip install diffable-sql
$ pip install psycopg2-binary # postgres
$ pip install mysqlclient # mysql
Usage
You can pass any number of DSNs as arguments, for example postgresql:///db-name
. The tool will output normalised SQL
DDL statements for each DSN.
$ diffable-sql sqlite:///db.sqlite3
CREATE TABLE sometable (
foo VARCHAR,
id BIGINT NOT NULL
);
ALTER TABLE sometable ADD CONSTRAINT c PRIMARY KEY (id);
ALTER TABLE sometable ADD CONSTRAINT c UNIQUE (foo);
CREATE INDEX i ON sometable (foo);
Specifically:
- The tables and columns will be sorted by name
- Indexes and constraints will be renamed as
i
andc
- Deferrable constraint information will be hidden
- Identity columns will be hidden
These allow schemas to be more easily diffed.
JSON output
Passing --output-format=json
will output a json structure for each table, containing information on the column
types, SQL, nullability, indexes and constraints:
$ poetry run diffable-sql sqlite:///tools/db.sqlite3 --output-format=json | jq
Outputs:
{
"name": "sometable",
"database": "tools/db.sqlite3",
"columns": {
"id": {
"type": "BIGINT",
"sql": "id BIGINT NOT NULL",
"nullable": false
},
"foo": {
"type": "VARCHAR",
"sql": "foo VARCHAR",
"nullable": true
}
},
"indexes": [
{
"name": "foobar",
"type": "Index",
"sql": "CREATE INDEX foobar ON sometable (foo)",
"columns": [
"foo"
],
"expressions": []
}
],
"constraints": [
{
"name": null,
"type": "PrimaryKeyConstraint",
"sql": "ALTER TABLE sometable ADD PRIMARY KEY (id)",
"columns": [
"id"
],
"expression": null
},
{
"name": null,
"type": "UniqueConstraint",
"sql": "ALTER TABLE sometable ADD UNIQUE (foo)",
"columns": [
"foo"
],
"expression": null
}
]
}
Args
$ diffable-sql --help
Usage: diffable-sql [OPTIONS] [dsn]...
Options:
--no-rename-indexes TEXT Don't rename indexes
--no-rename-constraints TEXT Don't rename constraints
--no-ignore-deferrable-constraints TEXT
Include deferrable constraint information
--no-ignore-identity-columns TEXT
Don't ignore identity column information
--output-format [sql|json]
--help Show this message and exit.
Project details
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
diffable_sql-0.2.0.tar.gz
(3.3 kB
view hashes)
Built Distribution
Close
Hashes for diffable_sql-0.2.0-py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | d898942fde9b198420ec3f9127682614bd3f048c568506f68171121c5812cf2c |
|
MD5 | 2ddcbe080a49b9a97a454fb67bdc4f41 |
|
BLAKE2b-256 | 0c47b114e60eac52a8880da2f09c64c8029435a1c2f4746d46122b7185a0366e |