User manual

Schevo is a PostgreSQL, MySQL, MariaDB and SQL Server database modeler that stores the model in a Git-friendly format: every table in its own JSON file, with stable output you can review and merge like any other code.

Getting started

1. Import your schema: File > Import SQL… (Ctrl+I) and pick the database creation script. Schevo draws the diagram grouping tables by prefix (cat_, ord_…).
2. Save the model (Ctrl+S) into a folder of your repository, for example db-model/. If the SQL came from a file, it stays linked to the model and is regenerated every time you save.
3. Work and save: only the files that change are rewritten.

The first time you open Schevo it loads a sample online shop so you can try everything. Afterwards it reopens the last model on startup; earlier ones are under File > Open recent.

You can have several models open at once. If the window already has a model, opening, creating or importing another one asks whether to use a new window or this window; if that model is already open in another window, that window is brought to the front. File > Close window (Ctrl+W) closes only the current window and File > Exit closes them all.

The model folder

db-model/
  project.json                     name, default schema, SQL path, line ending, naming rules
  tables/public.cat_product.json   one table per file: columns, PK, FKs, indexes, position and color
  extra.sql                        statements that are not drawn (sequences, views, functions…), if any
  diagram.json                     diagram zones and notes, if any

Importing from a database

If you don't have the creation script, File > Import from database… reads the schema straight from a PostgreSQL database (version 10 or later); for MySQL, MariaDB and SQL Server, see their own sections. Enter the host, port, database, user and password, and check Use SSL if the server requires it. Test connection checks the details and lists the schemas so you can choose which ones to read; if you choose none, every user schema is read (never pg_catalog or information_schema).

The result is the same model you would get by importing that database's creation script: tables, columns, primary keys, FKs (with their actions), UNIQUE, CHECK, indexes and comments. Columns with their own sequence become SERIAL/BIGSERIAL, and IDENTITY columns become identity columns, with their sequence options. What is not drawn (schemas, extensions, ENUM types, domains, sequences, functions, views, triggers, indexes on expressions or partial indexes, and EXCLUDE constraints) is kept in Project > Additional SQL statements. Composite and range types, rules, policies, privileges and comments on other objects are not read.

The connection is read-only and only queries the catalog. Schevo remembers the host, port, database and user, but never stores the password. PostgreSQL rewrites DEFAULT and CHECK expressions its own way (for example, BETWEEN becomes >= … AND <= … and IN (…) becomes = ANY (ARRAY[…])); Schevo undoes the most common changes in the text, and when comparing or generating migrations it treats equivalent expressions as equal, so the database and the model imported from its script show no differences.

The database can also be the other version when comparing and the starting point of a migration script: choose Database and click Connect….

MySQL and MariaDB

Besides PostgreSQL, Schevo models MySQL 8.0 or later and MariaDB 10.6 or later databases. Every model has an engine, shown in the status bar (clicking it opens the project properties):

• File > New model… asks for the engine of the model; the last one you used is preselected.
• File > Import SQL infers the engine from the script itself (backticks, ENGINE=, AUTO_INCREMENT, DELIMITER…). If it is not clear, it asks. It imports hand-written scripts, schema-only dumps (mysqldump --no-data or mariadb-dump --no-data, with their /*!…*/ comments, SET, USE and placeholder views) and exports that create the keys with ALTER TABLE after the tables. Data (INSERT…) is ignored with a single warning.
• In Project > Project properties… the engine can be changed freely while the model has no tables; with tables, only between MySQL and MariaDB. The default schema is called Database here: empty means "the current database", so tables are saved without a schema (tables/customer.json) and the generated SQL works on any database.

The model keeps the MySQL specifics: UNSIGNED and ZEROFILL inside the type (BIGINT UNSIGNED), ENUM/SET with their values, table and column character sets and collations, ON UPDATE CURRENT_TIMESTAMP, generated columns (STORED or VIRTUAL), inline comments, table options (ENGINE, ROW_FORMAT, AUTO_INCREMENT=, partitions) and FULLTEXT, SPATIAL, prefix (name(10)) or USING indexes. AUTO_INCREMENT is a BY DEFAULT identity column. Types are normalized on import (INTEGER → INT, TINYINT(1) → BOOLEAN, without the deprecated integer display width), and the PK always takes the name from the project naming convention, because MySQL does not store it. An index definition accepts column(10) DESC.

Editing the MySQL attributes: in a MySQL or MariaDB model, the table editor (double-click a table or a column) has a Table options tab with the engine, the row format, the default character set and collation, the initial AUTO_INCREMENT value, other options and partitioning (PARTITION BY …). Under each column you edit its character set, its collation, ON UPDATE and, in Generated, the expression of a generated column with VIRTUAL or STORED; for indexes, the FULLTEXT/SPATIAL type and the method (USING BTREE or HASH). The drop-downs suggest common values and accept any other. An empty field means "whatever the server says" and is not saved; neither is InnoDB, the default engine. Values are written as on import (character set and collation in lower case, now() as CURRENT_TIMESTAMP). The PK name cannot be edited, because MySQL does not store it. In a PostgreSQL model the editor shows none of this.

The generated SQL has a fixed format, like SHOW CREATE TABLE: keys and CHECKs inside each table, options on the closing line and the FKs at the end. Routines and triggers (statements with ; inside) are written between DELIMITER $$ and DELIMITER ;, also in extra.sql and in the extra statements editor: the script is meant for the command-line client (mysql database < script.sql), because DELIMITER is not a server statement.

Validation adds the MySQL/MariaDB rules: what the server rejects (a single AUTO_INCREMENT column, and in a key; FKs between integers of different size or sign; texts with different collations in an FK; SET DEFAULT; TEXT or JSON in a key without a prefix…) is an error, and what may cause trouble (tables that differ only in letter case, collations of the other variant, DESC on MariaDB before 10.8…) is a warning.

Importing from a MySQL or MariaDB database: in File > Import from database… choose the engine (the port changes to 3306 and Use SSL is checked); the database is optional and the list is called Databases. The model is the same one its script would give: the tables of the connection's database have no schema, InnoDB and the database collation are not stored, the AUTO_INCREMENT counter is not read, and views, routines, triggers and events are kept without DEFINER. If MySQL 8 rejects the password without SSL, turn SSL on: Schevo does not download the server's public key. The driver is a separate file, lib/mariadb-java-client.jar (LGPL), that connects to both engines; if it is missing, Schevo says so. As with PostgreSQL, the server rewrites expressions (backquotes, parentheses, curdate()…): comparisons and migrations compare them in canonical form.

Migration script: MySQL and MariaDB have no transactional DDL, so the script has no BEGIN/COMMIT (the header reminds you: make a backup and try it on another database first). Column changes use MODIFY COLUMN with the whole definition; FKs are dropped before their columns or the index backing them change and are added again at the end; the PK changes in a single statement. Renames use RENAME COLUMN and RENAME TABLE; FKs whose name changes are dropped and added again (MySQL has no RENAME CONSTRAINT). You can migrate between MySQL and MariaDB, but not from or to PostgreSQL; comparing across engines is allowed. Comparing a MySQL model with the Bundled sample uses the earlier version of the MySQL shop.

The MySQL shop examples are in the examples/mysql folder of the source code. MySQL and MariaDB are registered trademarks of their respective owners.

SQL Server

Schevo also models SQL Server 2017 or later and Azure SQL Database databases. As in PostgreSQL, tables always have a schema (dbo by default, tables/dbo.customer.json). It is chosen like the other engines when creating a model or in Project > Project properties…, and File > Import SQL recognizes it on its own (GO lines, brackets, IDENTITY(1, 1), NVARCHAR…). On the command line, import --dialect sqlserver (or mssql).

• It imports hand-written scripts (without GO or ;: each statement ends where the next one starts, with IF OBJECT_ID(…) IS NOT NULL DROP TABLE, double quotes or brackets) and the scripts generated by the engine's administration tools (WITH CHECK ADD CONSTRAINT, ADD CONSTRAINT … DEFAULT ((0)) FOR, default index options, ON [PRIMARY]), these without any warning. Data is ignored with a single warning; views, procedures, functions and triggers are kept verbatim.
• SQL Server features: IDENTITY(seed, increment) (an ALWAYS identity; quick editing identity creates it that way), (MAX) inside the type (NVARCHAR(MAX); notes nvarchar(max) in quick editing and length MAX in the editor), computed columns AS (expression) [PERSISTED], which have no type of their own and the diagram shows as = expression, per-column COLLATE, ROWVERSION (TIMESTAMP is its old synonym, not a date), clustered and nonclustered indexes (NONCLUSTERED PK, CREATE CLUSTERED INDEX), INCLUDE and filtered indexes (WHERE … IS NOT NULL). Comments are the MS_Description extended properties. The table editor keeps all of this when you accept.
• Every constraint has a name, DEFAULTs too. When the script gives none, Schevo uses a fixed one (<table>_pk, the FK convention, <table>_<column>_uk, <table>_<column>_ck, <table>_<column>_df) instead of the random one the engine would make up, and the generated SQL always writes it. Names made up by the engine (PK__customer__3B75D760) are kept and give a warning suggesting to rename them.

The generated SQL starts with SET ANSI_NULLS ON and SET QUOTED_IDENTIFIER ON, writes each table with its indexes and comments in a batch ending in GO, the FKs at the end, NULL or NOT NULL on every column and names qualified with the schema (brackets only when needed). GO is not T-SQL but a client command: the script is meant for the engine's command-line client (with -b, which stops at the first error) or the query editor of any tool. extra.sql and the additional statements editor also put a GO after each statement.

Validation adds the SQL Server rules: one clustered index per table, types that cannot be indexed, FKs with exactly the same columns (type, length and collation), and cascade paths: the engine rejects an FK that leaves two paths of ON DELETE CASCADE/SET NULL to a table or a cycle, so it is an error (set NO ACTION on one of them). A UNIQUE on a nullable column is a warning, because SQL Server allows only one NULL: use a filtered unique index.

Table editor: in a SQL Server model it also shows the NONCLUSTERED box of the PK; per column, the collation, the DEFAULT name (empty = the implicit <table>_<column>_df) and the computed column (expression and PERSISTED; it has no type); per index, CLUSTERED, INCLUDE and the WHERE filter; and the Table options tab with the storage (ON filegroup) and WITH (…).

Migration script: SQL Server DDL is transactional and the whole script runs in one transaction with SET XACT_ABORT ON; each statement goes in its own batch, preceded by IF @@TRANCOUNT = 0 SET NOEXEC ON, so if anything fails everything is rolled back and the rest (the COMMIT too) is no longer run. Before an ALTER COLUMN, the indexes, keys, CHECKs, DEFAULTs, FKs and computed columns that use the column are dropped and then recreated (this can take a while on large tables; widening a VARCHAR/NVARCHAR that is not in the PK does not touch the indexes). Adding or removing IDENTITY or turning a column into a computed one has no ALTER: the script flags it with a "NOT MIGRATED" comment and the table must be rebuilt by hand. Accepted renames use sp_rename (also for the PK, FKs and DEFAULTs whose name changes with the table or column). New columns go at the end of the table. Constraints with engine-generated names are matched by content when comparing and migrating.

Live database: File > Import from database… with the SQL Server engine (port 1433) reads the catalog (sys.*) with a SQL Server user and password (Windows authentication is not used). The server can be written as server\instance (then the port is not used). The Encrypt the connection box (ticked by default) encrypts and trusts the server certificate, as on development servers; without it, the connection is not encrypted. The user needs the VIEW DEFINITION permission: without it, SQL Server hides part of the schema without an error (Schevo warns). System-versioned, memory-optimized and graph tables are not read. On the command line the URL contains ; and must be quoted: schevo-cli import --db "jdbc:sqlserver://server:1433;databaseName=shop;encrypt=true;trustServerCertificate=true" model --user u, with the password in SQLCMDPASSWORD. Compare and migrate accept the database and the SQL Server Bundled sample.

The SQL Server shop examples are in the examples/sqlserver folder of the source code. SQL Server is a registered trademark of its owner.

The window

• Table list (left): type to filter; a click takes you to the table and a double-click edits it.
• Diagram (center): tables with their PK on top, relationships as lines (solid when the FK is part of the child's PK, dashed otherwise) and the crow's foot on the child side.
• Validation panel (bottom): model errors and warnings, updated as you edit. A click takes you to the table and a double-click opens it. It can be minimized, maximized or closed (View > Validation panel, Ctrl+Shift+V).
• Status bar: the result of the last action, the validation counts, the model's engine and, during the trial, the days left.

Find (Ctrl+F) searches tables, columns, relationships, indexes and comments across the whole model. Every shortcut is listed under Help > Keyboard shortcuts (Ctrl+F1).

Tables and columns

Edit > New table… (Ctrl+T) or double-clicking a table opens the editor: schema, name, color, comment, columns (type, length, scale, NOT NULL, default value, comment and whether it is part of the PK), indexes, and UNIQUE and CHECK constraints. Identity makes the database fill in the column: ALWAYS (always) or BY DEFAULT (only when the INSERT does not give a value); it is generated as GENERATED … AS IDENTITY, the column becomes NOT NULL and it must have an integer type and no default value. Importing SQL or a database keeps its options (START WITH, INCREMENT BY…). Next to the color you get the colors already used by other tables and zones, to reuse them with one click (also in Edit > Change color…, Ctrl+K).

In the diagram, each column name is shown in semibold and its type in gray. Clicking a column marks it; if the column takes part in relationships (it is an FK, or other tables reference it), those relationships stand out from the table's other relationships and the matching column in the other table is marked. Double-clicking opens the editor with that column selected. Types with a very long list of values (MySQL/MariaDB ENUM and SET) are abbreviated in the diagram, e.g. enum('pending','paid','picking',…); hovering over the row shows the full type, which is also in the editor.

• If you change the type of a column that FKs point to (the PK, for example), the change is copied to the columns of the child tables, and from there to the grandchildren, so both sides of every FK keep the same type.
• Renaming a table updates the FKs that reference it and the PK and FK names that followed the convention.
• Ctrl+C, Ctrl+V and Ctrl+D copy, paste and duplicate tables (also between two Schevo windows). Copies get the _copia suffix.
• Del deletes the selection and asks whether to also delete the FK columns that would be left without a relationship.
• Everything can be undone with Ctrl+Z and redone with Ctrl+Y.

Relationships

Edit > New relationship… (Ctrl+R): choose the parent table (the referenced one) and the child (the one with the FK). The child columns are proposed from the parent's PK; type a new name to create them. You can set ON DELETE, ON UPDATE, whether it is identifying (the columns join the child's PK) and whether it allows NULL.

• Selecting a relationship highlights its columns in both tables and shows handles: the ends can be moved to any side of the table and the center square moves the middle segment. Right-click > Reset automatic route undoes the manual route.
• Right-clicking a table or a relationship lets you jump to the parent or child table.

Quick editing

With the mouse over the diagram:

Diagram, zones and notes

• Dragging on empty space pans the diagram; Shift + drag selects several tables; Ctrl + wheel zooms around the cursor (from 10% to 400%); Ctrl+9 fits it to the window.
• Selecting a table highlights its relationships and neighbors and dims the rest (View > Highlight the selection's relationships…).
• Minimap (Ctrl+M) to move around large diagrams and compact mode (Ctrl+Shift+K), which shows only the PK and FK columns.
• Zones: titled, colored rectangles that group tables (Diagram > New zone or right-click). Dragging the title moves the zone with its tables, the corner resizes it and double-clicking the title renames it. Its name also shows on the minimap.
• Notes: free text on the diagram. Double-click to edit and Ctrl+Enter to finish.
• Diagram > Align left and Align top line up the selected tables.
• Diagram > Auto layout… rearranges every table by prefix, with parents to the left.

Validation and naming rules

The model is validated continuously. Errors include FKs pointing to tables or columns that don't exist, PK columns that allow NULL, duplicate names, SET NULL on NOT NULL columns and default values that don't match the type. Warnings include different types on the two sides of an FK, ON DELETE CASCADE cycles, reserved words and naming rule violations. You can save with errors, but Schevo warns you first.

Project > Naming rules… defines the conventions for PKs, FKs, UNIQUE constraints and indexes with the placeholders {table}, {parent}, {child}, {column} and {columns} (by default {table}_pk and {parent}_{child}_fk), and a regular expression for columns that must have the same type in every table (by default .*_id). Leave a field empty to skip that rule.

Working with Git

1. Push the first commit (import and save) on its own: the SQL is sorted alphabetically and the change is large even though the schema is the same. Later commits will only show real changes.
2. Commit the model folder and the SQL together.
3. Since every table is in its own file and the output is always the same, two people changing different tables never conflict, and moving a table only changes its x/y lines.
4. If a merge has conflicts, resolve them in the JSON files. When opening the model, Schevo points to the file and line of any <<<<<<< markers left. Don't resolve SQL conflicts by hand: save the model (or run schevo-cli generate) and it is regenerated.

Changes from outside (git pull)

Schevo watches the folder of the open model. If it changes outside the application (a git pull, a branch switch, another editor):

• with no unsaved changes, the model reloads by itself, keeping the zoom, position and selection;
• with unsaved changes, a notice appears above the diagram with Reload, which discards your changes and loads the files on disk;
• if the files have unresolved conflicts, the notice says how many problems there are (View details gives the file and line) and the open model is left alone; once they are resolved it reloads by itself;
• if only the SQL file changes, the notice offers View differences.

File > Reload from disk (F5) reads the model again at any time.

Generated SQL

Saving regenerates the linked SQL (you can change it in Project > Project properties…, together with the default schema and the line ending). File > Generate SQL… (Ctrl+G) first shows a preview of the script with the differences from the current file: added lines in green and removed lines in red, with a count and a Changes only option. From there, Save writes it to the linked file without saving the model (asking where if there is none), Save as… writes it to another file and Copy puts it on the clipboard. Export SQL as… writes it straight to another file.

The format is fixed: tables in alphabetical order with their PK, UNIQUE and CHECK constraints, then their indexes, and every FK at the end as an ALTER TABLE. Statements Schevo doesn't draw (sequences, types, domains, extensions, views, functions, triggers…) are kept verbatim and edited in Project > Additional SQL statements….

Migration scripts

File > Migration script (ALTER)… (Ctrl+Shift+M) generates the statements that take an existing database from an earlier version of the model to the current one. The starting version can be a Git revision (HEAD, origin/develop, a tag…), the last saved version, another model or SQL script, or the current schema of a database (Database > Connect…), which gives exactly what is still to be applied to it.

Renames. Comparing two versions cannot tell whether a column was renamed or dropped and another one added, and dropping and adding loses its data. So Schevo looks for possible renames and lists them above the script: a dropped table and an added one with the same columns, or a dropped column and an added one of the same type. Clear matches are checked; doubtful ones are not. Checked renames are written as RENAME (including the PKs and FKs whose names change with the table) and the data is kept; unchecked ones stay as drop and add and are listed in a comment at the end of the script.

Always review the script before running it: a rename you don't check is lost (drop and add) and type changes can fail if the existing data cannot be converted.

Comparing versions

File > Compare with another version… (Ctrl+Shift+C) marks new tables (green), changed tables (amber, with the changed columns highlighted) and removed tables (dashed red) on the diagram. List of changes details every difference and takes you to the table. Positions and colors don't count as changes, and neither do DEFAULT and CHECK expressions written in another equivalent way (e.g. x BETWEEN 0 AND 4 and (x >= 0) AND (x <= 4)); they produce no statements in migration scripts either. You can compare with a Git revision, the last saved version, another model or SQL script, or a live database (Database > Connect…), for example to see what the production database is missing. A bundled sample lets you try it.

Export and data dictionary

• File > Export PNG image / SVG / PDF: the whole diagram, always in the light theme.
• File > Export data dictionary (HTML): a single file with the diagram, an index with search and, for every table, its columns, keys, indexes, constraints, comments and linked relationships.

Command line and CI

schevo-cli is installed next to the application:

validate and check need no license, so you can use them on any continuous integration machine. Pre-commit hook so the model is never pushed without its SQL:

#!/bin/sh
schevo-cli check db-model || { echo "Run: schevo-cli generate db-model"; exit 1; }

License

Schevo includes a 30-day trial with every feature. After that, without a license, it runs read-only: you can open, browse, search, compare and export images of your models, but not edit or save them, or generate SQL, migrations or the dictionary. Your files never become inaccessible.

Enter your key in Help > License… or with schevo-cli license --activate <key>. The key is checked without an Internet connection. A perpetual license works forever with the versions released during its update period; a subscription works until its expiry date.

A subscription's expiry date already includes a 14-day grace period after the paid period. During those last 14 days you can keep working as usual, but the status bar shows how many days are left and, at startup (at most once a day), a window reminds you to renew before Schevo switches to read-only mode. From it, Enter new key… opens Help > License… so you can activate the renewed key.

Updates

Help > Check for updates… asks the Schevo website for the latest version and tells you whether you are up to date. When there is a new one, it shows its release date and what's new, and Download opens the download page in your browser. Schevo never installs anything by itself.

By default Schevo also checks on startup, at most once a day and in the background. If there is a new version, a notice appears above the diagram; if the check fails (for example, without Internet), nothing is shown. To turn it off, uncheck Help > Check for updates automatically.

If you don't want to install a particular version, click Skip this version in the notice: you won't be notified about it on startup again, but you will be about the next one. Help > Check for updates… still shows it (saying that you skipped it), in case you change your mind and want to download it.

The check only downloads a small public file from the website: it sends neither your version nor any data about you or your models. Comparing versions happens on your computer.

Language, theme and preferences

• View > Language: Spanish, English, German or French (applied on restart). By default the system language is used.
• View > Dark mode (Ctrl+Shift+D).
• Schevo remembers the last model, the last folder, the theme, compact mode, the minimap and the size of the validation panel.

Recovery and troubleshooting

• While there are unsaved changes, a recovery copy is made every 2 minutes. If Schevo closes unexpectedly, it offers to recover it the next time you open it.
• If an unexpected error appears, the Copy report button copies the technical details so you can send them to support.
• The application log is in %LOCALAPPDATA%\Schevo\logs on Windows and in ~/.schevo/logs on Linux (Help > About Schevo > Open the log folder).