October 6, 202611 min read

Manage YDB Schemas and Migrations with HCL

Use Ptah to generate YDB migrations from HCL, preserve existing rows, detect drift, and replay the migration history on a disposable database.

Simon HartSolutions Engineer

Ptah helps you plan, review, and apply database migrations. Try in your browser

An episode catalog starts with titles and air dates. Later it needs soft deletion, another index, and a view that hides removed episodes. Keeping a migration history gives each environment an ordered path through those changes. It does not give you one place to read the schema the application needs today.

With Ptah 0.13.0, you can describe that YDB schema in HCL and generate the migrations from it. Ptah reads the database, compares it with the declaration, and plans the transition. The resulting SQL still goes through review before it runs.

We’ll create an episode table, change it with rows already present, and add indexes and a view. Then we’ll check what happens when someone removes an index outside the migration process. The same files also let us generate a later change by replaying the history on a disposable YDB server.

The examples use Ptah 0.13.0 and YDB 26.2.1.14. The complete example files include each HCL state, generated migrations, and recorded output.

Start with a separate database

Install Ptah from the release or the installation guide. The binary includes the YDB driver. Use an empty, disposable YDB database; a declaration containing one demonstration table must not become the desired state of a shared application database.

In this example, YDB_URL is ydb://localhost:2136/local. It names the gRPC endpoint and the /local database. Use ydbs:// for TLS. The connection reference covers credentials and remote containers.

Work in a new directory with an empty migrations/ directory. Set YDB_URL to your test database and check the connection:

ptah db capabilities --db-url "$YDB_URL"

The result lists the capabilities Ptah resolves for that server. A server’s version and enabled features decide which operations it can accept.

Describe the first table

Save this complete declaration as schema.hcl:

schema "media" {}

table "episodes" {
  schema = schema.media

  column "series_id" {
    type = sql("Uint64")
    null = false
  }

  column "season_id" {
    type = sql("Uint64")
    null = false
  }

  column "episode_id" {
    type = sql("Uint64")
    null = false
  }

  column "title" {
    type = text
    null = true
  }

  column "air_date" {
    type = sql("Timestamp64")
    null = true
  }

  primary_key {
    columns = [column.series_id, column.season_id, column.episode_id]
  }

  index "episodes_title_idx" {
    columns = [column.title]
  }
}

schema "media" maps to a directory in YDB, so the table’s database-relative path is media/episodes. Names are case-sensitive. The sql(...) expressions supply YDB type names; they do not execute a query. The general text type maps to Utf8.

The primary key identifies an episode within its series and season. The index uses title as its key. YDB declares a new table’s index inside CREATE TABLE; an index added later uses ALTER TABLE ... ADD INDEX. The HCL declaration stays the same in both cases.

Validate and render the file without connecting to the database:

ptah schema validate --schema-file schema.hcl --dialect ydb --server-version 26.2.1.14
ptah schema render --schema-file schema.hcl --dialect ydb --server-version 26.2.1.14

Rendering shows the SQL for creating the declared schema. It does not ask which objects already exist. That is the planner’s job.

Generate, review, and apply the first migration

Read the live schema and inspect the proposed transition:

ptah migrations plan --schema-file schema.hcl --db-url "$YDB_URL"

The empty database needs one CREATE TABLE statement, with the index inside it. Planning writes neither database objects nor migration files. Generate the files once you are ready to review the transition:

ptah migrations generate --schema-file schema.hcl --db-url "$YDB_URL" --migrations-dir ./migrations --name create_episodes

The output directory now contains an .up.sql and a .down.sql with the same version. Review both, then calculate their checksums:

ptah migrations hash --dir ./migrations

Commit schema.hcl, both migration files, and ptah.sum together. Apply the reviewed history with checksum verification enabled:

ptah migrations up --db-url "$YDB_URL" --migrations-dir ./migrations --verify-sum --migration-lock-timeout 30s

--migration-lock-timeout bounds waiting for another migrator. After the apply, check the history and compare the actual schema with the declaration:

ptah migrations status --db-url "$YDB_URL" --migrations-dir ./migrations
ptah schema compare --schema-file schema.hcl --db-url "$YDB_URL"

The status output contains:

Total Migrations: 1
Applied Migrations: 1
Pending Migrations: 0

The comparison reports:

No schema differences detected.

Repeating the same migrations up command reports that the database is already up to date. It does not execute the migration again.

Add soft deletion without losing existing rows

Before the next change, we inserted Pilot and Second episode using the seed SQL. Both have the same series and season IDs, distinct episode IDs, and recorded air dates.

Add this column inside episodes and remove the episodes_title_idx block:

  column "is_deleted" {
    type    = bool
    null    = false
    default = false
  }

The complete second state includes those edits. Copy that state to schema.hcl and generate the next transition:

ptah migrations plan --schema-file schema.hcl --db-url "$YDB_URL"
ptah migrations generate --schema-file schema.hcl --db-url "$YDB_URL" --migrations-dir ./migrations --name add_episode_soft_delete

The generated up migration contains only the difference:

ALTER TABLE `media/episodes` DROP INDEX `episodes_title_idx`;
ALTER TABLE `media/episodes` ADD COLUMN `is_deleted` Bool NOT NULL DEFAULT false;

The default supplies a value for existing rows as well as later inserts that omit the column. Adding a column with a default depends on the target server’s capabilities; this example uses YDB 26.2.

After review, repeat the hash and apply commands from the first migration. Reading the table through the YDB CLI returned:

episode_id	title	is_deleted
1	Pilot	false
2	Second episode	false

The existing keys, titles, and air dates were unchanged. History now contained two applied migrations, another apply did nothing, and comparison with the updated HCL found no differences.

The new column does not tell Ptah which episodes should be hidden. A licensing rule or another business decision still belongs in application code or an explicit data migration.

Add YDB indexes and dependent objects

The third schema state builds on the second one. It adds an asynchronous covering index, a table with a vector index, a view, and a coordination node. The blocks below are excerpts from that complete file.

An asynchronous covering index

For a list ordered by air date, add this index to episodes:

  index "episodes_air_date_idx" {
    columns = [column.air_date]
    include = [column.title]
    type    = "async"

    auto_partitioning_by_load              = "ENABLED"
    auto_partitioning_min_partitions_count = 4
    auto_partitioning_max_partitions_count = 32
  }

include becomes YDB’s COVER clause. A suitable query can read the title from the index without fetching that column from the base table. type = "async" permits the index to lag behind table writes, so it needs to fit the application’s freshness requirements.

A global index has its own storage and partitioning settings. After applying the declaration, inspection returned GLOBAL ASYNC, the covering title column, load-based partitioning, and the configured minimum of 4 and maximum of 32 partitions.

A vector index

The complete file also declares search_documents, with a Uint64 key, a text body, and a nullable binary String column named embedding. Its index declaration is:

  index "search_documents_embedding_idx" {
    columns = [column.embedding]
    type    = "vector_kmeans_tree"

    distance         = "cosine"
    vector_type      = "float"
    vector_dimension = 1536
    levels           = 2
    clusters         = 128
  }

YDB stores the serialized vector in String; the column itself does not enforce its dimension. The application must write vectors in the expected format. The index declares cosine distance and 1,536 float elements, with two tree levels and 128 clusters.

Inspection after the migration preserved those settings, and the next schema comparison was empty. Vector-index support is separate from the Ptah inference workflow. YDB inference remains a separate task.

A view and a coordination node

The view keeps the soft-deletion filter with the database schema:

view "published_episodes" {
  schema = schema.media

  as = <<-YQL
    SELECT
        series_id,
        season_id,
        episode_id,
        title,
        air_date
    FROM `media/episodes`
    WHERE NOT is_deleted
  YQL
}

Ptah adds YDB’s required security_invoker = TRUE option. YDB view changes use replacement through drop and create, so the planner orders them with their dependencies.

The third state also includes an application coordination node:

coordination_node "media" "workers" {
  self_check_period     = "PT2S"
  read_consistency_mode = "strict"
}

Ptah manages this node’s configuration. Application sessions and semaphores are runtime state. The separate ptah_locks node used by the migrator is maintained by Ptah and does not belong in your declaration.

Replace schema.hcl with the complete third state and generate its migration:

ptah migrations generate --schema-file schema.hcl --db-url "$YDB_URL" --migrations-dir ./migrations --name add_search_and_workers --report html

The HTML safety report is written beside the migration. Review the SQL, update ptah.sum, and apply it using the earlier commands. Comparison with the HCL then found no differences. Direct application of the same declaration also had nothing to do:

ptah schema apply --schema-file schema.hcl --db-url "$YDB_URL"

Schema is synced, no changes to be made.

The view returned both episodes initially. After setting is_deleted = true on the second episode, it returned only Pilot. The base table still contained both rows.

Detect a change outside the migration history

A current migration history does not guarantee that the current schema matches it. To test the difference, we removed episodes_air_date_idx through the YDB CLI without changing HCL or the migration files.

migrations status still reported three applied versions and no pending migration. Check the schema itself:

ptah schema drift --schema-file schema.hcl --db-url "$YDB_URL"

This time the command exited 1:

Schema drift detected (highest severity: warning).
Failure threshold: all. Failing: true.
Database: ydb://localhost:2136/local

Findings:
- indexes_added: 1 (warning)

indexes_added describes the repair needed to reach the desired state: the missing index must be added. Running the earlier schema apply command showed the ADD INDEX statement and its partitioning settings. After confirmation with YES, Ptah restored the index. The next drift check exited 0:

No schema drift detected.

Direct application is an alternative workflow; it does not add a version to the migration directory. In a versioned deployment, compare the database with the HCL for the deployed release, rather than an unreleased change on the main branch. Adopt an intentional manual change into the desired schema instead of undoing it automatically.

Generate the next change by replaying history

A CI job can derive the starting schema from Git without connecting to a running application database. Ptah provisions a disposable YDB server, replays the migration directory, reads the result, and compares it with the new HCL.

The fourth state adds a nullable editor_note column of type text to episodes. With that state copied to schema.hcl and the first three migrations in the directory, run:

ptah migrations generate --replay --dev-url docker://ydb/26.2.1.14/local --schema-file schema.hcl --migrations-dir ./migrations --name next_change

This command uses the migration history as its source; it takes no --db-url. The Docker daemon must be local to the process running Ptah. The YDB dev database reference explains the container and directory-isolation rules.

The new migration contained one statement:

ALTER TABLE `media/episodes` ADD COLUMN `editor_note` Utf8;

Review the files and update ptah.sum. Then check formatting, replay the updated directory, and lint it:

ptah schema fmt --check schema.hcl
ptah migrations validate --dir ./migrations --dev-url docker://ydb/26.2.1.14/local
ptah migrations lint --dir ./migrations --dialect ydb --server-version 26.2.1.14

Validation returned:

OK: migrations directory matches ptah.sum
OK: migration SQL validated on dev database

The formatter and linter also exited 0; the linter reported No lint findings. Repeating generation with the same fourth state and updated history created no new files. The disposable YDB containers were removed after the commands.

Run hash when preparing a reviewed change. A CI verification job should check the existing checksum file, rather than recalculate it and accidentally accept an edited history.

Keep format limits separate from database support

HCL can describe the objects used above. It cannot express every property that Ptah supports on YDB. In 0.13.0, row deletion policies and column families are examples of properties that HCL leaves out.

We created a separate media/view_events table with a 30-day time-to-live (TTL) policy on created_at. Exporting the schema to HCL warned on standard error:

warning: table.media.view_events: row deletion policy (TTL P30D on created_at) is not represented in HCL

A complete HCL declaration included the table’s columns and key, but no TTL. Applying it made no changes. SHOW CREATE TABLE before and after returned the same policy. An HCL file’s inability to describe TTL does not request its removal. Desired YQL can declare TTL; omitting it from that declaration has a different meaning. The TTL reference describes the format-specific behavior.

Type mappings have limits too. Changing title from text to varchar(255) in the supplied schema-length.hcl control file does not make YDB enforce a maximum string length:

ptah schema validate --schema-file schema-length.hcl --dialect ydb --server-version 26.2.1.14 --no-skipped

This check exited 1 and reported:

ydb: column "media.episodes.title": type modifier=varchar(255): length 255 would be skipped

A property the target cannot enforce must stay visible in review. Reading Utf8 back from YDB cannot recover a length limit that the database never had.

Migration locks and transaction boundaries

Ptah coordinates YDB migrators through an exclusive semaphore held by a coordination-service session. Before the third migration, we started a second migrator while the first paused in its pre-apply hook under the lock. The second process used --migration-lock-timeout 2s and exited 2:

error: error running migrations: failed to acquire migration lock for migrate up: timed out acquiring migration lock "ptah_migrate" for ydb after 2s

The first process then applied the migration. History contained three applied versions, with no duplicate entry from the waiting process. This lock coordinates migrators using the same mechanism; it does not stop application writes or provide a fencing token for each schema request.

YDB schema statements run outside transactions and cannot share a query with data modifications. Ptah sends schema statements separately. Consecutive data operations run in transactions with their revision checkpoints. Recorded progress supports resuming an interrupted migration, but a lost response to a schema operation can leave its outcome unknown. Inspect that outcome before retrying. The YDB migration reference explains those boundaries.

YDB also cannot change an existing column’s type or a primary key in place. An explicit --allow-table-rebuild can permit a supported row-table rebuild, with copying and renaming steps. Stop application writes for that operation: rows written between the copy and swap can be lost. Ptah refuses rebuilds that cannot preserve the table’s properties. A down migration restores supported structural changes; it cannot recover deleted rows or messages.

Use the same workflow for a wider YDB schema

Ptah also models column tables, full-text indexes, topics and consumers, changefeeds, external sources and tables, secrets, replication, and transfers. Their supported operations depend on the server version and enabled features. Use a format that can describe the required family: support in the YDB adapter does not imply an HCL block exists for it.

These objects bring dependencies and data consequences. A secret precedes an external source that refers to it. A changefeed topic belongs to its table. Recreating a stream can discard unread messages or a consumer position. Ptah plans the supported configuration changes; YDB and its connectors perform the federated queries and stream processing.

The YDB reference covers those families, capability flags, and the formats that represent them.

Put Ptah to work

Example files

← All articles