- Generated by
1.9.8
|
Lightweight 0.20261002.0
|
dbtool is a command-line utility for managing database migrations and performing backup/restore operations. It works with any ODBC-compatible database including SQLite, SQL Server, and PostgreSQL.
Key features:
Released artifacts ship dbtool and dbtool-gui together with every runtime DLL or shared object they need. They do not include an ODBC driver — you must install the appropriate driver for your database separately (e.g., Microsoft ODBC Driver for SQL Server, PostgreSQL ODBC, or SQLite ODBC).
dbtool-gui additionally offers managed backups: pick a single folder on the Settings page and the Backups page keeps one <profile>.zip per configured profile there, with one-click "back up all", safe atomic overwrite, and restore into any profile or an ad-hoc connection string. See src/tools/dbtool-gui/README.md for details; this is a GUI-only convenience layer over the same backup/restore primitives described below.
| Platform | Artifact | Install command |
|---|---|---|
| Windows (x86_64) | Lightweight-<version>-win64.msi | msiexec /i Lightweight-<version>-win64.msi |
| Debian / Ubuntu | lightweight_<version>_amd64.deb | sudo dpkg -i lightweight_<version>_amd64.deb |
| Fedora / RHEL | lightweight-<version>.x86_64.rpm | sudo dnf install lightweight-<version>.x86_64.rpm |
| Portable (Linux) | Lightweight-<version>-Linux.tar.gz | extract anywhere; run from bin/ |
Windows installs into C:\Program Files\Lightweight\bin\. Linux packages install into /usr/bin/ with Lightweight.so.<version> under /usr/lib/.
The binary will be located at out/build/clang-release/src/tools/dbtool/dbtool.
The resulting artefacts land in out/package/<preset>/.
dbtool requires a database connection string, which can be provided in three ways (in order of precedence):
--connection-string "..."SQL_CONNECTION_STRING or ODBC_CONNECTION_STRINGdbtool.yml, located as described belowThe first match wins:
--config <FILE> (a missing file is an error);dbtool.yml in the current directory or any parent directory — so a project can keep its own dbtool.yml at its root and every subdirectory picks it up;dbtool.yml in the directory of the dbtool executable or any parent directory — handy for portable installs that ship a config next to the binary;~/.config/dbtool/dbtool.yml (Linux/macOS, honouring $XDG_CONFIG_HOME) or APPDATA%\dbtool\dbtool.yml (Windows).dbtool list-profiles shows which file was used and why; --verbose prints it for every other command. dbtool-gui uses the same lookup unless a profile-store path is set in its Settings.
Legacy single-profile shape (still supported):
Multi-profile shape:
defaultPluginsDir also accepts a YAML sequence to fan out plugin discovery across multiple directories — handy when a vendor ships a shared plugin set under one prefix and the operator keeps local overrides under another:
When the same plugin filename appears in more than one of those directories, dbtool keeps the file with the newest modification time and discards the others — so dropping a fresher build into ./plugins reliably shadows the baseline copy without having to rebuild the YAML. Filename comparison is case-insensitive on Windows and case-sensitive elsewhere; when two same-named files share the same modification time, the directory listed earlier in the sequence wins.
Pass --verbose (-v) to see one stderr line per discarded duplicate, e.g.:
The effective plugin directory for a given run resolves as: --plugins-dir CLI option → profile's own pluginsDir → top-level defaultPluginsDir (possibly a list) → current working directory.
defaultBackupDir names a folder backups are written to, and a profile can override it with its own backupDir. This lets a deployment (an installer, say) pre-configure where backups go, so dbtool and dbtool-gui use the same folder without every user setting it up. The effective folder resolves like pluginsDir: the profile's own backupDir → top-level defaultBackupDir → none (the behaviour described below for --output).
--output, or for a generated name when --output is omitted. A path with a directory part is used exactly as given. The folder is created when it is missing.dbtool-gui uses defaultBackupDir as its default backup folder, unless a folder was chosen in its Settings. It keeps every profile's archive in one folder, so a per-profile backupDir is not used there.--connection-string without --profile applies no profile and therefore no backup folder.A profile can carry its password in dbtool.yml. dbtool stores it encrypted:
You never need to produce the enc: value by hand:
dbtool add-profile** prompts for the password and writes the profile with it encrypted (see add-profile).password: hunter2, dbtool uses it as is and — once a connection with it has succeeded — rewrites that one value in place as enc:.... The rest of the file (comments, ordering, formatting) is left untouched. A password that fails to connect is left alone. If the file is read-only, dbtool warns and carries on. If the file lives in a git repository, dbtool warns that the old plaintext may remain in its history — change the database password in that case. dbtool-gui follows the same rule: it encrypts the value as soon as a connection with it succeeds, whether that is its interactive connect or the start of a managed backup or restore.password and secretRef are mutually exclusive. secretRef (env:, file:, stdin:) remains the choice for secrets that must not be in the file at all. list-profiles shows each profile's AUTH as encrypted, plaintext, secretRef or -.
Values are encrypted with AES-256-CBC + HMAC-SHA256 (encrypt-then-MAC) using only the operating system's cryptography (Windows CNG, OpenSSL libcrypto on Linux, CommonCrypto on macOS). The master key is built into official dbtool / dbtool-gui binaries by CI; nothing on the user's machine is needed, so the same dbtool.yml works on every machine.
| Someone who has… | can read the password? |
|---|---|
only the dbtool.yml (repository, share, ticket, screenshot) | No |
| the dbtool source code | No — the key is not in the repository |
modified an enc: value | No — tampering is detected and the connection is refused |
| an official dbtool / dbtool-gui binary | Yes — the key is embedded in it |
In other words, the encryption keeps passwords out of plain sight in configuration files; it is not a vault. Use secretRef when holders of the binary must not be able to recover a password.
Builds made without the CI key (local developer builds, forks, distribution packages) use a public development key and write enc:dev:... values. Official builds refuse those with a clear message; re-run dbtool add-profile --force, or replace the value with the plaintext password so it is re-encrypted on the next connect.
The key ring is read when CMake configures the project, in exactly one of two ways:
| Input | Typical use |
|---|---|
DBTOOL_MASTER_KEYS environment variable: id=<64 hex>[,id=<64 hex>...] | GitHub Actions secret exported to the configure step |
-DDBTOOL_MASTER_KEYS_FILE=<path> (or the env var of the same name): a file with one id=<64 hex> per line | GitLab "File" variables; package ports (vcpkg/Conan) that strip the environment but forward CMake options |
Generate a key with openssl rand -hex 32. Ids are [a-z0-9], at most 16 characters; dev is reserved. The first entry encrypts and every entry decrypts, so to rotate, prepend a new key (v2=...,v1=...), ship that build, then re-encrypt profiles at leisure. Only the file path is stored in CMakeCache.txt; the keys only land in a generated header inside the build tree, which is never installed. Configuring with neither input prints dbtool: no master key provided - using the public development key. A build tree that was once configured with a key ring refuses to be re-configured without one (so an automatic re-configure cannot silently produce a development-key build); use a fresh build directory, or -U DBTOOL_KEYRING_WAS_RELEASE, when you really want that.
Use list-profiles to enumerate every profile parsed from the configuration file. The command does not open a database connection, so it works against any platform. Values of PWD= / Password= inside a profile's raw connectionString are redacted to *** in the output:
BACKUPDIR is the profile's effective backup folder: its own backupDir, else defaultBackupDir.
Use --config <FILE> to inspect a non-default configuration file. When no config file is found, list-profiles prints where it looked and exits successfully.
SQLite:
SQL Server:
PostgreSQL:
Apply all pending migrations:
Use --dry-run to preview SQL without executing:
On success, migrate prints a status summary (registered, applied, pending, latest release, checksum verdict) so you can confirm the database's current state in a single command. If there are no pending migrations, it explicitly reports that the database is already up to date instead of "Applied 0 migrations.".
The --schema <NAME> flag pins the connection's default schema for the migration runner — useful when the target database expects unqualified DDL/DML to land in a non-default schema (e.g. lasa instead of dbo / public):
Per-backend behaviour:
| Backend | What --schema does for migrations |
|---|---|
| PostgreSQL | Emits SET search_path TO "<schema>", public on every new connection. The schema_migrations history table is created here and unqualified DDL inside migrations lands here too. |
| SQL Server | No session-level switch is portable. Use the login's server-side DEFAULT_SCHEMA (ALTER USER … WITH DEFAULT_SCHEMA = lasa). --schema is still accepted (it qualifies table names in backup/restore archives) but does not relocate schema_migrations. |
| SQLite | No-op — SQLite has no schema concept beyond attached databases. |
Schema names are validated against [A-Za-z0-9_] to keep them safe to interpolate into SET search_path. Anything else is rejected.
The same flag also applies to apply, rollback, migrate-to-release, rollback-to-release, status, and the backup/restore commands (where the schema additionally qualifies table names inside the archive).
Apply pending migrations up to (and including) the named release. Forward-only: if the database is already at or past the target release, the command is a no-op and prints a hint pointing at rollback-to-release. Pair with --dry-run (-n) to preview the SQL without touching the database.
The release version must match a LIGHTWEIGHT_SQL_RELEASE(...) declaration shipped by one of the loaded plugins. Use dbtool releases to list them.
If a pending migration whose timestamp is <= release.highestTimestamp declares a dependency on a migration whose timestamp is > the release boundary (and is not already applied), migrate-to-release refuses to run rather than applying a partial state that violates the dependency contract.
List migrations waiting to be applied:
List migrations that have been applied:
Show migration status with checksum verification:
Outputs:
Apply a specific migration by timestamp:
Revert a specific migration:
Rollback all migrations applied after the specified timestamp:
The target migration itself is NOT reverted.
Mark a migration as applied without executing its SQL:
Useful for:
Rolls back every migration applied after the named release:
The release's own migrations are kept; only what came after is reverted.
Lists the releases declared by the registered migrations, with each one's migration count and whether it is fully applied:
Rewrites schema_migrations.checksum so the stored checksums match what the current code generates:
Use this only after a regeneration that changed the byte shape of a migration without changing its meaning — for example a formatting change in generated DDL. If the logic actually changed, the checksum mismatch is a real warning and rewriting it hides a genuine divergence. Because it edits migration bookkeeping, it refuses to run without --yes — there is no interactive prompt, the command simply exits. See Checksum Mismatches.
Drops every table the registered migrations own, plus the schema_migrations table, and leaves tables it does not own untouched:
The pairing with migrate is the point: hard-reset returns the database to "no migrations
applied" so the full set can be replayed from scratch. Which tables count as migration-owned is computed by folding the registered migration plan, not by guessing from the live schema, so user tables survive. Like rewrite-checksums, it refuses to run without --yes.
--dry-run prints the three groups it computed — tables to drop, migration-declared but absent, and user-owned tables it will preserve — without touching anything.
Rewrites legacy VARCHAR/CHAR columns to NVARCHAR/NCHAR where the registered migrations now declare wide types:
This exists for databases created before a migration switched a column to a wide type: the migration history is already marked applied, so nothing would otherwise re-run to widen the existing columns. Run it with --dry-run first to see the planned ALTER statements.
On SQL Server a column cannot change between narrow and wide text while anything depends on it. The command therefore reads every dependent object from the catalog — primary-key, unique, foreign-key (on either side, composite included), default and check constraints, indexes (with their included columns and filters) and statistics — drops them, alters the columns and recreates each object under its original name with its original definition, all in one transaction. The dry run lists those objects. Objects it cannot recreate safely — schema-bound views or functions, computed columns, columnstore, XML, spatial and full-text indexes — stop the command before anything changes; drop them, run it, and recreate them yourself.
Executes an SQL query and prints any result set:
Pass - as the argument, or omit it entirely, to read the query from stdin:
Lists the profiles defined in the configuration file (see Configuration):
Adds a profile to dbtool.yml (the file found as described in Where dbtool finds `dbtool.yml`, or --config; created if missing) with its password encrypted. Existing comments and formatting are preserved.
| Option | Meaning |
|---|---|
--name <NAME> | Profile name (required) |
--connection-string <STR> / --dsn <DSN> | Exactly one is required. A PWD= inside the connection string is moved into the encrypted password field |
--uid <UID> | User name for a DSN profile |
--schema <S>, --plugins-dir <DIR>, --backup-dir <DIR> | Stored on the profile (--backup-dir becomes its backupDir) |
--no-password | Store the profile without a password |
--set-default | Make it the defaultProfile |
--force | Replace an existing profile of the same name |
Passwords are never accepted as command-line arguments.
Resolves a single secret reference and prints it to stdout, without connecting to any database:
This is a debugging aid for configuration: it lets you confirm that a env: / file: / stdin: reference in a profile resolves to what you expect, before a connection failure sends you looking in the wrong place.
Create a compressed backup of the database:
Where the file goes. When dbtool.yml names a backup folder for the selected profile (`backupDir` / `defaultBackupDir`):
The folder is created if it is missing, and the file that was written is printed. Without a configured folder --output is required and is used as given.
Compression methods: none, deflate, bzip2, lzma, zstd, xz
Filter tables with wildcards:
Restore a database from backup:
Restore to a different schema:
Restore only specific tables:
Compare the data content of two backup archives to detect silent data corruption — for example, to prove that a concurrent (multi-threaded) backup contains the same rows as a safe single-threaded baseline:
This is a pure file comparison: it opens no database connection.
The comparison is order-independent. Two backups of the same database can legitimately emit rows in a different order and split them into different chunks (different pagination, worker interleaving), so chunk-level checksums would diverge even when the data is identical. Instead, backup-diff compares the multiset of rows per table:
data/<table>/NNNN.msgpack chunks, serialized to a canonical, length-prefixed, type-tagged byte encoding (with an explicit NULL marker so e.g. string "12" followed by int 3 can never collide with string "123"), hashed with SHA-256, and counted into a digest -> count map. The two maps are then compared. Only digests and counts are retained, so memory stays proportional to the number of distinct rows — archives with millions of rows remain tractable. One table is processed at a time on both sides; neither archive is held in memory in full.It prints a summary line (N tables compared, M identical, K differing) and exits 0 when every common table is identical and both archives hold the same set of tables, or 1 otherwise.
| Option | Description | Default |
|---|---|---|
--connection-string <STR> | ODBC connection string | |
--schema <NAME> | Database schema to use | |
--config <FILE> | Path to configuration file | nearest dbtool.yml upward, then the per-user file (see Configuration) |
--plugins-dir <DIR> | Directory to scan for migration plugins | . (current directory) |
--output <FILE> | Output file for backup. A bare file name, or no --output, goes into the profile's backupDir / defaultBackupDir when one is configured | |
--input <FILE> | Input file for restore | |
--left <FILE> | First (baseline) backup archive for backup-diff | |
--right <FILE> | Second (candidate) backup archive for backup-diff | |
--filter-tables <PATTERN> | Table filter (wildcards supported) | * (all tables) |
--jobs <N> | Number of concurrent jobs | 1 |
--compression <METHOD> | Compression method for backup | deflate |
--compression-level <N> | Compression level (0-9) | 6 |
--chunk-size <SIZE> | Chunk size for backup data | 10M |
--progress <TYPE> | Progress output: unicode, ascii, logline | unicode |
--quiet, -q | Suppress progress output | |
--dry-run, -n | Preview without executing | |
--no-lock | Skip migration locking | |
--schema-only | For backup/restore: skip data, transferring schema only | |
--memory-limit <SIZE> | Memory limit for restore (accepts the size suffixes below) | |
--batch-size <N> | Rows per batch for restore | |
--ignore-table <NAME> | For backup-diff: report differences in this table but do not fail. Repeatable. | |
--profile <NAME> | Named profile from the configuration file | store default |
--up-to <TIMESTAMP> | Upper bound for migration commands | no bound |
--max-retries <N> | Maximum retry attempts for transient errors | 3 |
--verbose, -v | Emit extra informational output (e.g. shadowed plugins) | |
--yes, -y | Confirm destructive actions without prompting | |
--show-examples | Print usage examples and exit | |
--help | Show help message |
The --chunk-size option accepts size suffixes:
1024 or 1024B10K or 10KB10M or 10MB1G or 1GBMigrations can be packaged as shared library plugins. dbtool scans the plugins directory for .so, .dll, or .dylib files.
LIGHTWEIGHT_SQL_MIGRATION macroLIGHTWEIGHT_MIGRATION_PLUGIN() in exactly one source fileA plugin may export an optional LightweightMigrationPluginPostInit symbol that dbtool calls once per invocation, after schema_migrations has been created and a live connection is available. Use this for one-shot bridging work that needs both a SqlConnection and the merged MigrationManager — for example, importing a legacy version-tracking table into schema_migrations.
The hook is optional — plugins that don't export the symbol behave exactly as before. Per-plugin exceptions are logged to stderr but do not abort dbtool.
For large databases, use multiple jobs:
If dbtool status reports checksum mismatches, there are two quite different causes — check the second one first, because it is benign and easy to mistake for the first.
1. You upgraded Lightweight and the generated SQL changed. The checksum is computed over the SQL text that the formatter renders for a migration, not over your C++ source. So a library release that changes emitted DDL re-hashes every already-applied migration that uses the affected construct, even though nobody touched the migration. Example: the PostgreSQL formatter now emits BIGSERIAL instead of SERIAL for a Bigint auto-increment key, so every PostgreSQL migration using PrimaryKeyWithAutoIncrement reports a mismatch after upgrading past that change. Nothing is out of sync and nothing breaks — mismatches are reported, not enforced. Confirm the mismatching migrations are exactly the ones touched by the release, then re-baseline:
2. A migration really was modified after it was applied. Then the database schema may genuinely be out of sync with the code. Review the change and create a new migration instead of editing the old one; do not run rewrite-checksums, which would erase the evidence.
If migration locking fails:
--no-lock to skip locking (only if you're certain no other migrations are running) 1.9.8