MySQL to PostgreSQL: Migrating an AdonisJS App

MySQL to PostgreSQL: Migrating an AdonisJS App

Chimezie Enyinnaya
Chimezie Enyinnaya August 2, 2026

Changing a database driver takes a few minutes. Migrating a production application does not.

An application can compile after you replace mysql2 with pg and still fail when it reads a raw query result, updates rows with a join, inserts a record without RETURNING, or encounters a value that violates a PostgreSQL constraint.

This happened while moving Mezie Labs, an AdonisJS 7 application from MySQL to PostgreSQL. Lucid handled most ordinary model queries. The difficult parts lived at the edges: raw SQL, old migrations, loose column types, driver-specific errors, and the live data cutover.

This article walks through the migration from audit to production. You will see what needed code changes, how the data moved with pgloader, and how to make the final cutover boring. Boring is exactly what you want when production data is involved.

Why a database migration is more than a driver swap

Both MySQL and PostgreSQL are relational databases. They share enough SQL to make a migration look smaller than it is.

The differences appear in four layers:

  1. The driver contract. Query results, inserted IDs, error codes, and numeric values can have different JavaScript shapes.
  2. The SQL dialect. Joined updates, duplicate handling, booleans, casts, and schema-altering statements differ.
  3. The schema. MySQL may accept data that a stricter PostgreSQL column or constraint rejects.
  4. The operational cutover. Rows, foreign keys, sequences, migration metadata, secrets, downtime, and rollback all need a plan.

An ORM reduces how much database-specific code you write. It does not remove the database underneath your application.

The safest starting point is therefore not pnpm remove mysql2. Start with an audit.

Step 1: Audit the application before changing it

You need to find every place where the current database leaks into the codebase.

For an AdonisJS application using Lucid, inspect these areas first:

  • config/database.ts and environment validation
  • database driver dependencies and lockfiles
  • raw queries and raw WHERE clauses
  • schema migrations, including old migrations
  • code that reads inserted IDs or affected-row counts
  • database error-code checks
  • enum, boolean, JSON, timestamp, and numeric columns
  • test database setup
  • deployment and worker configuration

The first Mezie Labs audit found several real blockers:

  • raw-query consumers expected MySQL's result shape
  • a bulk update used MySQL's UPDATE ... LEFT JOIN syntax
  • a deduplication migration used MySQL's joined DELETE
  • inserts assumed MySQL returned the new numeric ID automatically
  • certificate issuance used INSERT IGNORE, UUID(), and MySQL duplicate-entry codes
  • a charset migration contained CONVERT TO CHARACTER SET
  • legacy lesson durations were strings even though the application treated them as numbers
  • course status values in MySQL did not match the current application constraint

That list became the migration plan. Without it, the first PostgreSQL test run would have become the audit, only noisier.

Step 2: Switch the connection and driver

Once the audit is complete, change the application to use PostgreSQL.

Replace the MySQL driver:

pnpm remove mysql2
pnpm add pg

Then update the Lucid connection:

config/database.ts
import env from "#start/env";
import { defineConfig } from "@adonisjs/lucid";

const dbConfig = defineConfig({
  connection: "postgres",
  connections: {
    postgres: {
      client: "pg",
      connection: {
        host: env.get("DB_HOST"),
        port: env.get("DB_PORT"),
        user: env.get("DB_USER"),
        password: env.get("DB_PASSWORD"),
        database: env.get("DB_DATABASE"),
      },
      migrations: {
        naturalSort: true,
        paths: ["database/migrations"],
        disableRollbacksInProduction: true,
      },
    },
  },
});

export default dbConfig;

Lucid uses Knex underneath, and PostgreSQL connections use the pg driver. The Lucid installation guide covers the generated database configuration, while the Knex installation guide lists the driver used for each database.

The same environment variable names can stay in place if the rest of your deployment already uses them:

DB_HOST=127.0.0.1
DB_PORT=5432
DB_USER=postgres
DB_PASSWORD=postgres
DB_DATABASE=mezielabs

Keep the local PostgreSQL service separate from a deployment-oriented Compose file if those environments serve different purposes. A small dev-only database setup is easier to start, destroy, and recreate without changing production infrastructure.

At this point, the application points at PostgreSQL. It is not migrated yet.

Step 3: Port the driver-level assumptions

Raw SQL returns driver-specific objects. Code that reaches into those objects must change with the driver.

Read rows from rows

With the MySQL driver, code often unwraps a tuple:

const result = await db.rawQuery(sql, bindings);
const row = result[0][0];

With PostgreSQL, read the rows property:

const result = await db.rawQuery(sql, bindings);
const row = result.rows[0];

The same difference applies to updates. MySQL exposes affectedRows on its result packet. PostgreSQL exposes rowCount:

const result = await db.rawQuery(sql, bindings);
const rowsUpdated = Number(result.rowCount ?? 0);

Do not hide this behind a broad compatibility helper during a one-way migration. Updating each consumer makes the new contract explicit and removes dead MySQL behavior.

Convert aggregate strings deliberately

PostgreSQL returns COUNT(*) as a bigint, which the Node driver commonly represents as a string. Convert it at the boundary:

const totalItems = Number(row.total_items ?? 0);
const completedLessons = Number(row.completed_lessons ?? 0);

This is also why aliases matter. COUNT(*) AS total_items gives application code a stable field name even when the database changes.

The Knex query builder documentation notes that PostgreSQL count values are returned as strings.

Request inserted IDs with RETURNING

This MySQL-oriented insert assumes the returned array contains an ID:

const [productId] = await db.table("products").insert(product);

In PostgreSQL, ask for the column you need:

const [insertedProduct] = await db
  .table("products")
  .insert(product)
  .returning("id");

const productId = Number(insertedProduct.id);

Knex returns an array of objects for PostgreSQL's RETURNING form, so read insertedProduct.id.

Knex documents the cross-database insert behavior and the PostgreSQL RETURNING form in its insert query guide.

Replace database-specific error codes

MySQL reports a duplicate key with ER_DUP_ENTRY or error number 1062. PostgreSQL uses SQLSTATE 23505 for a unique violation.

function isUniqueViolation(error: unknown): boolean {
  const databaseError = error as { code?: string };
  return databaseError?.code === "23505";
}

Search for error codes even when the surrounding query uses Lucid. Retry and idempotency logic often depends on the database error rather than the ORM.

Step 4: Rewrite SQL by behavior, not syntax

Some SQL can be translated word for word. The dangerous queries are the ones whose structure belongs to one dialect.

Use real PostgreSQL booleans

MySQL applications often compare boolean-like columns with 1 and 0:

WHERE lessons.status = 1
  AND watch_histories.is_completed = 1

Use boolean values in PostgreSQL:

WHERE lessons.status = TRUE
  AND watch_histories.is_completed = TRUE

Do the same in writes and conditional expressions. PostgreSQL does not treat integers and booleans as interchangeable values.

Replace joined updates with UPDATE ... FROM

MySQL supports this shape:

UPDATE course_progresses cp
LEFT JOIN (...) agg ON agg.user_id = cp.user_id
SET cp.lessons_completed = COALESCE(agg.items_completed, 0)
WHERE cp.course_id = ?

PostgreSQL uses UPDATE ... FROM. A common pattern is to calculate the rows in common table expressions, then join them into the update:

WITH item_counts AS (
  SELECT
    wh.user_id,
    COUNT(DISTINCT ci.id)::integer AS items_completed
  FROM curriculum_items AS ci
  INNER JOIN sections AS s ON s.id = ci.section_id
  INNER JOIN lessons AS l ON l.id = ci.content_id
  INNER JOIN watch_histories AS wh ON wh.lesson_id = l.id
  WHERE s.course_id = ?
    AND l.status = TRUE
    AND wh.is_completed = TRUE
  GROUP BY wh.user_id
),
progress_rows AS (
  SELECT
    cp.id AS progress_id,
    COALESCE(ic.items_completed, 0) AS items_completed
  FROM course_progresses AS cp
  LEFT JOIN item_counts AS ic ON ic.user_id = cp.user_id
  WHERE cp.course_id = ?
)
UPDATE course_progresses AS cp
SET lessons_completed = progress_rows.items_completed
FROM progress_rows
WHERE cp.id = progress_rows.progress_id

This is not a cosmetic rewrite. Check that users with zero matching rows remain in the update. In this example, the second CTE starts from course_progresses and uses a left join, preserving those users.

Replace joined deletes with DELETE ... USING

A MySQL deduplication query may use DELETE with a join. PostgreSQL expresses the same operation with USING:

DELETE FROM course_progresses AS older
USING course_progresses AS better
WHERE older.user_id = better.user_id
  AND older.course_id = better.course_id
  AND older.id < better.id

Real deduplication rules are usually more involved than “keep the largest ID”. Preserve the original ranking behavior, especially when completion state or timestamps decide which row wins.

Replace INSERT IGNORE deliberately

INSERT IGNORE can hide more than duplicate keys. Do not replace it automatically without deciding which conflict you intend to ignore.

PostgreSQL supports ON CONFLICT, and Knex exposes it through onConflict():

await db
  .table("course_certificates")
  .insert(certificate)
  .onConflict(["user_id", "course_id"])
  .ignore();

For Mezie Labs certificate issuance, application-level creation was the clearer option. It kept existing retry behavior, generated IDs with Node's randomUUID(), and handled SQLSTATE 23505 explicitly.

The important decision is not which form looks shortest. It is whether the new code preserves concurrency and idempotency.

Step 5: Make the schema tell the truth

A database migration is a chance for old schema compromises to become visible.

MySQL had a lessons.duration column stored as text. The application summed it as a number. PostgreSQL rejected that mismatch during an aggregate query.

The fix was to convert the column, not cast every future aggregate:

ALTER TABLE lessons
  ALTER COLUMN duration TYPE integer
  USING CASE
    WHEN duration IS NULL OR btrim(duration::text) = '' THEN NULL
    WHEN btrim(duration::text) ~ '^[0-9]+$'
      THEN btrim(duration::text)::integer
    ELSE 0
  END;

The USING expression defines how existing rows become the new type. PostgreSQL documents this form under changing a column's data type.

The fallback to 0 was an application decision. Your data may deserve a different policy:

  • reject the migration when malformed values exist
  • write invalid values to an audit table
  • convert known formats such as "12 minutes"
  • set invalid values to NULL

Do not choose blindly. Run a discovery query first:

SELECT id, duration
FROM lessons
WHERE duration IS NOT NULL
  AND btrim(duration::text) <> ''
  AND btrim(duration::text) !~ '^[0-9]+$';

Constraints need the same treatment. The legacy database contained course statuses such as completed and in development, while the current application expected published and draft.

Normalize the data before restoring the constraint:

UPDATE courses
SET status = CASE
  WHEN status = 'completed' THEN 'published'
  WHEN status = 'in development' THEN 'draft'
  WHEN status IN ('draft', 'coming soon', 'archived', 'published', 'early access')
    THEN status
  ELSE 'coming soon'
END;

Then add the PostgreSQL check constraint:

ALTER TABLE courses
  ADD CONSTRAINT courses_status_check
  CHECK (status IN ('draft', 'coming soon', 'archived', 'published', 'early access'));

This order matters. Adding the constraint before normalizing the rows makes the migration fail halfway through its intent.

What about old MySQL-only migrations?

Mezie Labs had a migration that ran this statement:

ALTER TABLE lessons
CONVERT TO CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

That migration solved a real MySQL problem. It has no PostgreSQL equivalent because PostgreSQL uses a database-level encoding, and the target database was created with UTF-8.

On PostgreSQL, the migration skips the charset statement. This lets a new PostgreSQL database replay the full migration chain without attempting MySQL syntax.

Whether you should edit old migrations depends on your deployment model. For a one-way cutover where every PostgreSQL environment starts empty and replays the application migrations, porting historical migrations can be reasonable. If deployed PostgreSQL environments have already applied them, never rewrite their history casually. Add corrective migrations instead.

The rule is simple: decide which system owns the target schema, then keep its migration history internally consistent.

Step 6: Build the PostgreSQL schema before loading data

You have two broad ways to create the target:

  1. let a migration tool infer both schema and data from MySQL
  2. let the application create its current schema, then import only the data

Mezie Labs used the second approach.

node ace migration:run

This makes the AdonisJS migrations the source of truth. The PostgreSQL database starts with the exact constraints, indexes, and column types expected by the current code.

The data loader then has one job: move and transform rows.

This approach also avoids copying stale MySQL migration records into PostgreSQL. Exclude framework metadata tables such as adonis_schema and adonis_schema_versions from the import. PostgreSQL should keep the migration records created when its own schema was built.

Step 7: Rehearse the data migration with pgloader

pgloader can read a MySQL database and load PostgreSQL directly. Its MySQL migration tutorial covers the standard schema-and-data flow.

For an application-owned target schema, use a data-only load. A simplified template looks like this:

LOAD DATABASE
    FROM {{MYSQL_URL}}
    INTO {{POSTGRES_URL}}

WITH data only, no truncate, disable triggers, reset sequences

SET PostgreSQL PARAMETERS
    search_path to 'public'

EXCLUDING TABLE NAMES MATCHING 'adonis_schema', 'adonis_schema_versions'

ALTER SCHEMA '{{MYSQL_DATABASE}}' RENAME TO 'public'
;

The braces mark template values. MYSQL_DATABASE is the source database name, while the two URL values contain the complete connection strings. Render the file at runtime from secret environment variables. Do not commit production connection strings to the repository or leave them in a generated deployment file.

Here’s what each option does:

  • data only preserves the schema created by the application.
  • no truncate refuses to clear unexpected target data. Recreate the disposable target before each rehearsal instead.
  • disable triggers helps with foreign-key ordering, but disabling internal constraint triggers requires PostgreSQL superuser privileges.
  • reset sequences updates generated-ID sequences after explicit IDs are copied.

The ALTER SCHEMA clause tells pgloader to map the MySQL database to PostgreSQL's public schema. Without that mapping, pgloader may look for target tables under a schema named after the MySQL database.

disable triggers comes with another cost. PostgreSQL does not recheck imported rows when pgloader enables the triggers again. The database can therefore contain orphaned foreign keys while reporting no load error.

If the target role cannot disable constraint triggers, load tables in dependency order or drop and recreate the foreign keys around the copy. When you recreate them, add them as NOT VALID, then run VALIDATE CONSTRAINT after the load. If you keep disable triggers, run explicit orphan checks for every foreign key before cutover.

Treat this loader as a one-off cutover tool. Remove the temporary service or job after migration. A forgotten migration service should not remain connected to your production databases.

Put transformations around the load

Real databases rarely have identical historical and current schemas. pgloader supports BEFORE LOAD DO and AFTER LOAD DO blocks for controlled adjustments.

The Mezie Labs migration used them to:

  • temporarily remove a course status constraint
  • relax legacy nullable timestamps before import
  • preserve an old lesson transcript column in a separate table
  • map legacy course status values
  • restore current NOT NULL and check constraints

Keep these transformations specific and reviewable. If the logic becomes large, move it into versioned SQL scripts with their own verification queries.

Verify timestamp semantics

pgloader maps MySQL DATETIME and TIMESTAMP columns to PostgreSQL timestamptz by default. That mapping can shift historical values when the source and target timezone assumptions differ.

MySQL does not apply session timezone conversion to DATETIME values. PostgreSQL interprets a timestamp without an offset using the session TimeZone when it stores the value as timestamptz.

Set the migration sessions to the timezone your source data represents, often UTC. Then compare a few known timestamps in both databases. Include old records, recent records, and values near a daylight-saving boundary if your application serves a region that uses one.

Treat wall-clock values separately from real instants. An appointment recorded as "09

local time" may belong in timestamp without time zone, while a created_at value normally represents an instant.

The pgloader casting rules and PostgreSQL date and time documentation explain the default conversion behavior.

Rehearse with a disposable copy

Never make production the first full run.

Use this rehearsal sequence:

  1. restore a recent MySQL backup into an isolated source database
  2. create an empty PostgreSQL database
  3. run the application migrations against PostgreSQL
  4. load the MySQL data with pgloader
  5. run transformation, foreign-key, timestamp, and sequence checks
  6. compare row counts and selected business records
  7. run the full application test suite against the migrated database
  8. record the duration and every manual step

If pgloader runs in Docker while both databases run on your macOS host, use host.docker.internal instead of 127.0.0.1 in the connection URLs. Inside the container, 127.0.0.1 points back to the container itself.

Repeat the rehearsal until every check passes, the duration fits the maintenance window, and the runbook contains no improvised steps.

Step 8: Verify sequences after copying IDs

When a loader inserts explicit values into an id column, PostgreSQL's sequence may not move with the data. The next application insert can then reuse an existing ID.

PostgreSQL provides setval for changing a sequence's current value. The sequence documentation explains how its third argument controls the next value returned by nextval.

For one table, the reset looks like this:

SELECT setval(
  pg_get_serial_sequence('products', 'id'),
  COALESCE((SELECT MAX(id) FROM products), 1),
  EXISTS (SELECT 1 FROM products)
);

The boolean argument handles empty tables correctly:

  • true means the next call advances beyond the stored value
  • false means the next call returns the stored value itself

pgloader can reset sequences, but verify them anyway. A migration is complete when the next normal insert works, not when the copy command exits with code zero.

Step 9: Test behavior, not database trivia

Start with focused tests around the code you changed:

node ace test --files="tests/unit/course_progress_service.spec.ts"
node ace test --files="tests/functional/certificate_service.spec.ts"
node ace test --files="tests/functional/commerce_fulfillment.spec.ts"

Then run the complete suites:

node ace test unit
node ace test functional
./node_modules/.bin/tsc --noEmit

The final Mezie Labs validation passed 81 unit tests and 47 functional tests, followed by a clean TypeScript check.

Those numbers are useful, but coverage matters more. The migration smoke tests included:

  • login and session persistence
  • course access and lesson completion
  • progress recomputation
  • checkout fulfillment
  • certificate issuance
  • admin curriculum reorder flows
  • worker startup and queued jobs

Also assert the application's real HTTP behavior. A migration is not a reason to change a redirect into a 403, or to rewrite auth expectations around what a test author assumes should happen. Preserve the contract users already experience.

Remember test database setup

Do not assume the test runner creates the PostgreSQL schema. In this application, tests/bootstrap.ts did not run migrations automatically.

Prepare the test database explicitly:

env NODE_ENV=test node ace migration:run
node ace test unit
node ace test functional

A green suite against an old MySQL test database proves nothing about the PostgreSQL migration.

Step 10: Plan a controlled production cutover

A single-write application cannot safely accept writes to MySQL while you copy an earlier snapshot into PostgreSQL. The simplest consistent cutover uses a short write freeze.

The production sequence was:

  1. Put the application into maintenance mode. Pause workers, schedulers, queue consumers, webhook handlers, and administrative jobs so every writer is stopped.
  2. Take a final MySQL backup.
  3. Run the rehearsed pgloader import into an empty, migrated PostgreSQL database.
  4. Apply the known data transformations.
  5. Validate foreign keys, timestamps, constraints, and sequences.
  6. Compare row counts and business-critical records.
  7. Update the application's database environment variables.
  8. Deploy the PostgreSQL code and run node ace migration:run --force while production traffic and workers remain paused.
  9. Run read-only checks and controlled smoke tests with a dedicated test account. Keep external side effects and production queues paused.
  10. Make the go-or-no-go decision. Roll back now if any required check fails.
  11. Start workers, schedulers, and queue consumers against PostgreSQL, then reopen application writes.
  12. Watch the first real writes and queued jobs before ending the maintenance window.

Your smoke tests should follow business flows, not only database health:

  • Can a user sign in?
  • Can an enrolled user open a course?
  • Can a learner complete a lesson?
  • Does progress update correctly?
  • Can a checkout grant access exactly once?
  • Can an eligible learner receive a certificate?
  • Can an administrator change curriculum order?
  • Are worker processes configured to use the new database when released?

Keep the final MySQL backup untouched through an agreed rollback window.

Step 10 is the rollback boundary. Before that decision, only controlled test writes should reach PostgreSQL. Once production traffic or workers resume, PostgreSQL is the system of record.

Define rollback before the freeze

Rollback is easy only while PostgreSQL has not accepted unique production writes.

Before releasing production traffic or workers, rollback means:

  1. keep every writer paused
  2. point the application back at MySQL
  3. deploy the last MySQL-compatible release
  4. restart the web and worker processes

After PostgreSQL accepts writes, rollback needs a reverse data plan. That is a different project.

If validation fails at the decision point, roll back. If validation passes, release the paused writers and stop treating the old MySQL server as an active peer.

Common migration mistakes

Changing the driver before auditing raw SQL

The application starts, then fails only when a less common workflow reaches a MySQL-specific query.

Better approach: create a database-dependency inventory first. Give every raw query and migration a disposition.

Letting the loader invent the production schema

The imported schema reflects historical MySQL types instead of the current application contract.

Better approach: build the target from application migrations, then load data into it.

Copying migration metadata from MySQL

PostgreSQL thinks migrations ran even though the target schema was created by a different path.

Better approach: exclude framework migration tables from the data copy.

Casting around a bad column forever

Every aggregate gains another CAST, while the schema continues to claim a number is text.

Better approach: clean existing values once and change the column type.

Trusting row counts alone

Every table has the expected number of rows, but status values, sequences, or nullable fields are wrong.

Better approach: combine counts with constraint checks, sample comparisons, aggregate comparisons, and real application writes.

Lessons from the migration

The hardest part of moving from MySQL to PostgreSQL was not installing pg. It was finding all the places where years of application behavior had quietly learned MySQL's rules.

The successful approach was deliberately plain:

  • audit before editing
  • make the schema describe the data the application actually uses
  • port raw SQL by behavior
  • let application migrations own the target schema
  • rehearse the data load until it becomes routine
  • verify foreign keys, timestamps, sequences, constraints, and business flows
  • freeze every writer for one controlled cutover

If you treat the migration as a driver change, production will finish the audit for you. Treat it as an application, schema, data, and operations change, and you can discover the surprises while rollback still means deleting a disposable database.