bug(postgres): JSON/JSONB data insertion fails with "invalid input syntax for type json" causing foreign key constraint failures

[samueltuoyo15]

During a PostgreSQL migration, tables containing json or jsonb columns fail during data row insertion with the error invalid input syntax for type json.

Because data insertion fails on core tables (such as users, projects, contractors), those tables remain empty or unindexed, which causes downstream Foreign Key creation to fail with there is no unique constraint matching given keys for referenced table.

Screenshots

Steps to Reproduce

1. Prepare a source PostgreSQL database containing tables with json or jsonb column types and populated data.
2. Launch DB Mover and select PostgreSQL migration mode.
3. Enter the source PostgreSQL connection URI and a target PostgreSQL connection URI.
4. Click Start Migration.
5. Observe the migration terminal logs when it reaches tables with JSON columns.

Log Snippet

Processing table: contractors
Created table structure: contractors
Error processing table contractors: invalid input syntax for type json

...

Processing table: users
Created table structure: users
Error processing table users: invalid input syntax for type json

Error adding foreign key activities_actor_id_users_id_fk on activities: there is no unique constraint matching given keys for referenced table "users"

Expected Behavior

DB Mover should successfully copy rows containing json / jsonb columns into the target PostgreSQL database without syntax errors, allowing table structures, data, and Foreign Keys to migrate completely.

Actual Behavior

Data insertion fails for any table containing json / jsonb columns, throwing invalid input syntax for type json and triggering subsequent foreign key failures across the schema.

Root Cause Analysis

In server/src/databases/postgres/migration.ts:

1. When reading data from the source PostgreSQL database, node-postgres (pg) automatically parses json and jsonb columns into JavaScript objects (e.g. { key: "value" }).
2. When inserting rows into the target database using parameterized queries (INSERT INTO ... VALUES ($1, $2, ...)), raw JS objects are passed directly into parameter values.
3. Without stringifying objects (JSON.stringify()) or type casting placeholders (e.g. $1::json), PostgreSQL receives raw/unformatted object strings like "[object Object]", causing PostgreSQL to throw invalid input syntax for type json.

Suggested Fix

In server/src/databases/postgres/migration.ts, inspect column types during data row mapping and apply JSON.stringify(val) for objects/arrays targeting json or jsonb columns (or explicitly cast parameters to $1::json / $1::jsonb).

[JC-Coder]

Thanks for sharing , will see to this.