Skip to content

Contributing to PostgreSQL Migrator

Thanks for considering contributing to PostgreSQL Migrator! We accept all contributions, from bug reports to merge requests. The primary place to contribute is the GitLab repository at dalibo/pg_migrate.

Requirements

Depending on your contribution, you will need:

  • about 4GiB of free RAM to run Oracle
  • a Linux distribution
  • git
  • Docker and Docker Compose v2
  • Go 1.26+
  • Bun 1.3+
  • An editor respecting EditorConfig
  • air for hot reload of Go service
  • uv to build documentation
  • golangci-lint 2.11+ to lint Go code
  • GoReleaser 2.15 to build snapshot
  • usql to access different SGBD
  • sqlplus to access Oracle database
  • mise to manage environment and execute script

Getting the Code

We use git submodules for running e2e with bats. Ensure you have the submodules initialized.

$ git clone --recurse-submodules https://gitlab.com/dalibo/pg_migrate.git  # First clone
$ cd pg_migrate/
$ git submodule update --init  # in an existing clone

You may want to enable git hooks for development using test/githooks/install.sh. pre-push hook executes some linters depending on diff against remote upstream branch.

$ test/githooks/install.sh
'.git/hooks/pre-push' -> '/home/.../pg_migrate/test/githooks/pre-push'

pre-push hook assumes you create branch with remote tracking. Use git checkout -b my-feature origin/master to create a remote tracking branch my-feature. Use git branch --set-upstream-to=origin/master to enable remote tracking on current local branch. git rev-parse --abbrev-ref @{upstream} checks remote tracking.

Running your code

Run pg_migrate with go:

$ go run . --version
pg_migrate (PostgreSQL Migrator) (devel)
transqlate v0.7.2
$

Start Services

Run the following command to orchestrate development databases with Docker Compose:

$ docker compose up -d
[+] Running 4/4
 ✔ Network pg_migrate_default       Created     0.2s
 ✔ Container pg_migrate-postgres-1  Started     0.7s
 ✔ Container pg_migrate-oracle18-1  Started     0.7s
 ✔ Container pg_migrate-mariadb-1   Started     0.2s
$

Oracle Bloat

Running Oracle is heavy. Its Docker image is several gigabytes in size. The database takes minutes to get ready. Oracle is ready when docker compose logs -f oracle18 shows DATABASE IS READY TO USE!.

On lower configuration, just start MySQL and PostgreSQL services.

usql implements a universal psql-like client. It’s a good idea to have it. PostgreSQL Migrator uses the same backend and DSN.

Universal DSN for development source databases are:

  • maria://sakila:N0tSecret@localhost:3312/sakila
  • mysql://sakila:N0tSecret@localhost:3308/sakila
  • oracle://SAKILA:N0tSecret@localhost:1518/SAKILA?CONNECT_TIMEOUT=1&TIMEOUT=45

Use these to connect to source database using usql and to configure source DSN for the Migration project.

Reset databases

To reset databases, execute docker compose down -v and restart with docker compose up -d.

Initialize a Migration Project

$ go run . init --source=mysql://sakila:N0tSecret@localhost/sakila --target=postgresql://postgres@localhost my-migration/
13:59:27 INFO   Opening database connection pool. key=source driver=mysql dsn=my:sakila@localhost/sakila
13:59:27 INFO   Connecting to database.          key=source count=1
...
13:59:27 INFO   Initialized migration project.   source="mariadb.org binary distribution" version=12.1.2-MariaDB-ubu2404 path=my-migration
13:59:27 INFO   Databases connections closed.    source=1 target=1
$

Note

.gitignore excludes any file or directory prefixed with my-. Hence the name my-migration used for the project directory in order to keep generated files ignored by git.

Now use -C switch or PGMDIRECTORY env var to execute PostgreSQL Migrator on the project.

$ go run . -C my-migration/ inspect
14:01:19 DEBUG  Changing directory.              path=my-migration/
14:01:19 DEBUG  Read project.                    status=initialized path=my-migration driver=mysql online=true
14:01:19 DEBUG  Cache miss.                      name=Source
14:01:19 DEBUG  Cache miss.                      name=Target
14:01:19 INFO   Opening database connection pool. key=source driver=mysql dsn=my:sakila@localhost/sakila
14:01:19 INFO   Connecting to database.          key=source count=1
14:01:19 DEBUG  Released database connexion.     key=source usage=0/1
14:01:19 INFO   Inspecting source database.      driver=mysql
...
14:01:19 INFO   Auditing catalog.                driver=mysql
14:01:19 WARN   Target catalog has pending annotations. count=28
14:01:19 INFO   Databases connections closed.    source=2 target=0
14:01:19 INFO   Execute pg_migrate ui to browse project.
14:01:19 INFO   Execute pg_migrate dump to migrate for PostgreSQL.
$

And so on for any subcommand (convert, dump, etc.).

SQL style conventions

We follow the SQL style guide by Simon HOLYWELL.

Developing transqlate

transqlate is our SQL and procedural SQL transpiler. Use go work to run with a local clone of transqlate.

$ git clone https://github.com/dalibo/transqlate.git ../transqlate
$ go work init
$ go work use . ../transqlate/

Then use go run . to run pg_migrate with your local transqlate library.

To rollback and use transqlate library from go.mod, use go work edit -dropuse=../transqlate.

Executing CLI Tests

Running e2e tests requires GoReleaser. bats executes e2e tests written in test/cli directory. e2e tests run a released pg_migrate binary found in PATH. test/bats.sh script builds pg_migrate with GoReleaser and runs bats in a container.

$ test/bats.sh test/cli/oracle.bats
...
    • building                                       binary=dist/pg_migrate_linux_amd64_v1/pg_migrate
...
+ bats --print-output-on-failure --timing --jobs 2 --no-parallelize-within-files test/cli/oracle.bats
1..7
ok 1 init in 133ms
ok 2 status in 22ms
ok 3 inspect in 21313ms
ok 4 audit in 220ms
ok 5 report in 110ms
ok 6 convert in 41ms
ok 7 dump-postgres in 1019ms
+ teardown

See bats and bats-assert documentation for writing tests.

You can execute jtap tests with jq:

$ jq -rf test/cli/oracle/inspect.jq test/results/oracle/.pg_migrate/Source.json
ok - role XTRA exists
ok - schema XTRA exists
ok - IDX_ACTOR_LAST_NAME exists
ok - index size
ok - table size
ok - table row count
...

You can execute pgTAP tests with pg_prove:

$ pg_prove -d orakila test/cli/oracle/
test/cli/oracle/0_sakila.pg .. ok
test/cli/oracle/1_xtra.pg .... ok
All tests successful.
Files=2, Tests=7,  0 wallclock secs ( 0.01 usr  0.02 sys +  0.05 cusr  0.01 csys =  0.09 CPU)
Result: PASS

Developing UI

pg_migrate’s UI is a VueJS 3 SPA (Single Page Application). Building UI requires Bun. UI project is in internal/cmd/ui directory.

  • Install front dependencies with bun --cwd=internal/cmd/ui install.
  • Then build the UI with bun --cwd=internal/cmd/ui run build.

Now go run . will embed the UI in the binary. pg_migrate only serves static files from .pg_migrate directory and embedded assets.

Before running UI, you need to initialize and inspect a source database. See above. Then execute pg_migrate and vitejs dev server in two terminals:

  • Go backend with go run . -C my-migration ui
  • ViteJS dev server with bun --cwd=internal/cmd/ui run dev.

Go to localhost:5173 to browse the migration project.

You don’t need to restart pg_migrate if you modify the Vue 3 app or refresh catalog using pg_migrate’s CLI.

After a rebase on master, reset migration project quickly with .config/mise/tasks/reset.

When developing go backend, use air to restart the server on Go source changes.

$ air -c .config/air.toml -- -C my-migration ui

  __    _   ___
 / /\  | | | |_)
/_/--\ |_| |_| \_ v1.52.3, built with Go go1.22.5

...
17:12:33 INFO   Serving UI.                      url=http://localhost:8765

Code formatting and linting

The Javascript / Vue code format and syntax is checked with Biome and ESLint.

We advise you to configure your editor to use those tools.

Checks can be done with the following commands:

$ cd internal/cmd/ui
$ bun run lint
$ bun run format-check

Optionaly, you can fix the “fixable” errors with:

$ bun run lint --fix
$ bun run format

Unit Tests with Bun

We use Bun to run some unit tests in the UI. Unit tests are located in internal/cmd/ui/src/test/ and can be run from anywhere in the repository.

$ bun test

E2E Tests with Cypress

tests/ui/ holds a cypress project for PostgreSQL Migrator UI. Unlike suggested by cypress, we still use Bun to execute tests. Test suite requires a running pg_migrate ui on a converted Oracle project as produced by test/bats.sh. cypress tests do not access vite dev server, you must build UI first. You must start go run . ui before opening a new terminal for cypress execution.

$ bun --cwd=internal/cmd/ui run build
$ go run . ui

In another terminal:

$ cd test/ui/
$ bun install
bun install v1.2.13 (64ed68c9)

+ @types/bun@1.2.21
+ cypress@14.5.4
+ cypress-parallel@0.15.0

260 packages installed [1109.00ms]
$ bunx cypress open --e2e

Now browse, execute and write tests as documented in cypress documentation.

test/cypress.sh executes cypress tests headless, parallelized, as in CI.

Editing Documentation

The documentation is built with Material for Mkdocs and hosted on Read the Docs.

Conventions

We follow semantic line breaks convention. See sembr.org for more details. In complement, we use multiple lines before a new section, as described by the Python Developer’s guide.

Make use of multiple blank lines where applicable to clarify the structure of the file. Extra blank lines help group sections together to make the organization of the file clearer.

Building

Execute mkdocs using uv like this:

$ uv --directory=docs run mkdocs serve -sf ../mkdocs.yml
INFO    -  Building documentation...
...
INFO    -  [08:46:19] Serving on http://127.0.0.1:8000/

Browse to localhost:8000 to see the documentation.

Home Video

Landing page video shows dump of a specific fixture:

  • Oracle with sakila.
  • Two bigger tables from oracle-demo.sql. Schemas = ["SAKILA"] in pg_migrate.toml.
  • Just initialized migration project.
  • Empty postgres target instance.

Extend sakila fixture with the following command:

$ docker compose exec oracle18 sqlplus -s /nolog @test/docker/oracle-demo.sql
Warm up inspection until it’s fast. Resize the terminal to 136x38 characters. Then record inspect and dump --refresh-stats=false. Convert video to webm with ffmpeg -i video.mp4 docs/demo.webm. Avoid spamming git history with video by updating demo on major releases.

Big Dataset

Sakila fixture weighs a few megabytes. If you need to stress copy with a bigger dataset, use test/docker/*-big.sql scripts to add big tables in the database. After the execution of this script, run inspect --refresh command to update migration project with new tables. Big fixture is very slow to create, be patient.

Oracle

Once oracle18 service is up, execute oracle-big.sql like this:

$ docker compose exec oracle18 sqlplus -s /nolog @test/docker/oracle-big.sql

This generates about 4GiB of data.

MySQL

Execute test/docker/mysql-big.sql like this:

$ docker compose exec -t mysql sh -c 'mysql -usakila --password=N0tSecret sakila < /pg_migrate/test/docker/mysql-big.sql'

Idempiere Schema

If you need to stress UI with a lot of objects create Idempiere schema with the following steps:

$ docker compose run --rm idempiere-seed
$ docker compose exec oracle18 /pg_migrate/test/docker/idempiere.sh
$ go run . -C my-migration inspect

Unlikely, idempiere schema is called ADEMPIERE.

Explain Dump Plan

dump command has a hidden dot format to save plan as GraphViz dot graph. Use a tool like xdot or edotor to vizualize plan. Beware plan can be very big. See GraphViz interfaces reference for other interfaces.

$ go run . dump --format=dot
10:15:09 INFO   Auditing catalog.                driver=oracle
10:15:09 WARN   Ignoring unhandled annotations in target model. len=40
10:15:09 INFO   Dumping to file.                 path=dump.dot format=dot
$

Profiling

PostgreSQL Migrator has two profiling feature. The first is generic Go CPU, memory, goroutines and other system profiling. A second is an app profiling feature to visualize migration performances.

System Profiling

Enable profiling using PGMPPROF environment variable. Accepted values are : cpu, mem, blocks, goroutines, mutex and trace. Inspect data with go tool pprof or go tool trace.

$ PGMPPROF=cpu go run . dump
...
12:48:27 INFO   profile: cpu profiling disabled, cpu.pprof
$ go tool pprof -http=:8080 cpu.pprof
...

App Profiling

pg_migrate --profile produces Chrome JSON trace usable by performance visualisation tools. After dump, go to ui.perfetto.dev and vizualize profile.json file in project directory. Traces includes tasks and error events.

Updating Tools and Dependencies

When updating tools, runtimes and dependencies, ensure to update dependencies after tools and runtime. This ensures CI cache is effective for last tool version.

Releasing

  • Tag master branch on GitLab named v and containing a Semantic Version.
  • GitLab CI job release will do the work.
  • Install the new version on your machine.
  • Reset oracle18 database in your development environment, with adempiere fixture.
  • Update Sample report and online demo with .config/mise/tasks/demo.
  • Create a merge request with updated demo.

Governance

Current governance is private. Dalibo Labs is fully committed to PostgreSQL Migrator, it’s a major contribution. With significant contributions, we are open to share the governance of PostgreSQL Migrator. Get in touch.