Indexing a Database
Recover tables, keys, views, routines and history from SQL files and dbt projects, and read live Oracle, PostgreSQL and ClickHouse catalogs.
EKOS reads a database two ways, and a project can use both:
| Route | Reads | Configured by |
|---|---|---|
| Files | checked-in DDL, views, routines, migration scripts, dbt projects | [observe] paths + [recover.sql] dialect rules |
| Live catalog | the catalog of a running Oracle, PostgreSQL or ClickHouse database, read-only | [[databases]] / ekos database add |
Neither route reads a row value into the ledger.
SQL files
- Put the SQL where
[observe] pathscovers it. - Set the dialect.
ekos init --detectwrites one rule per engine-named directory (oracle/,postgresql/, …); otherwise write them yourself:
[recover.sql]
default-dialect = "generic"
[[recover.sql.dialect-rules]] # first path-glob match wins
path-glob = "db/legacy/**/*.sql"
dialect = "mysql"
[[recover.sql.dialect-rules]]
path-glob = "db/warehouse/**/*.sql"
dialect = "snowflake"
Dialects: generic, postgres, mysql, mssql (also tsql, synapse), snowflake, databricks, clickhouse, trino, oracle.
The dialect still matters. When a whole file fails to parse, each statement is tried alone: a failing one is skipped and reported as SQL005, and SQL001 names the dialect when a file states tables and yields none. A wrong dialect can therefore cost you most of a schema with only warnings to show for it.
- Compile as usual and check that tables exist:
ekos ekl "FIND Object WHERE kind = 'Table' LIMIT 20"
ekos coverage
Zero tables for a repo that has a schema means: check the dialect, then read the recover diagnostics in .ekos/diagnostics/ for SQL001 and SQL005.
Each Table records its dialect, and definitions are compared only within one dialect, so a project that ships every script for Oracle and for PostgreSQL does not see the two copies as conflicting.
What the files give you
| Source | Recovered |
|---|---|
| DDL | tables, columns, keys, CHECK constraints; ALTER TABLE … ADD … FOREIGN KEY as an edge |
COMMENT ON … |
author-written descriptions of tables, columns, views and routines, ranked above LLM text |
CREATE [MATERIALIZED] VIEW |
View objects with the tables and routines their query reads |
| PL/pgSQL and PL/SQL routines, triggers, Oracle packages | Procedure objects with one ProcedureStatement per statement and what each reads, writes and calls; triggers classified as validation, derived column, audit, cascade, mixed or unknown, with reasons |
migration scripts named <yyyyMMddHHmm>_<description>.sql |
dated SchemaChangeSets with one classified SchemaChange per operation, linked to their table |
SELECT, VIEW, stored procedures |
Transformation IR (source → filter → join → aggregate → sink) |
| dbt projects | one Table per models/**/*.sql and per declared source — from the project's own files, never manifest.json |
Pentaho .ktr/.kjb |
Transformation IR, exportable to dbt with ekos dbt |
Oracle scripts are read with SQL*Plus lines (SET, PROMPT, @file) set aside and Oracle-only clauses rewritten, keeping the file's line numbers. On iDempiere release-13, 9 of 62,251 Oracle migration statements were rejected, 60 of 61 routines were recovered completely, and 2,154 change sets yielded 26,060 classified changes, with no model calls.
Live databases
A project can name its databases next to its sources. ekos build reads each catalog once per build, through a read-only session, and checks that a write is refused. The password is never in ekos.toml, only the name of the variable that holds it:
ekos database add erp --connect localhost:51521/XEPDB1 --user ekos_ro \
--password-env EKOS_ORACLE_PASSWORD --owner ADEMPIERE --profile p2 # --kind oracle is the default
ekos database add shop --kind postgres --connect db.local:5432/shop --user shop_ro \
--password-env SHOP_PW --owner public
ekos database add wh --kind clickhouse --connect https://ch.local:8443 --user ro \
--password-env WH_PW --owner analytics
ekos database test shop # read-only connection, write refused, census next to what was compiled
ekos database list
Each add writes a [[databases]] entry:
[[databases]]
name = "erp"
kind = "oracle" # oracle | postgres | clickhouse
connect = "localhost:51521/XEPDB1" # postgres: host[:port]/database · clickhouse: http[s]://host[:port]
user = "ekos_ro"
password-env = "EKOS_ORACLE_PASSWORD" # the variable's NAME, never the password
owners = ["ADEMPIERE"] # schemas to read (ClickHouse: databases)
profile = "p2" # Oracle only
dictionary = "idempiere" # Oracle only: also read AD_Table's flags
| Kind | How it is read | Read-only user |
|---|---|---|
oracle |
DBA_* views inside SET TRANSACTION READ ONLY, reconciled against *_OBJECTS so a user who cannot see some objects is an error, not a smaller answer |
CREATE SESSION, SELECT on the schema, SELECT_CATALOG_ROLE |
postgres |
a default_transaction_read_only session with timeouts, reconciled against the server's own counts |
GRANT USAGE + GRANT SELECT |
clickhouse |
system.tables / system.columns, every query with readonly=1 |
SETTINGS readonly = 1, GRANT SELECT ON db.*, system.* |
ekos recover turns the catalog into ordinary Table, View, Procedure and trigger objects marked origin: live; routines go through the same readers as files. Live objects never merge with tables from scripts, and a dropped object leaves the model on the next build. In ClickHouse, sorting and primary keys are table properties, not unique keys.
Oracle needs a build with the oracle feature (cargo build --release -p ekos --features oracle) and Oracle Instant Client on LD_LIBRARY_PATH at run time. The prebuilt binaries are built without it, and a kind = "oracle" entry then fails ekos build with a message saying so. PostgreSQL and ClickHouse need nothing installed.
Profiles (Oracle only). profile decides how much build measures: p0 optimizer statistics (the default), p1 exact row counts, p2 one aggregate scan per table for null counts, number and date bounds and text lengths. No tier stores a text value, and bounds are withheld for columns named like personal data. On iDempiere, 903 tables were profiled at p2 in 6 s.
In the web console, a project's Sources tab does the same: add a database (the password goes to the server's keychain), test it, see the census next to the compiled counts, and Init & compile.
Code against database
When a project has both the code and the live database, ekos commit compares them and records each disagreement as a conflict that cites both sides: a model table or column the database lacks, a table no model describes, a mandatory column that is nullable, a routine file whose text differs from the installed one. When the tree explains a difference (a migration newer than the database, a translation table), the conflict says so.
ekos conflicts list --type model_column_missing
ekos conflicts show <name>
ekos conflicts resolve <name> --pick 1 --note "the migration is pending" # human only
On iDempiere: 444 conflicts, 239 explained by the tree itself.
ekos commit also writes one WriteBehaviour per table: whether every update moves the timestamp column, and every way rows disappear (code, users, cascades, purges), each cited. On iDempiere, 144 of 903 tables have a raw UPDATE that skips Updated.
ekos ekl "FIND Object WHERE kind = 'WriteBehaviour' AND update_class = 'raw_sql_skips_updated'"
Explain a legacy pipeline
Through MCP, ekos_transformation_explain walks a pipeline step by step with evidence per step; ekos_transformation_diff compares an old pipeline with a new one to check a migration preserved the logic. Unmapped steps are reported, never guessed.
To find the names that point at nothing — a routine writing a table no script creates, Java SQL against a dropped table — see Checking Reference Integrity.
The older ClickHouse observer configured by
EKOS_CLICKHOUSE_*environment variables (RFC 0056) still works, and its gated live NL-to-SQL tool is off by default. For a project database, prefer[[databases]].