Skip to content
sjpPublic

About

Schema management for relational databases.

Resources

Stars

4 stars

Watchers

2 watching

Forks

Repository files navigation

Schematic

License (MIT) GitHub Actions Code coverage

Project icon

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.

Features

  • Easy querying of database schema in a vendor-independent manner. Vendor-specific information may also be available (e.g. PRAGMA for 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

Currently supported databases

  • MySQL
  • Oracle
  • PostgreSQL
  • SQLite
  • SQL Server

Command-line tool

Schematic ships a command-line tool, packaged as the dotnet-schematic .NET tool:

dotnet tool install --global dotnet-schematic

The 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).

Configuring a connection

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 choosing

3. 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 present

Values 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.

Lint rules that read data

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-only

Shell completions

The 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 >> $PROFILE

Each generated script starts with a comment describing how to install it.

Icon

Original icon made by Madebyoliver from www.flaticon.com. Modifications created by myself.

About

Schema management for relational databases.

Resources

Stars

4 stars

Watchers

2 watching

Forks

Releases

Packages

Used by

Contributors

Languages