Jobs and Relations

Define what to archive: the root table, the filter that selects rows, and the related tables that belong with them.

A job describes one set of data to move: where it starts, which rows qualify, and what belongs with them.

jobs:
  archive_old_orders:
    root_table: orders
    primary_key: id
    where: "created_at < DATE_SUB(NOW(), INTERVAL 2 YEAR)"
    relations:
      - table: order_items
        primary_key: id
        foreign_key: order_id
        dependency_type: "1-N"

The job name — archive_old_orders here — is how you select it on the command line, and how GoArchive tracks its progress.

Job settings

Setting	Description	Required
`root_table`	The table the job starts from	Yes
`primary_key`	That table’s primary key column	Yes
`where`	The filter selecting rows to move	Yes
`relations`	Tables whose rows belong with the root rows	No

The filter

where is a SQL condition applied to the root table. It is required — there is no implicit “everything”, so a job always states its intent.

where: "created_at < DATE_SUB(NOW(), INTERVAL 2 YEAR)"
where: "status = 'closed' AND closed_at < '2024-01-01'"
where: "1=1"          # deliberately, the whole table

Only the root table is filtered. Related rows are selected by their relationship to the matching root rows, not by a filter of their own.

The primary key

primary_key names the column that identifies a row. It must be the table’s actual primary key, spelled exactly as the database spells it — including capitalisation.

The root table’s key must be an integer type. Related tables may use any single-column key.

Relations

Each relation names a table, the column that identifies its rows, and the column that points back to the parent.

Setting	Description	Required
`table`	The related table	Yes
`primary_key`	Its primary key column	Yes
`foreign_key`	The column referring to the parent	Yes
`dependency_type`	`1-1` or `1-N`	No
`relations`	Tables related to this table	No

Nesting

Relations nest to any depth your data requires. A nested relation belongs to the table it sits inside, not to the root:

jobs:
  archive_old_orders:
    root_table: orders
    primary_key: id
    where: "created_at < DATE_SUB(NOW(), INTERVAL 2 YEAR)"
    relations:
      - table: order_items
        primary_key: id
        foreign_key: order_id
        dependency_type: "1-N"

      - table: shipments
        primary_key: id
        foreign_key: order_id
        dependency_type: "1-1"
        relations:
          - table: shipment_items
            primary_key: id
            foreign_key: shipment_id
            dependency_type: "1-N"

shipment_items refers to shipments, so it nests inside it. Getting this right matters: the nesting determines the order in which tables are written and removed.

Confirm the structure before running:

goarchive plan -c archiver.yaml --job archive_old_orders

validate additionally checks that the nesting you declared matches the relationships the database actually has.

Naming

Table and column names may contain letters, digits, and underscores. Names are given unqualified — a job works within the databases named in source and destination.

Several jobs

jobs:
  archive_old_orders:
    root_table: orders
    primary_key: id
    where: "created_at < DATE_SUB(NOW(), INTERVAL 2 YEAR)"

  archive_old_sessions:
    root_table: sessions
    primary_key: id
    where: "last_seen_at < DATE_SUB(NOW(), INTERVAL 90 DAY)"

Jobs run one at a time, each selected by name:

goarchive archive -c archiver.yaml --job archive_old_sessions

Job settings #

The filter #

The primary key #

Relations #

Nesting #

Naming #

Several jobs #