SQL Migration

Migrate data between MongoDB and relational databases with intelligent type mapping, schema generation, and full CDC-backed replication. VisuaLeaf supports one-shot snapshots, ongoing streaming replication, and bidirectional-optional sync between MongoDB and PostgreSQL, MySQL, MariaDB, SQL Server, and Oracle. Under the hood it embeds Debezium 2.5.4 to read the source database's binlog or logical-replication stream — the same battle-tested engine used by Confluent, Airbyte, and Materialize.

Change Data Capture (Debezium 2.5.4)

Rather than polling the source database with repeated SELECT *, VisuaLeaf captures changes directly from the transaction log. This means:

• Zero-impact reads — no table locks, no long-running scans, no query load on the OLTP database
• Initial snapshot + streaming — Debezium takes a consistent snapshot of existing rows, then switches to log tailing for new changes
• Exactly-once semantics — offsets are persisted after each successful write to Mongo, so restart resumes exactly where it left off
• Schema evolution — column adds / renames on the source flow through automatically
• Row-level deletes captured — Debezium emits tombstone events so deleted rows are removed from the Mongo target

Log Sources by Database

Database	Log Source	Required Setup
PostgreSQL 10+	Logical replication (pgoutput)	wal_level = logical, replication role, publication
MySQL 5.7+ / 8.0+	Row-based binlog	binlog_format = ROW, binlog_row_image = FULL
MariaDB 10.4+	Row-based binlog (MySQL-compatible)	Same as MySQL
SQL Server 2016+	CDC (Change Data Capture)	sys.sp_cdc_enable_db and sys.sp_cdc_enable_table
Oracle 12c+	LogMiner or XStream	Supplemental logging on tracked tables

Example Connection Strings

Add these in Connection Manager. VisuaLeaf validates the CDC prerequisites and warns you if configuration is missing.

# PostgreSQL
jdbc:postgresql://db.internal:5432/appdb?user=debezium&password=***&sslmode=require

# MySQL
jdbc:mysql://db.internal:3306/appdb?user=debezium&password=***&useSSL=true

# SQL Server
jdbc:sqlserver://sqlserver.internal:1433;databaseName=appdb;user=debezium;password=***;encrypt=true

# Oracle
jdbc:oracle:thin:debezium/***@//ora.internal:1521/ORCLPDB1

SQL Source Types

Import data from SQL databases into MongoDB using these source options.

Source Type	Description	Use Case
SQL Table	Import all rows from a SQL table	Full table migration to MongoDB collection
SQL View	Import from a SQL view (pre-joined/filtered data)	Import denormalized data from multiple tables
SQL Query	Import using custom SELECT statement	Complex joins, filters, or transformations at source

SQL Target Types

Export MongoDB data to SQL databases using these target options.

Target Type	Description	Use Case
SQL Table	Export directly to a SQL database table	Live migration with Insert/Upsert/DropInsert modes
SQL Script	Generate SQL INSERT/UPDATE statements as a file	Review before execution, version control, manual apply

Supported SQL Databases

VisuaLeaf supports migration to and from major relational databases.

Database	Versions	Features
MySQL	5.7+, 8.0+	JSON columns, SSL/TLS, connection pooling
PostgreSQL	10+, 12+, 14+, 16+	JSONB columns, array types, SSL/TLS
SQL Server	2016+, 2019+, 2022+	Windows/Linux, Azure SQL, SSL/TLS
Oracle	12c+, 19c+, 21c+	JSON columns, connection pooling

Type Mapping

VisuaLeaf automatically maps between SQL and MongoDB types, with per-field manual override in the mapping UI. Type coercion is deterministic and driven by the source column metadata Debezium extracts from the transaction log.

SQL to MongoDB Type Mapping

SQL Type	MongoDB / BSON Type	Notes
SMALLINT, INT, INTEGER	Int32	32-bit signed integer
BIGINT	Int64 (Long)	64-bit signed integer — preserved as NumberLong
DECIMAL, NUMERIC	Decimal128	Lossless decimal preservation, ideal for financial data
REAL, FLOAT, DOUBLE	Double	IEEE 754 double precision
BOOLEAN, BIT(1), TINYINT(1)	Boolean	MySQL TINYINT(1) auto-detected as boolean
CHAR, VARCHAR, TEXT, NVARCHAR	String (UTF-8)	All character types normalized to UTF-8
DATE	Date (midnight UTC)	Time component zeroed
TIME, TIMETZ	String (ISO 8601 time)	Preserved as-is
TIMESTAMP, TIMESTAMPTZ, DATETIME	Date (BSON UTC datetime)	Millisecond precision; timezone normalized to UTC
JSON, JSONB	BSON Document (nested)	Parsed and embedded as a nested subdocument — not a string
ARRAY[] (PostgreSQL)	Array	Elements typed per column definition
UUID	Binary (subtype 4) or String	Configurable; default is Binary UUID
BLOB, BYTEA, VARBINARY, IMAGE	Binary (subtype 0)	Raw bytes preserved — large blobs can be routed to GridFS
ENUM	String	Enum label; original definition kept in a metadata sidecar collection
NULL	null	Preserved

MongoDB to SQL Type Mapping

MongoDB Type	MySQL	PostgreSQL	SQL Server
String	VARCHAR / TEXT	VARCHAR / TEXT	NVARCHAR / NTEXT
Number (Int)	INT / BIGINT	INTEGER / BIGINT	INT / BIGINT
Number (Double)	DOUBLE	DOUBLE PRECISION	FLOAT
Boolean	TINYINT(1)	BOOLEAN	BIT
Date	DATETIME	TIMESTAMP	DATETIME2
ObjectId	CHAR(24)	CHAR(24)	CHAR(24)
Object / Array	JSON	JSONB	NVARCHAR(MAX)

SQL Field Configuration

Configure SQL column properties for each mapped field.

• Nullable - Allow NULL values in the column
• Primary Key - Set as primary key (typically _id → id)
• Auto Increment - Enable auto-increment for numeric primary keys
• Unique - Add unique constraint to the column
• Index - Create index on the column for faster queries
• Default Value - Set default value for new rows

Write Modes

Control how data is written to the target (whether that's a SQL table or a Mongo collection). Write modes apply symmetrically in both directions.

Mode	Behavior	Use Case
Append (Insert)	Insert new rows only, fail on duplicates	First-time migration, append-only data, audit logs
Upsert	Insert new rows, update existing (matched by primary key / _id)	Incremental sync, idempotent runs, ongoing CDC replication
Replace Collection (Drop & Insert)	Drop & recreate the target table / collection, then insert all rows	Full refresh, dev / staging re-seed, schema resets

Real-Time Replication (Bidirectional-Optional)

Once the initial snapshot completes, Debezium keeps the connection to the source's transaction log open and streams changes as they happen. Each Mongo target receives inserts, updates and deletes with sub-second latency in most deployments.

• Forward mode (SQL → Mongo) is always available — the default direction for CDC replication
• Reverse mode (Mongo → SQL) uses MongoDB change streams (requires a replica set) and mirrors writes into the SQL table via prepared statements
• Bidirectional mode can be enabled per-pipeline with conflict resolution set to last-write-wins or source-priority. Loop detection uses a per-row origin marker to avoid ping-pong writes
• Pause / resume at any time from the Task Manager — Debezium offsets and Mongo resume tokens are checkpointed every 30 seconds
• Backfill + tail — if replication falls behind (e.g. Mongo target was offline for hours), Debezium simply keeps tailing the log; there is no data loss as long as the source WAL / binlog retention window is not exceeded

Example Pipeline Configuration

# Debezium connector properties (managed by VisuaLeaf UI)
name                        = users-pg-to-mongo
connector.class             = io.debezium.connector.postgresql.PostgresConnector
database.hostname           = db.internal
database.port               = 5432
database.user               = debezium
database.dbname             = appdb
schema.include.list         = public
table.include.list          = public.users,public.orders
plugin.name                 = pgoutput
snapshot.mode               = initial
publication.autocreate.mode = filtered

# VisuaLeaf-specific target settings
target.connection           = mongo-prod
target.database             = app
target.write.mode           = upsert
target.match.key            = id -> _id

Data Masking

Protect sensitive data during migration by applying masking transformations. Data masking allows you to export data for development, testing, or analytics while obscuring personally identifiable information (PII) and other sensitive fields.

Masking Types

Mask Type	Description	Example
Redact	Replace with fixed placeholder	john@email.com → [REDACTED]
Partial Mask	Show first/last characters only	john@email.com → j***@***.com
Hash	One-way hash (SHA-256)	john@email.com → a8f5f167...
Randomize	Replace with random value of same type	555-1234 → 382-9471
Fake	Generate realistic fake data	John Smith → Jane Doe
Null	Replace with null/empty	secret123 → null

Applying Masks

Apply masking in the field transformation section of your export job:

1. Open field mapping for your export job
2. Click on a sensitive field (email, phone, SSN, etc.)
3. In the transformation script, use built-in masking functions
4. Preview to verify masking is applied correctly

Masking Functions

// Redact completely
return '[REDACTED]';

// Partial mask email
return value.replace(/(.{1}).*@(.*)\.(.{2,})/, '$1***@***.$3');

// Hash value (consistent across runs)
return crypto.sha256(value);

// Randomize phone number
return '555-' + Math.floor(1000 + Math.random() * 9000);

// Null out field
return null;

Common Masking Patterns

Field Type	Recommended Mask	Result
Email	Partial mask	j***@***.com
Phone	Partial mask (last 4)	***-***-1234
SSN / ID	Hash or redact	***-**-6789 or hash
Credit Card	Partial mask (last 4)	****-****-****-1234
Name	Fake data	Random realistic name
Address	Fake or redact	Random address or [REDACTED]
Password	Null or exclude	Don't export passwords

Pro Tips

1. Use SQL Query source to join multiple tables before importing to MongoDB - this creates properly denormalized documents.
2. For large migrations, use batch size control to avoid memory issues. Start with 1000 rows per batch.
3. Generate a SQL Script first to review the SQL before executing against production databases.
4. When exporting nested MongoDB objects to SQL, they're automatically serialized as JSON columns.
5. Use Upsert mode for incremental syncs - it's idempotent and safe to run multiple times.
6. Apply data masking when exporting to development/test environments to protect PII.
7. Test your SQL connection before saving the job using the "Test Connection" button.

Related Features

• Export/Import Data - Full guide to creating export jobs with field mapping and transformations
• Task Manager - Organize, schedule, and monitor migration jobs
• SQL Query - Browse and query SQL tables directly
• Connection Manager - Manage MongoDB and SQL connections