lumnik. / scenarios All scenarios · Français

One product record joins PostgreSQL and Oracle with nothing migrated

Goal Show a complete product record when the product lives in PostgreSQL and its category in Oracle, without touching either database.

Products live in PostgreSQL, their categories in Oracle. Two jdbc connectors read them without writing anything, and an entity joins them.

$ lm secret set MERGE_DB_POSTGRES_PASSWORD -
Updated secret "MERGE_DB_POSTGRES_PASSWORD"

$ lm secret set MERGE_DB_ORACLE_PASSWORD -
Updated secret "MERGE_DB_ORACLE_PASSWORD"
postgres-m_product.sql.txt
-- PostgreSQL, database erp, as erp_reader
SELECT m_product_id, m_product_category_id, name, isactive FROM m_product
WHERE m_product_id IN (1024944, 1025082, 9999999) ORDER BY m_product_id;

 m_product_id | m_product_category_id |     name     | isactive 
--------------+-----------------------+--------------+----------
      1024944 | 1000021               | 4120.300.042 | N
      1025082 | 1000021               | 160-8487     | Y
      9999999 | 9999999               | NO-CATEGORY  | Y
(3 rows)

SELECT count(*) FROM m_product;
 count 
-------
 40234
(1 row)
oracle-m_product_category.sql.txt
-- Oracle Free 23ai, service FREEPDB1, schema ERP
SELECT M_PRODUCT_CATEGORY_ID, NAME, ISACTIVE FROM M_PRODUCT_CATEGORY
WHERE M_PRODUCT_CATEGORY_ID IN ('1000017', '1000018', '1000021', '9999999') ORDER BY M_PRODUCT_CATEGORY_ID;


M_PRODUCT_CATEGORY_ID  NAME                           ISACTIVE
---------------------- ------------------------------ --------
1000017                Filtration à sable             Y
1000018                Filtration à cartouche         Y
1000021                Pompes                         Y


  COUNT(*)
----------
        63
manifests/30-product-with-category.yaml
kind: Entityname: MergedProductBdescription: Products from PostgreSQL enriched with their Oracle category.match: [product_id]scopes: [catalogue]sources:  - table: t_m_product_b    map:      m_product_id: product_id      m_product_category_id: category_id      name: product_name      description: product_description      isactive: product_activejoins:  - table: t_m_product_category_b    from: category_id    to: m_product_category_id    map:      name: category_name      description: category_description      isactive: category_active
$ lm entity get MergedProductB --filter product_id=1024944 -o json
[
  {
    "product_id": "1024944",
    "category_id": "1000021",
    "product_name": "4120.300.042",
    "product_description": "Moteur de pompe 0,75 cv Mono",
    "product_active": "N",
    "category_name": "Pompes",
    "category_description": "Pompes centrifuges ou auto-amorçantes",
    "category_active": "Y",
    "_conflicts": "{}"
  }
]

$ lm entity get MergedProductB --filter product_id=1025082 -o json
[
  {
    "product_id": "1025082",
    "category_id": "1000021",
    "product_name": "160-8487",
    "product_description": "POMPE FILTRATION PLUS 860 300 W MONOPHASE (DAB)",
    "product_active": "Y",
    "category_name": "Pompes",
    "category_description": "Pompes centrifuges ou auto-amorçantes",
    "category_active": "Y",
    "_conflicts": "{}"
  }
]

$ lm entity get MergedProductB --filter product_id=9999999 -o json
[
  {
    "product_id": "9999999",
    "category_id": "9999999",
    "product_name": "NO-CATEGORY",
    "product_description": "Synthetic product without a category, proving LEFT JOIN preservation",
    "product_active": "Y",
    "category_name": null,
    "category_description": null,
    "category_active": null,
    "_conflicts": "{}"
  }
]

Limits

  • Product 9999999 is a synthetic sentinel added to prove the left join.
  • mode: full does not see a row edited in place: following updates needs a watermark column.
  • Synthetic data.

Catalogue · two databases

The product is in PostgreSQL.

Its category is in Oracle.

Two legacy databases, neither sees the other.

Synthetic catalogue

The problem

A product record needs both.

  • Migrating a database is a project.
  • A home-made ETL is one more script.
  • A failed join makes products disappear.

Nothing is migrated

Both databases stay where they are.

  • Two read-only accounts.
  • The passwords in lm secret.
  • No application to build.

What had to be written

joins:
  - table: t_m_product_category_b

One join across the two databases.

A 68-line scenario, three declaration files.

What the record shows

4120.300.042, category Pompes.

  • The product comes from PostgreSQL.
  • Pompes comes from Oracle.
  • 40,234 products, each once.

Replay of 3 November 2026.

Nothing disappears

A product with no category stays.

  • 9999999 has no category in Oracle.
  • It is on the record, category empty.
  • The join is a left join.

The limit

Full mode does not see a row changed in place.

  • It reads what follows the last key.
  • To follow updates: a watermark column.
  • Oracle is Pro edition only.

Try it

The scenario replays end to end.

PostgreSQL, Oracle, entity: every step is published with its output.

This scenario needs lumnik Pro.

See the editions →