Skip to content

dbtool.md

Latest commit

 

History

History
659 lines (476 loc) · 22.2 KB

dbtool.md

File metadata and controls

659 lines (476 loc) · 22.2 KB
Raw

dbtool - Database Management CLI

Overview

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:

  • Apply, rollback, and manage SQL migrations
  • Full database backup with compression
  • Selective table backup and restore
  • Parallel processing for large databases
  • Checksum verification for migration integrity

Installation

Pre-built installers

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

Building from source

cmake --preset clang-release
cmake --build out/build/clang-release --target dbtool

The binary will be located at out/build/clang-release/src/tools/dbtool/dbtool.

Producing the installer locally

# Linux (TGZ + DEB + RPM)
cmake --preset gcc-release
cmake --build --preset gcc-release
cpack --preset gcc-release

# Windows (WiX MSI)
cmake --preset clangcl-release -DLIGHTWEIGHT_BUILD_GUI=ON
cmake --build --preset clangcl-release
cpack --preset clangcl-release

The resulting artefacts land in out/package/<preset>/.

Configuration

Connection String

dbtool requires a database connection string, which can be provided in three ways (in order of precedence):

  1. Command-line option: --connection-string "..."
  2. Environment variable: SQL_CONNECTION_STRING or ODBC_CONNECTION_STRING
  3. Configuration file: ~/.config/dbtool/dbtool.yml (Linux) or %APPDATA%\dbtool\dbtool.yml (Windows)

Configuration File Format

Legacy single-profile shape (still supported):

ConnectionString: "DRIVER=SQLite3;Database=mydb.db"
PluginsDir: ./plugins
Schema: myschema

Multi-profile shape:

defaultProfile: prod
defaultPluginsDir: ./plugins        # store-wide fallback for any profile
                                    # that omits its own `pluginsDir`
profiles:
  prod:
    schema: dbo
    connectionString: "DRIVER={ODBC Driver 18 for SQL Server};Server=...;Database=prod"
  dev:
    pluginsDir: ./dev-plugins       # per-profile override
    connectionString: "DRIVER=SQLite3;Database=dev.db"

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:

defaultPluginsDir:
  - ./plugins                       # local / operator-owned plugins
  - /opt/lightweight/plugins        # vendor-shipped baseline

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

plugin 'pricing.dll' from ./plugins/pricing.dll shadowed by /opt/lightweight/plugins/pricing.dll (newer mtime)

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.

Inspecting configured profiles

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:

$ dbtool list-profiles
Profiles (from /home/me/.config/dbtool/dbtool.yml):

NAME  DEFAULT  CONNECTION                            SCHEMA  PLUGINSDIR
prod  *        DRIVER={ODBC Driver 18 for SQL Se...  dbo     ./migrations
dev            DRIVER=SQLite3;Database=dev.db                ./dev-plugins

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.

Database-Specific Connection Strings

SQLite:

DRIVER=SQLite3;Database=/path/to/database.db

SQL Server:

DRIVER={ODBC Driver 18 for SQL Server};Server=localhost;Database=mydb;UID=sa;PWD=password;TrustServerCertificate=yes

PostgreSQL:

DRIVER={PostgreSQL Unicode};Server=localhost;Port=5432;Database=mydb;Uid=postgres;Pwd=password

Migration Commands

migrate

Apply all pending migrations:

dbtool migrate --connection-string "DRIVER=SQLite3;Database=test.db"

Use --dry-run to preview SQL without executing:

dbtool migrate --dry-run --connection-string "..."

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

Custom default schema (--schema)

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):

dbtool migrate --schema lasa --connection-string "..."

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

migrate-to-release <VERSION>

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.

dbtool migrate-to-release 1.0.0
dbtool migrate-to-release 1.0.0 --dry-run

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

List migrations waiting to be applied:

dbtool list-pending --plugins-dir ./plugins

list-applied

List migrations that have been applied:

dbtool list-applied

status

Show migration status with checksum verification:

dbtool status

Outputs:

  • Total registered migrations
  • Applied and pending counts
  • Checksum mismatches (if any migrations were modified after application)

apply <TIMESTAMP>

Apply a specific migration by timestamp:

dbtool apply 20260126120000

rollback <TIMESTAMP>

Revert a specific migration:

dbtool rollback 20260126120000

rollback-to <TIMESTAMP>

Rollback all migrations applied after the specified timestamp:

dbtool rollback-to 20260101000000

The target migration itself is NOT reverted.

mark-applied <TIMESTAMP>

Mark a migration as applied without executing its SQL:

dbtool mark-applied 20260126120000

Useful for:

  • Baseline migrations when setting up an existing database
  • Skipping migrations that were applied manually

rollback-to-release <VERSION>

Rolls back every migration applied after the named release:

dbtool rollback-to-release 1.0.0

The release's own migrations are kept; only what came after is reverted.

releases

Lists the releases declared by the registered migrations, with each one's migration count and whether it is fully applied:

dbtool releases

rewrite-checksums

Rewrites schema_migrations.checksum so the stored checksums match what the current code generates:

dbtool rewrite-checksums --yes

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.

hard-reset

Drops every table the registered migrations own, plus the schema_migrations table, and leaves tables it does not own untouched:

dbtool hard-reset --yes
dbtool migrate

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.

unicode-upgrade-tables

Rewrites legacy VARCHAR/CHAR columns to NVARCHAR/NCHAR where the registered migrations now declare wide types:

dbtool unicode-upgrade-tables --dry-run
dbtool unicode-upgrade-tables --yes

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.

exec <QUERY>

Executes an SQL query and prints any result set:

dbtool exec "SELECT COUNT(*) FROM Users"

Pass - as the argument, or omit it entirely, to read the query from stdin:

echo "SELECT * FROM Users WHERE id = 1" | dbtool exec -
dbtool exec < query.sql

list-profiles

Lists the profiles defined in the configuration file (see Configuration):

dbtool list-profiles

resolve-secret <REF>

Resolves a single secret reference and prints it to stdout, without connecting to any database:

dbtool resolve-secret env:DB_PASSWORD
dbtool resolve-secret file:/run/secrets/db_password
dbtool resolve-secret stdin:

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.

Backup & Restore

backup

Create a compressed backup of the database:

dbtool backup --output backup.zip --compression zstd --jobs 4

Compression methods: none, deflate, bzip2, lzma, zstd, xz

Filter tables with wildcards:

# Backup specific tables
dbtool backup --output backup.zip --filter-tables=Users,Products

# Backup tables matching patterns
dbtool backup --output backup.zip --filter-tables="*_log,audit*"

# Backup schema-qualified tables
dbtool backup --output backup.zip --filter-tables=dbo.Users,sales.*

restore

Restore a database from backup:

dbtool restore --input backup.zip --jobs 4

Restore to a different schema:

dbtool restore --input backup.zip --schema new_schema

Restore only specific tables:

dbtool restore --input backup.zip --filter-tables=Users,Products

backup-diff

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:

dbtool backup-diff --left backup_st.zip --right backup_mt.zip

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:

  • Tables present in only one archive are reported as a difference.
  • For each common table, every row is decoded from its 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.
  • For differing tables, the command reports the per-side row count, the number of rows present only on each side, and up to three example differing row digests.

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.

Command-Line Options Reference

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

Size Suffixes

The --chunk-size option accepts size suffixes:

  • Bytes: 1024 or 1024B
  • Kilobytes: 10K or 10KB
  • Megabytes: 10M or 10MB
  • Gigabytes: 1G or 1GB

Plugin System

Migrations can be packaged as shared library plugins. dbtool scans the plugins directory for .so, .dll, or .dylib files.

Creating a Migration Plugin

  1. Write migrations using the LIGHTWEIGHT_SQL_MIGRATION macro
  2. Add LIGHTWEIGHT_MIGRATION_PLUGIN() in exactly one source file
  3. Build as a shared library
// migrations.cpp
#include <Lightweight/SqlMigration.hpp>

LIGHTWEIGHT_MIGRATION_PLUGIN()

LIGHTWEIGHT_SQL_MIGRATION(20260126120000, "Create users table")
{
    plan.CreateTable("users")
        .PrimaryKeyWithAutoIncrement("id")
        .RequiredColumn("email", Varchar(255))
        .Timestamps();
}

Loading Plugins

dbtool migrate --plugins-dir ./build/plugins

Optional Post-Init Hook

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

#include <Lightweight/SqlMigration.hpp>

LIGHTWEIGHT_MIGRATION_PLUGIN()

LIGHTWEIGHT_MIGRATION_PLUGIN_POSTINIT(connection, manager)
{
    // Runs after CreateMigrationHistory(). Make it idempotent: dbtool invokes
    // this on every run, including `status`. Return early when there is nothing
    // to do.
    MyPlugin::BridgeLegacyState(manager, connection);
}

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.

Workflow Examples

Full Migration Workflow

# Check current status
dbtool status

# Preview pending migrations
dbtool migrate --dry-run

# Apply migrations
dbtool migrate

# Verify all checksums
dbtool status

Backup Before Migration

# Create backup
dbtool backup --output pre-migration-backup.zip --compression zstd

# Apply migrations
dbtool migrate

# If something goes wrong, restore
dbtool restore --input pre-migration-backup.zip

Parallel Backup and Restore

For large databases, use multiple jobs:

# Backup with 4 parallel workers
dbtool backup --output backup.zip --jobs 4

# Restore with 4 parallel workers
dbtool restore --input backup.zip --jobs 4

Troubleshooting

Connection Errors

  • Login failed: Verify username and password in connection string
  • Server not found: Check server address and port, ensure server is running
  • Driver not found: Install the required ODBC driver
  • Network error: Check firewall settings and network connectivity

Checksum Mismatches

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:

dbtool rewrite-checksums          # rewrites schema_migrations.checksum to match current code

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.

Lock Acquisition Failed

If migration locking fails:

  • Another migration may be running
  • Use --no-lock to skip locking (only if you're certain no other migrations are running)

See Also

  • sql-migrations.md - Guide to writing SQL migrations in C++
  • @ref Lightweight::SqlMigration::MigrationManager - C++ API for managing migrations