Conversion Rules

In this guide, you will write conversion rules to adapt the PostgreSQL catalog to your needs. Please take a look on previous Get Started guide to initialize a project with a ready-to-go TOML configuration file.

1. Rename objects

To rename an object, such as a schema or a table, you can define a conversion rule in your configuration file. For example, to rename a schema from sakila to public, add the following rule in your pg_migrate.toml file:

[[Convert.Rules]]
Path = "Schemas/sakila"
Name = "public"

Path reflects the internal path of an object after the source database inspection. You may use it as an unique identifier to choose the object to configure. See Rules.Path reference for more details.

You must trigger catalog conversion to apply the new conversion rule. convert scans the source catalog stored in Source.json file, located under .pg_migrate folder, finding the sakila schema and its objects. The newly-converted tree is written to another file, Target.json.

$ pg_migrate --verbose convert
...
09:57:38 DEBUG  Converting object identifier.    from=sakila to=public path=Schemas/sakila
...
$

Generate DDL of this catalog with the dump command and --format=tree options to control the wanted conversions. Use --force to ignore all pending annotations.

$ pg_migrate dump --format=tree --schema-only --force
13:29:38 INFO   Auditing catalog.                driver=mysql catalog=PostgreSQL
13:29:38 WARN   Ignoring unhandled annotations in target model. len=62
13:29:38 INFO   Writing queries to directory tree. path=dump
...
13:29:38 INFO   Create foreign key.              path=Tables/public.payment/ForeignKeys/fk_payment_staff
13:29:38 INFO   Dump completed.                  elapsed=30ms tasks=139 jobs=4 mem=56.5MB sections=pre,post annotations=62
$

Files are located inside the dump/ folder. For instance, the film table will be correcty created in a public schema.

$ cat dump/Tables/public.film/create.sql

--
-- Name: film; Type: TABLE; Schema: public; Owner: pg_database_owner
--

CREATE TABLE "public"."film" (
  "film_id" integer NOT NULL GENERATED ALWAYS AS IDENTITY,
  "title" varchar(255) NOT NULL,
  "description" text,
  "release_year" smallint,
  "language_id" smallint NOT NULL,
  "original_language_id" smallint,
  "rental_duration" smallint NOT NULL,
  "rental_rate" numeric(4, 2) NOT NULL,
  "length" integer,
  "replacement_cost" numeric(5, 2) NOT NULL,
  "rating" text,
  "special_features" text[],
  "last_update" timestamp NOT NULL
);

ALTER TABLE "public"."film" OWNER TO "pg_database_owner";

2. Change column types

Sometimes, you may need to change a column datatype, such as a bigger integer or a date without time. You can choose to change a specific column from its path and the Type attribute or provide a global data type conversion rule with the Convert.DataTypes parameter.

For example, we can increase the email column size up to 255 characters in customer and staff tables.

[[Convert.Rules]]
Path = "Tables/sakila.customer/Columns/email"
Type = "varchar(255)"

[[Convert.Rules]]
Path = "Tables/sakila.staff/Columns/email"
Type = "varchar(255)"

$ pg_migrate --verbose convert
...
16:06:12 DEBUG  Converting column type.          from=varchar(50) to=varchar(255) path=Tables/sakila.customer/Columns/email
16:06:12 DEBUG  Converting column type.          from=varchar(50) to=varchar(255) path=Tables/sakila.staff/Columns/email
...
$

The following configuration defines a rule adding time zone to any column named timestamp.

[Convert.DataTypes]
"timestamp" = "timestamp with time zone"

$ pg_migrate --verbose convert
...
16:06:12 DEBUG  Converting column type.          from=timestamp to="timestamp with timezone" path=sakila/Tables/actor/Columns/last_update
16:06:12 DEBUG  Converting column type.          from=timestamp to="timestamp with timezone" path=sakila/Tables/address/Columns/last_update
16:06:12 DEBUG  Converting column type.          from=timestamp to="timestamp with timezone" path=sakila/Tables/category/Columns/last_update
16:06:12 DEBUG  Converting column type.          from=timestamp to="timestamp with timezone" path=sakila/Tables/city/Columns/last_update
...
$

As a result, tables like staff will respect conversion rules during the dumping step.

$ cat dump/Tables/public.staff/create.sql

--
-- Name: staff; Type: TABLE; Schema: public; Owner: pg_database_owner
--

CREATE TABLE "public"."staff" (
  "staff_id" smallint NOT NULL GENERATED ALWAYS AS IDENTITY,
  "first_name" varchar(45) NOT NULL,
  "last_name" varchar(45) NOT NULL,
  "address_id" integer NOT NULL,
  "picture" bytea,
  "email" varchar(50),
  "store_id" smallint NOT NULL,
  "active" boolean NOT NULL,
  "username" varchar(16) NOT NULL,
  "password" varchar(40),
  "last_update" timestamp NOT NULL
);

ALTER TABLE "public"."staff" OWNER TO "pg_database_owner";

Congratulation! You performed the basic configuration of your migration project. You should read Convert reference to go deeper with conversion rules.