This is a work in progress.
The aim of this project is to create an easier way to review and manage schemas of medium-level complexity.
- Easy querying of database schema in a vendor-independent manner. Vendor-specific information may also be available (e.g.
PRAGMAfor SQLite). - Given a declared schema, generate code/classes that can be used in Entity Framework, Dapper, ServiceStack.OrmLite, etc.
- Detects potential schema issues. For example, missing indexes or foreign key constraints.
- Generates a schema report that includes details on tables, views, columns, indexes, etc. Also includes relationship diagrams. A demo is available at https://sjp.github.io/Schematic/demo/report
- MySQL
- Oracle
- PostgreSQL
- SQLite
- SQL Server
Schematic ships a command-line tool, packaged as the dotnet-schematic .NET tool:
dotnet tool install --global dotnet-schematicThe schematic command exposes the following sub-commands:
init— interactively create a configuration file (see below).orm efcore/orm ormlite/orm poco— generate ORM projects for a database.lint— analyse a database schema for potential issues.report— generate an HTML report of a database schema.test— test a database connection to see whether it is available.completion— generate shell completion scripts (see below).
Every database command needs to know two things: which dialect to use (one of mysql, oracle,
postgresql, sqlserver, sqlite) and a connection string. There are three ways to supply them.
1. Inline, no file needed. Pass --dialect and --connection-string directly. This is the quickest
way to run a one-off report or lint:
schematic report --dialect sqlite --connection-string "Data Source=app.db" --output ./report
schematic lint --dialect sqlite --connection-string "Data Source=app.db"2. Scaffold a configuration file with init. For a repeatable setup, run the interactive wizard.
It prompts for the dialect and connection details (entering them field-by-field, or pasting a full
connection string), optionally tests the connection, and writes a schematic.json:
schematic init # writes ./schematic.json
schematic init --output db.json # or a path of your choosing3. Use a configuration file. A configuration file is JSON with a Dialect and a connection string
named Schematic:
{
"Dialect": "sqlserver",
"ConnectionStrings": {
"Schematic": "Server=localhost,1433;Database=MyDb;User Id=sa;Password=<password>"
}
}Point any command at it with --config (or -c). If you omit --config, a schematic.json in the
current directory is picked up automatically:
schematic report --config schematic.json --output ./report
schematic lint # uses ./schematic.json if presentValues can also be overridden via environment variables (for example, keeping secrets out of the file):
set Dialect and ConnectionStrings__Schematic. Inline --dialect / --connection-string options take
precedence over both the file and environment variables.
Values inside the configuration file (and inline --dialect / --connection-string options) can also
reference environment variables with ${NAME} placeholders, so the file itself can be committed to
source control without leaking secrets:
{
"Dialect": "sqlserver",
"ConnectionStrings": {
"Schematic": "Server=localhost,1433;Database=MyDb;User Id=sa;Password=${DB_PASSWORD}"
}
}${NAME} is the only supported syntax — there's no nesting or default-value fallback. If a referenced
variable isn't set, the command fails immediately with a clear error rather than silently using a blank
value. schematic init can offer to write a placeholder like this for you instead of the literal
password when you choose the guided setup.
A few lint rules query the database rather than just its schema: they look for empty tables, for nullable
columns that never hold a value, and for self-referencing rows, and they check that each view still compiles.
On a large production database the nullable-column check can mean a table scan for every nullable column that
is always null. Pass --schema-only-lint to report, or --schema-only to lint, to leave those rules out. Linting
then reads nothing beyond the schema, and the issues only those rules can find are not reported.
schematic report --config schematic.json --output ./report --schema-only-lint
schematic lint --config schematic.json --schema-onlyThe completion command prints a tab-completion script for a given shell to standard output. Supported shells are bash, zsh, fish, and powershell.
# bash — current shell
source <(schematic completion bash)
# bash — permanent
schematic completion bash > /etc/bash_completion.d/schematic
# zsh — save to a directory on your $fpath
schematic completion zsh > "${fpath[1]}/_schematic"
# fish
schematic completion fish > ~/.config/fish/completions/schematic.fish
# PowerShell — append to your profile
schematic completion powershell >> $PROFILEEach generated script starts with a comment describing how to install it.
Original icon made by Madebyoliver from www.flaticon.com. Modifications created by myself.
