Eve Database/SQL

Goals:

• Read and update tables with records, writing only the fields that changed;
• Stop hard when the code and the database disagree, before a row is written with a wrong mapping;
• Make a job a transaction with commit, rollback and rules for retry;
• Load, transform and transfer data between files and databases (ETL);
• Run the same code on a local SQLite file, a database server or a database behind an Eve server.

The layers

+-------------------------------------------------------+
| Local Eve App                                         |
|  - Memory Tables (Buffered Records)                   |
|  - Ingestion (XML, JSON, CSV, DAT via Unicode Streams)|
+-------------------------------------------------------+
                           |  Eve RPC / Stream Protocol
+-------------------------------------------------------+
| Eve Server                                            |
|  - ACID Session Manager                               |
|  - Local Database Cache                               |
+-------------------------------------------------------+
                           |  Vendor Driver Connector
+-------------------------------------------------------+
| Remote Database (PostgreSQL / MySQL / ...)            |
+-------------------------------------------------------+

A script reaches a database by a connection name, such as "sales". The name is declared in the configuration file of the machine, eve.cfg, with a URL. The script never sees the URL, the driver or the password, so the same code runs on every path:

Passwords are never written in scripts. They are secrets of the configuration of the machine (see System Library).

Configuration: eve.cfg and mappings.json

The connections are declared in the file eve.cfg of the machine and of the project (the project file adds to the machine file and wins on the same name). One setting for each line; a line that starts with # or ** is a comment.

core_db = "db/eve.db"                                    ** the core database, read as $EVE_CORE_DB
database "shop"  = "sqlite://db/shop.db"                 ** a connection name and its URL
database "sales" = "postgresql://etl@db1:5432/sales" exclusive
database "sales" schema_version = "select max(version) from flyway_schema_history"

A password is never in the file: it is a Secret of the machine. The file db/mappings.json is written by debug mode and committed with the code. For each connection it holds the schema version the mapping was validated against and a fingerprint of each mapped table; production reads it and trusts it. A SQLite file is created at the first use of its connection, with its folder; its schema version is PRAGMA user_version, 0 for a new file.

{ "connections": { "shop": { "schema_version": 3, "tables": { "customers": "9f2c..." } } } }

SQLite is the core

SQLite is part of the virtual machine. It needs no server and it is always there, but it never holds ETL data: it stores the logs and the metadata. Data goes to the database you connect: PostgreSQL, or evedb (DuckDB) for staging, the cache and the tests. There is no offline work: a local Eve session is connected to a remote session; when there is no remote server, the local Eve does the job and plays both client and server, with the core database and an evedb database connected. Each project keeps its files in its folder db/. The core database is a SQLite file named eve.db by default. Its name is set in the configuration file eve.cfg and read as the system variable $EVE_CORE_DB:

One file per role keeps the locks apart: a long load into the staging file never blocks the run history. The VM opens SQLite in WAL mode with foreign keys on, and makes the tables it creates STRICT, so a text can not hide in an integer column.

Records

A row type is a class derived from Record. The class holds the fields; it knows nothing about a database:

class Customer = {id: Integer, name: String, city: String?, version: Integer} <: Record;
class Order    = {id: Integer, customer_id: Integer, amount: Decimal, created: Instant} <: Record;

• A field of type String? may hold null (a nullable column). A field of type String never does;
• A record made by a literal, {id: 0, name: "Ann", city: null, version: 0} :Customer, is detached: plain data, unknown to any database;
• A record class may have methods. The method validate runs before every write (see Validation).

The table declaration

A table declaration binds a record class to a table of a connection. It declares a name, for example customers, that is an object of the library class Table(:Customer): the table seen as a buffer of records. A class hosts only methods, so the mapping is not in the class; one class can have several declarations (a file, a staging table, a remote table).

table customers: Customer in "sales" is
  key (id);                      ** the primary key, one field or several
  generated (id);                ** filled by the database: never written, read back after insert
  version version;               ** optimistic lock, checked and increased by every update
  column name as "cust_name";    ** a field whose column has another name
end customers;

table orders: Order in "sales" is
  key (id);
  generated (id, created);
  reference customer_id to customers;   ** a foreign key: orders are written after customers
end orders;

table countries: Country in "sales";    ** short form: key (id), nothing else

table is a contextual keyword: a keyword only at the start of a declaration. A field named key or version is still allowed. A table is declared in a driver, an aspect, a module or a macro, like a class.

Column = value mapping

The column of a field is its name, or the name of a column ... as clause. There is no case conversion and no plural rule: Eve quotes every name in the SQL, so CustName matches only CustName. When only the case differs, the message tells you the clause to write; Eve never guesses.

A pair outside the table, such as a String field and a numeric column, is a mapping error. So is a pair that could lose data, such as an Integer field and a numeric(30) column: a mapping must be safe for every row, not only for the rows of today.

The compiler turns each declaration into a plan, the ordered list of fields and columns. Eve never writes select *: it reads the columns of the plan in order, and the values of an insert or an update are always parameters, never text inside the SQL.

Debug mode reports, production refuses

Eve checks the code against the real database: the table exists, every field has its column, the types match without loss, a nullable column has an optional field, a column that is NOT NULL without default has a field, the key is a real key.

• Debug mode (eve -d): the compiler connects to every database named by a table and lists all the differences of all the tables in one run, each with the line of the declaration and a fix, or the whole class and table text generated from the database (db describe sales orders --eve). Errors stop the compilation; warnings, such as a column the class does not use, are printed. After a clean run, the compiler writes the mapping to db/mappings.json, with the schema version it was validated against and a fingerprint of each table, which you commit with the code;
• Production mode (the default): the compiler does not connect, and the VM trusts the file db/mappings.json. The database carries a schema version, a number that db migrate raises with every migration (for a database that a DBA keeps, eve.cfg gives a query that returns it, for example from the migration table of the DBA: Eve installs no table in a target database). The mapping file records the schema version it was validated against. When the VM opens a connection for the first time, it reads the schema version of the database and compares it with the file. If they are equal, it trusts the mapping and reads no catalog. If they differ, it raises a MappingError: the program stops before it has read or written a row, retry and resume are refused, and the process aborts with exit code 4. You validate the mapping again in debug mode, and commit the new file. The schema will not repair itself, and a retry loop would only hammer the database.

A value that can not be converted at run time, such as a text that is not a UUID, is a ConvertError with the table, the column and the key of the row.

Sessions and tracked records

The first use of a connection name in a process opens a session: the connection, its transaction and the records it tracks. Each process has its own sessions, so each aspect has its own connections and a started aspect never shares one with its siblings. A session is never shared state, so loading in parallel needs no special rule.

A record read with find! or where! is tracked. It remembers the original value of every field you change:

Setting a field back to its original value makes the record clean again. A record with no changed field writes nothing. You read the status with customers.status(c).

The update block

update c do ... done; changes several fields of one record without repeating its name. Inside the block the names of the fields are in scope, and let city := v; sets the field city of c:

new c := customers.find!(42);
update c do
  let city := "Lyon";            ** the field c.city
  let name := "Ann Smith";       ** the field c.name
done;

The block is only a way of writing: the record is the same tracked record, and the dirty fields and the statements are the same as for let c.city := v;. At done the method validate of the record runs, so an error is found at the end of the block, with all the fields changed together. A block works on one record; to change two records write two blocks. A set-based update of a whole table is SQL (execute!).

Methods of a table

The names follow one rule: a ! marks a result that can change from call to call. Every read of a database is one, so find!, where!, scan! and count! carry it. A write that returns nothing has no ! (add, remove, flush), and a write that returns counts keeps it (load!, execute!). The mark says nothing about parallel use: the compiler decides that.

Append

new c := {id: 0, name: "Ann", city: null, version: 0} :Customer;
let customers <+ c;               ** status: added; nothing is sent yet

At the end of the job one insert is sent. The generated fields are left out of the statement and read back: after the job, c.id holds the number given by the database.

Update

new c := customers.find!(42);
let c.city := "Lyon";             ** the field is dirty, the original value is kept

An assignment to a field of a tracked record is already the update: there is no block to write. The statement sent writes only the fields that changed, and checks the version:

update "customers" set "city" = ?, "version" = "version" + 1
where "id" = ? and "version" = ?

Ten thousand records with the same changed field are sent as one batch, not as ten thousand statements. Changing a key field, a generated field or a field of a read only table is an error; changing a key is a delete and an insert.

Delete

customers.remove(c);              ** status: removed; delete at the end of the job

At the end of the job Eve writes in a safe order: inserts first (customers before orders, from the reference clauses), then updates, then deletes in the reverse order (orders before customers). A cycle of reference clauses is a compile error.

Validation

A record class can check itself. The method validate runs before every insert and update, and a failure is an error of the job with the key of the record in the message:

class Product = {id: Integer, price: Decimal, version: Integer} <: Record is
  method validate(@self) is
    expect self.price > 0d;
  return;
end Product;

Transactions: a job is a transaction

You do not write begin. The first statement of a job that uses a connection begins a transaction on it. At done or stop the job writes the pending changes, then commits. If anything fails, the job rolls back. The records in memory keep their values but are detached, because they may not match the database any more: you read the data again, make another record, compare the two, or discard the changes by hand. A retry runs the job again and its reads give fresh records. Outside a job every change is written and committed at once, and debug mode warns about it.

Example: a dry run

# raise the prices of one category, with a dry run
primary aspect reprice is
  class Product = {id: Integer, category: String, price: Decimal, version: Integer} <: Record is
    method validate(@self) is
      expect self.price > 0d;
    return;
  end Product;

  table products: Product in "shop" is
    key (id);
    version version;
  end products;

  process main(category: String, percent: Decimal, dry_run = False: Logic) is
    new changed := 0;

    reprice: job
      set $isolation = "repeatable read";
    do
      for p in products.where!("category = ?", category) do
        let p.price := (p.price * (100d + percent) / 100d).round(2);
        let changed += 1;
      done;
      print "{changed} products to change";
      rollback if dry_run;           ** nothing is written, the job ends rolled back
    done reprice;                    ** one update per product: set price, version

  recover
    if $error is ConflictError do
      retry after 1s if $error.attempt < 3;     ** read the products again
    else if $error is DeadlockError do
      retry after 2s if $error.attempt < 5;
    done;
    abort;
  return;
end reprice;

Isolation and locks

The isolation of a job is the default of the database: read committed for PostgreSQL and MySQL, serializable for SQLite. A job changes it in its declarations with system variables, in the way an aspect shadows $timeout:

A deadlock or a serialization failure is a DeadlockError, a transient error. find!(key, lock: True) locks a row until the commit.

Optimistic locking

With a version clause every update and delete says and version = the version I read. If no row matches, someone else changed the row: the record becomes stale and the job fails with a ConflictError. The rollback detaches the records of the job, so a retry reads them fresh. Without version the last writer wins, and debug mode warns once per table that more than one job writes.

Long loads: commit and checkpoints

A job that loads ten million rows should not hold one transaction for an hour. commit; inside the loop commits each batch. But a retry runs the job again from its start, and would load the committed batches twice. The rule: a job with commit; keeps a checkpoint in the core database, written after the commit of the batch, and starts from it. The checkpoint and the batch are in two databases, so a crash between the two commits loads the last batch again: the writes of a batch must be idempotent (upsert or merge by key):

load: job
  new since := checkpoint.read!(:Instant)("orders", default: Instant.epoch);
do
  for batch in source.where!("created > ?", since).batch(10000) do
    for o in batch do
      let target <+ to_target(o);
    done;
    commit;                          ** the batch is committed in the target
    checkpoint.write("orders", batch[-1].created);   ** then the checkpoint, in the core database
  done;
done load;

Eve does not install a checkpoint table in the target: the target may be kept by a DBA. A write to the core database (logs, checkpoints) does not count as the write connection of the job. Debug mode warns when a job has commit; and writes that are not idempotent (inserts instead of upserts).

One connection per job

A job writes to one connection. It may read from others: there the transaction only reads and has nothing to commit. A write to a second connection is a compile error when the compiler sees it, and a DatabaseError at run time otherwise. To move data between two databases use two jobs: stage in one job, move in the next. Writing to two databases in one job is not supported in version 0.4; it is technical debt in the backlog.

Batch errors

When some records of a batch fail, in flush() or in load!, the batch is rolled back and the job fails with a BatchError. Its field failed is a collection of the failing records, each with its key and its reason. recover reads it as $error.failed: it can count the failures, log the records and decide. A retry runs the job from its checkpoint, so only the failed batch and the ones after it are processed again. A resume ends the job as failed and the process continues after done.

recover
  if $error is BatchError do
    for f in $error.failed do
      log_err "record {f.key}: {f.reason}";
    done;
    retry if $error.failed.count() < 10 and $error.attempt < 3;
  done;
  abort;

Direct SQL

Everything that is not rows is SQL. These methods of a connection run in the transaction of the session and track nothing:

new sales := database.connect("sales");
for r in sales.query!(:Order)("select id, customer_id, amount, created from orders where amount > ?", 100d) do
  print "{r.id}: {r.amount}";
done;

Values are always parameters. A {...} placeholder of Eve inside the SQL text is a warning, because it can open the door to SQL injection. After a direct write, call refresh on the records you still use: Eve does not know which ones the statement changed.

Scripts, procedures and commands

• Scripts. A SQL file of the project installs or changes a structure, disables indexes or prepares a bulk load (only on a connection that Eve owns): sales.script("create_orders.sql"). DDL goes through scripts and the command eve db migrate, never through the ORM;
• Stored procedures. There are two kinds: native procedures, written in the language of the database, and Eve procedures, written in Eve (below);
• Commands. The databases of a project are administered by the database part of eve: eve db list, add, remove, test, info, tables, describe, sql, script, create, create-table, migrate, backup, prepare, activate, reindex, cache, proc list, proc reload. They run with the rights of the database user of the connection: Eve adds no layer of rights.

A project with databases keeps its procedures in two folders:

Native procedures

A native procedure lives in the database. Eve does not read its language: it only needs its signature, which an external declaration brings in. A procedure without a result is an external procedure; one with a result is an external function whose name ends with !, because its result depends on the database:

external procedure archive_orders(before: Date) in "sales";
external function total_sales!(region: String) => (@total: Decimal) in "sales" as "etl.total_sales";

The name in the database is the Eve name, or the text after as. A call runs in the transaction of the job, like execute!. This works for every database that has stored procedures; for PostgreSQL, Oracle or MySQL it is the only kind.

Eve procedures

An Eve procedure is an exported procedure of a module in the folder proc/. The database part of eve checks these modules, keeps them compiled and runs them next to a SQLite or DuckDB database: in your own process when you run a script with eve -x, and in the machine when the project is served. A program calls them as a module, inside the transaction of its job:

** proc/orders.eve
module orders is
  export (archive);

  procedure archive(before: Date) is
    ** move the old orders to the archive table
    ...
  return;
end orders;

from proc use (orders);
...
orders.archive(cutoff);

A machine runs them by name too: the method db.call of the machine API takes "orders.archive" and its arguments (Eve Server). eve db proc list shows the procedures and their signatures; eve db proc reload reads proc/ again on a running machine.

SQL functions in Eve

SQLite and DuckDB let a program add its own functions to SQL. In Eve you mark such a function with the word sql:

sql function tax(amount: Decimal, country: String) => (@result: Decimal) is
  let result := amount * rate(country);
return;

Now SQL can call it, and the work happens inside the database engine, where the data is:

class Taxed = {id: Integer, tax: Decimal} <: Record;
new rows := shop.query!(:Taxed)("select id, tax(amount, country) as tax from orders");

• Registered for you. When the database part opens a SQLite or DuckDB connection, it registers every sql function of the program and of proc/, under its Eve name;
• Every one is registered. All the sql functions of the program are registered, the ones of the modules it imports too; a function can not be overloaded, because SQL finds it by its name;
• NULL only for a nullable type. A parameter of type String? receives SQL NULL as null. A parameter of type String can not: when SQL gives it NULL, the mapping fails and the query stops;
• An error stops the query. An error inside the function is an exception: the engine abandons the statement and query! or execute! raises $err_sql (code 59) with the name of the function and the message, for example SQL function ratio failed: Division by zero. A recover can handle it like any database error;
• Deterministic. A sql function has no !: the engine decides how many times and in which order it calls it, so the function does no input or output and opens no connection. It has one result, and its types are column types (Column = value mapping);
• Threads. DuckDB runs a query on several threads at once, and each of them may call your function. Eve protects the running program: one call at a time per session, or a pool of interpreters, each with its own memory;
• Only SQLite and DuckDB. PostgreSQL, MySQL and the other databases get no Eve functions. With them, read the data into the program, change it in Eve, and write it back with load! or one row at a time. This is why the authors expect DuckDB with Eve functions to be the fastest way for heavy transformations.

sql function ratio(a: Decimal, b: Decimal) => (@result: Decimal) is
  let result := a / b;
return;
...
  new rows := shop.query!(:Ratio)("select id, ratio(a, b) as r from pairs");
recover
  print $error.code == $err_sql;     ** True
  print $error.message;                ** SQL function ratio failed: Division by zero

sql is a contextual keyword: it is a keyword only before function, so you can still name a variable sql.

Introspection

The system variable $query holds the text of the last SQL statement of the process. In debug mode $trace prints every statement with its parameters (secrets are masked) and its duration.

Errors

In recover, retry after 2s; waits before it runs the job again, and $error.attempt counts the runs (1 for the first), so a retry has a limit: retry after 2s if $error.transient and $error.attempt < 5;. See Exceptions.

Drivers and the Eve server

A driver is a Zig module that implements one interface: open, transactions, prepared statements, batches, bulk load, the catalog for the validation, the dialect and the classification of errors. The core, the ORM and the server never depend on a vendor. The order: SQLite (embedded) and PostgreSQL in version 0.4, with Supabase and Neon, which speak its protocol; MySQL and MariaDB, and Cloudflare D1 in 0.5; MongoDB later, with an API of its own; Oracle is postponed (backlog); SQL Server by the community, through the same interface.

On path 3 a desktop or an edge machine holds no password and does not reach the database network. The Eve server holds the sessions, the drivers and a cache:

• A remote session has a lease, renewed while the client works; a client that crashed does not hold locks for long;
• Each transaction has an id, kept in the journal of the core database of the server. Eve installs no table in the target. After a broken connection the client asks the server whether that id is committed, and the server asks the database when the driver can answer (PostgreSQL can). If nobody can tell, the job fails with a fatal CommitError and an administrator checks;
• Only the changed columns travel: an update of one field of a record of fifty fields sends one value, the key and the version;
• cached 1h; keeps a reference table (countries, currencies) in the cache of the server (an evedb database). A write goes to the remote database first. The cache is never the authority: do not use a cached table for a decision that needs the current value;
• A bulk ETL between two databases should run on the server, with a remote apply, so the rows do not cross the network twice.

Failures across the layers

Read next: Files and ETL, EveDB, Multitasking