EKOSdocs
Docs / Integrations Build

Databases

SQL files in eight dialects, live Oracle, PostgreSQL and ClickHouse catalogs, dbt and legacy ETL.

EKOS reads a database two ways: from the SQL files in your repositories, and from a live catalog it connects to read-only. Both end up as the same Table, View, Procedure and Trigger objects.

From files

Source What EKOS reads
PostgreSQL DDL; COMMENT ON as descriptions; preprocessing for hand-written schemas; PL/pgSQL functions, procedures and triggers statement by statement; views
Oracle DDL (RFC 0210: SQL*Plus commands and PL/SQL units handled line-preserving); PL/SQL routines, triggers and packages into the same procedural IR as PL/pgSQL
Migration scripts <timestamp>_<description>.sql files become dated SchemaChangeSets with one classified SchemaChange per DDL operation, linked to their tables (RFC 0210)
MySQL DDL
SQL Server (T-SQL) DDL, procedures, views
Snowflake, Databricks, Trino DDL and transformation SQL
ClickHouse DDL
dbt models and sources from the project's own .sql and YAML files, never manifest.json
Pentaho .ktr / .kjb into the Transformation IR, exportable to dbt

When a whole file fails to parse, each statement is tried alone and the failing ones are reported as SQL005; a file that states tables and yields none is SQL001, which names the dialect. Choose the dialect per path with [[recover.sql.dialect-rules]] (ekos init --detect writes one per engine-named directory).

Live databases

Declare a database in the project's ekos.toml, or with ekos database add (the console's Sources tab does the same):

[[databases]]
name = "erp"
kind = "oracle"                      # oracle | postgres | clickhouse
connect = "db.example.com:1521/ORCLPDB1"
user = "ekos_ro"
password-env = "EKOS_ERP_PASSWORD"   # the variable's NAME, never the password
owners = ["ADEMPIERE"]               # schemas to read (databases, for ClickHouse)
profile = "p0"                       # Oracle only: p0 | p1 | p2
Kind connect Notes
oracle (RFC 0211) host:port/service Reads the DBA_* dictionary views (grant SELECT_CATALOG_ROLE) inside SET TRANSACTION READ ONLY, reconciled against the database's own object counts. Profiles: p0 optimizer statistics, p1 row counts, p2 one scan per table for null counts, number and date bounds and text lengths — no tier stores a text value. Optional dictionary = "idempiere" reads the application dictionary's table flags. Needs a build with the oracle feature (not in the release binaries or the image) and Oracle Instant Client on LD_LIBRARY_PATH
postgres (RFC 0235) host[:port]/database Tables, constraints, views, routines and triggers; nothing to install
clickhouse (RFC 0235) http[s]://host[:port] system.tables / system.columns with readonly=1; nothing to install

For every kind, ekos build reads the catalog once per build in a read-only session and checks that a write is refused; the objects carry origin: live; a dropped table leaves the model at the next run. Live objects are never merged with objects from files, and name lookups use them only for names no file defines. For Oracle, ekos commit then compares what the code claims (Java application-model tables and columns, routine files) with what the database holds, as reviewable conflicts — a missing table or column, a mandatory column the database allows to be null, a routine whose text differs — each explained, where the source tree accounts for it, as a pending migration. ekos database test <name> connects, checks writes are refused, and puts the database's census next to what was compiled.

Measured: on the iDempiere Oracle testbed, the catalog read reconciled 10,613 objects with 0 mismatches (devlog_292); PostgreSQL 16 and ClickHouse 24.8 test databases gave a census equal to the compiled model for tables, views, routines and triggers (devlog_317).

The password is read from the named environment variable at run time. In the console it is kept in the server's keychain under a variable the console names, and only a signed-in person can enter one.

Not yet

A live SQL Server connector is planned. SAP, Salesforce, Fabric and Snowflake API connectors exist as mock-tested scaffolds, not exercised against live accounts. See Indexing a Database.

Checking references

ekos check refs (RFC 0233) reports every reference from code, ETL, dbt, routines and views that resolves to no table or routine, or to several, with the file and line, the closest names and a confidence. Formats: table, JSON, SARIF; --fail-on error for CI. Agents get the same through ekos_xref_check.

To move data rather than index it: a live PostgreSQL source goes to ClickHouse with EKOS Migrate, or to Apache Iceberg with EKOS Lakehouse. Lakehouse also has a deeper Pentaho translator that turns whole packages into silver and gold dbt models, with fidelity labels.