- Generated by
1.9.8
|
Lightweight 0.20260625.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_STRING~/.config/dbtool/dbtool.yml (Linux) or APPDATA%\dbtool\dbtool.yml (Windows)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.
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:
Use --config <FILE> to inspect a non-default configuration file. With no config file present at the default location, list-profiles prints a friendly notice 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.
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):
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:
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 | ~/.config/dbtool/dbtool.yml |
--plugins-dir <DIR> | Directory to scan for migration plugins | . (current directory) |
--output <FILE> | Output file for backup | |
--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