Case study

Database Integration

Job: Vivavis, Full-Stack Engineering Intern
Year: 2023
Stack: Node.js, React, Sequelize, node-cron, Yup, Oracle, PostgreSQL, MySQL
Team: Solo, with a project manager and an internship supervisor
Status: Beta, handed off when the internship ended

Overview

Some of Vivavis's applications ran on Oracle. Others needed that data but ran on PostgreSQL, MySQL, or another database, and data types don't match across them. Before the app, each project that needed Oracle data had its own script to copy it over, and each script handled types its own way. During my internship I built an internal app that lets Vivavis engineers move data out of Oracle into those databases and convert it on the way.

How a sync works

An engineer adds a source database with its connection details and writes an SQL query that selects the table and columns to pull. They add a target database, map each source field to a target field, and transform values where needed, from type conversion to math on a column. Then they pick a schedule: a fixed interval or a fixed time. The app saves the whole configuration as JSON and validates it with Yup before it runs.

The first run copies everything the query returns. Every run after that only picks up rows added since the last sync, so existing data in the target isn't overwritten. Schedules run on node-cron, and an engineer can set up several syncs and pause or resume any of them.

Before saving, engineers can test each connection and preview the raw result of their SQL query. When a sync fails, the error goes to a log and the sync can be retried.

An Oracle source form with a passing connection test, next to an SQL query and the first five rows it returns — Adding a source: connection details, a connection test, and the raw result of the query.fig. 02

Five Oracle fields mapped to PostgreSQL fields. AMOUNT_CENTS is divided by 100 into a numeric amount, IS_PAID becomes a boolean, and the rest keep their default types — Each source field maps to a target field, with a transform where the default type is wrong.fig. 03

A schedule set to run every 6 hours, with a daily fixed time as the other option and a timeline marking the next run at 12:00 — A sync runs on a fixed interval or at a fixed time.fig. 04

Four syncs from Oracle to PostgreSQL and MySQL, each with its schedule, last run, and status: running, scheduled, failed with a retry button, and paused — Several syncs run side by side. An engineer can pause, resume, or retry each one.fig. 05

Getting the types right

Oracle and the target databases disagree on basic types. An Oracle DATE carries a time, while a DATE column in PostgreSQL or MySQL drops it. An Oracle NUMBER can hold an integer or a decimal depending on how the column was declared. My early test syncs cut times off timestamps and rounded decimals into integers. I added a default type map for each target database, and an engineer can override any field with a transform when the default is wrong.

Default type map, per target

Oracle	PostgreSQL	MySQL
DATE	timestamp	datetime
NUMBER(p)	bigint	bigint
NUMBER(p, s)	numeric(p, s)	decimal(p, s)
VARCHAR2(n)	varchar(n)	varchar(n)
CLOB	text	longtext

Where it stopped

My internship ended with the app in beta. It synced new rows only. Updates to existing rows and deletions weren't built yet. I built it solo, working with a project manager and an internship supervisor, and handed it to my project manager when I left.

Upserts were next on my list: match rows on the primary key and update them instead of skipping them. Deletions would have come after, from a soft-delete column in the source or a periodic comparison of keys.

Back to Work Email me about it

Job · 2023