README

Symfony bundle that integrates 22h/questdb-client and 22h/questdb-migration (both 0.3): a configured client, migrations with dependency injection and locking, console commands and a web profiler panel.

This repository is experimental. Until 1.0, the configuration may still change in minor versions; see the changelog.

Requirements

PHP 8.4 or higher
Symfony 7.4 or 8
PSR-17 factories, e.g. nyholm/psr7 (the bundle sends the requests with symfony/http-client)

Installation

composer require 22h/questdb-bundle

// config/bundles.php
return [
    // ...
    TwentyTwo\QuestDBBundle\QuestDBBundle::class => ['all' => true],
];

Configuration

# config/packages/questdb.yaml
questdb:
    client:
        url: '%env(QUESTDB_URL)%'      # default: http://localhost:9000
        username: ~                    # basic auth
        password: ~
        token: ~                       # or a bearer token, not both
        timeout: ~                     # maximum duration of a request in seconds, no limit when null
        logger: ~                      # null: channel "questdb", false: no logging, or a logger service id
        http_client: ~                 # service id of a PSR-18 client, instead of the built-in one
    profiler:
        enabled: true                  # only active in debug mode with the web profiler
        schema: true                   # also show tables and migrations (a few catalog queries per request)
    migrations:
        paths:
            'App\QuestDBMigrations': '%kernel.project_dir%/migrations/questdb'
        table_name: migration_versions
        lock: true                     # use symfony/lock when it is configured
        logger: ~

migrations: false disables the migrations.

The client and the migration manager log to the Monolog channel questdb: every request with its SQL and duration (debug), rejected queries (info) and connection errors (warning).

timeout limits the whole request including the response (max_duration of symfony/http-client). For everything else (TLS, proxy, connect timeout, middleware) pass your own PSR-18 client with http_client; timeout is then configured on that client. Failed requests are never retried: inserts also go through /exec, a retry after a dropped connection could write twice.

Several clients

questdb:
    clients:
        default:
            url: '%env(QUESTDB_URL)%'
            timeout: 3
        reports:
            url: '%env(QUESTDB_URL)%'
            timeout: 30
            logger: 'monolog.logger.questdb_reports'

client: is the short form of clients: { default: ... }. Every client gets the options above. Inject a client by its name like Doctrine connections: Client $reportsClient gets the client reports, Client $client (any other name) the client default (or the first one when there is no default). The services are questdb.client.<name>.

Running SQL from the console

questdb:query runs any SQL: queries print their rows, other statements (DDL, INSERT, UPDATE, ...) the message of QuestDB.

bin/console questdb:query "SELECT * FROM trades WHERE symbol = :symbol" --param symbol=BTC
bin/console questdb:query "SELECT * FROM trades LIMIT 3" --format=vertical
bin/console questdb:query "SHOW TABLES" --client=reports
bin/console questdb:query "SELECT * FROM trades" --format=csv --limit=0 > trades.csv
echo "SHOW TABLES" | bin/console questdb:query

Formats: table (default), vertical (one block per row, for wide rows), json and csv. With json and csv, only the data goes to stdout, notes go to stderr.
At most --limit rows are fetched (default 100, 0 for all); the limit is applied by QuestDB and the total number of rows is shown anyway.
Parameters are bound by name. Integers, decimals, true, false and null keep their type, everything else is bound as string.
For UPDATE on WAL tables, QuestDB does not report the number of changed rows, so the count is shown with a note.

Migrations

bin/console questdb:migrations:status [--fail-on-pending]
bin/console questdb:migrations:migrate [VERSION] [--dry-run]
bin/console questdb:migrations:rollback [--steps=N | --to=VERSION] [--dry-run]
bin/console questdb:migrations:generate [--namespace=NAMESPACE]

status --fail-on-pending exits with code 1 when migrations are pending, e.g. to check a deployment. rollback asks for confirmation, because down() usually deletes data. generate creates an empty migration class named after the current UTC time (Version20261009143000) in the first configured directory, or in the directory of --namespace.

A migration is a class implementing TwentyTwo\QuestDBMigration\MigrationInterface in one of the configured directories; the short class name is its version (see the migration library).

Dependency injection: migrations that are registered as services (e.g. by resource: '../src/' with autoconfiguration) are created by the container and can have constructor dependencies. Other migrations are instantiated without arguments.

Locking: when symfony/lock is installed and configured, migrations run under the lock questdb-migration-<set> (questdb-migration-default), so two deployments cannot migrate at the same time. Use a store all servers share.

Migration sets

Every QuestDB instance keeps its own version table, so migrations for several instances are configured as sets:

questdb:
    migrations:
        default:
            paths: { 'App\QuestDB\Migrations': '%kernel.project_dir%/migrations/questdb' }
        analytics:
            client: analytics
            paths: { 'App\Analytics\Migrations': '%kernel.project_dir%/migrations/analytics' }

Each set has the options client (default: the default client), paths, table_name, lock and logger. The short form above (migrations: { paths: ... }) is the set default. All commands take --set=<name>, without it they work on the set default. MigrationManager $analyticsMigrationManager injects the manager of a set, MigrationManager $migrationManager the one of default.

To keep the same schema on several instances, configure two sets with the same directory and different clients. There is deliberately no "one set on several clients": after a failure, the instances would be in different states.

Health check

TwentyTwo\QuestDBBundle\Health\QuestDBHealthChecker runs the checks for the health endpoint of an application and never throws:

Key	Status
`questdb:connection`	`fail` when QuestDB is not reachable or rejects queries, otherwise the latency in ms
`questdb:wal`	`fail` for suspended WAL tables
`questdb:materialized_views`	`warn` for invalid materialized views
`questdb:migrations`	`warn` for pending migrations (normal for a moment during a deployment)

With several QuestDB instances or migration sets, the name is added to the component (questdb.analytics:connection, questdb.analytics:migrations). Clients with the same URL are checked once. The output never contains the QuestDB URL or the exception message, only what is wrong.

$report = $this->questDbHealth->check();
$report->getStatus();                       // HealthStatus::Pass, Warn or Fail: the worst result
foreach ($report->results as $result) {
    $result->key;                           // "questdb:wal"
    $result->status;                        // HealthStatus
    $result->output;                        // "Suspended WAL tables: trades"
    $result->observedValue;                 // 1
    $result->observedUnit;                  // "tables"
}

Web profiler

In debug mode with the web profiler, the QuestDB panel shows:

Queries of the request with duration, HTTP status, the error message of QuestDB for rejected queries and, with several clients, the client.
Tables of every QuestDB (clients with the same URL are read once): type (table or materialized view), partitioning, WAL, rows, size, latest row, and whether a table is suspended or a view is invalid.
Migrations: executed and pending versions of every migration set.

The toolbar turns red for failed queries and suspended tables, and yellow for pending migrations and invalid views. Collecting tables and migrations costs a few catalog queries per request; turn it off with questdb.profiler.schema: false.

Development

composer install
composer check                  # code style, PHPStan, unit and functional tests
docker compose up -d            # QuestDB for the integration tests
QUESTDB_URL=http://localhost:9000 composer test-integration

License

This project is licensed under the MIT License.

22h / questdb-bundle

Package info

Security

Statistics