Drafting: declare and populate new tables in memory, beside the pipeline's tables on the server #1552

Drafting new tables without creating them on the server

When designing new tables for an existing pipeline, users need to declare them, insert or compute rows, and look at the results against the pipeline's real upstream data, before anything is created on the database server. Today the only way to try a definition is to declare it on the server, in a scratch schema. That needs privileges to create schemas and tables, leaves tables behind, and puts unreviewed tables next to the pipeline's own.

Drafting in memory removes all three. New tables are declared in memory, the pipeline's existing tables are read but never written, and everything declared is gone when the instance closes. When the design is right, its definitions are already the code: declared from a normal connection, they create the tables on the server the usual way.

An in-memory instance

database.backend = "duckdb" opens an in-memory instance: no server, no network, no credentials, no config file. Everything declared in it lives in the process. It works in a subprocess started with an empty environment, and nothing it holds can reach a server.

On its own it is a whole pipeline in memory, for tests, tutorials, and debugging. Drafting uses it in the two ways below: with copies of the server's tables loaded into it, or behind a drafting connection.

DuckDB is an optional dependency, installed as an extra like postgres is: pip install "datajoint[duckdb]", with duckdb added to [project.optional-dependencies] and to the test extra. A plain install does not pull it in. The adapter imports it only when an in-memory instance is opened, as the PostgreSQL adapter does with psycopg2, and without it raises an error that names the extra to install.

Tables from another database

A draft's new tables reference tables that already exist on the server. In memory, those are mirrors:

• Declared from the server table's heading: attribute names, types, codecs, primary key, and lineage, without declaring the mirror's own ancestors. A mirror is read-only; only loading writes to it.
• Loaded with rows the caller supplies, typically the rows a sample of keys restricts to, plus the ancestor rows a make may join. Loading is a bulk call that bypasses make, and it works for every tier, Computed and Part included (as allow_direct_insert allows today), so a downstream draft can be seeded with an upstream table's results.

Copying rows in is the default way to read the server. It lets the code being drafted run where it cannot reach the server: one process, holding the server login, reads the rows and hands them over, and the in-memory instance that runs make never holds a connection or a credential. Reading the server directly from the in-memory engine (DuckDB can attach MySQL and PostgreSQL) remains possible where that separation is not needed.

A drafting connection

For interactive work, a drafting connection combines the two: a server connection whose new declarations go to memory. DatabaseSettings gains a switch, for example drafting: bool = False (DJ_DRAFTING); the name is open. Connection._config is per-connection, so one process can hold a drafting connection and a normal one at once.

The Python module is the same on both:

# ephys_analysis.py
schema = dj.Schema('proj_ephys')

@schema
class ArtifactScore(dj.Computed):
    definition = """ -> Recording
                     -> ArtifactParams
                     ---
                     score : float """

On a drafting connection, each table binds where it exists:

So Recording and ArtifactParams are read from the server, and ArtifactScore is declared in memory. A part binds where its master binds; a new master declared with its parts is declared in memory with them.

Nothing is written to the server. Declaring, inserting, populating, deleting, and dropping act only on in-memory tables. An insert into, or a delete from, a table on the server raises, naming the table. The session needs only a login that can read the pipeline's schemas.

Declarations and references

Declaration is the real one. A new table goes through the same declaration checks as on the server, so a definition the server would refuse is refused in memory too.

References are recorded in DataJoint. A reference from an in-memory table to a server table or a mirror cannot be a foreign-key constraint, so it is recorded in DataJoint and emits no FOREIGN KEY clause. Attribute inheritance, types, lineage, and indexes are unchanged; only the constraint is dropped. Dependencies unions these edges with the catalog's, so parents(), key_source, Diagram, joins (with their lineage check), and restrictions work over one graph. A normal connection never sees them.

Matching the server a draft is for

An in-memory instance can be told which backend the draft is for (MySQL or PostgreSQL), and its declaration and insert checks follow that backend where the engine differs:

• decimal precision (DuckDB stops at 38 digits; MySQL allows 65);
• declared string lengths (DuckDB ignores the length in VARCHAR(n));
• key comparison (MySQL's default collation is case-insensitive);
• uuid storage, datetime precision, and unsigned integers;
• identifier length (64 characters on MySQL, 63 on PostgreSQL).

Where it cannot match, it refuses at declaration, so a draft that declares and populates in memory declares and populates on the server.

populate() in memory

• key_source and make work as on the server: over in-memory tables and mirrors, restricted to given keys when only a sample is wanted, with the three-part make_fetch / make_compute / make_insert. self.upstream is restricted to the key and raises on a table that is not an ancestor.
• Inserts are checked as on the server: types and ranges, string lengths, decimal precision, nullability and defaults, attributes not in the heading, duplicate keys, references (a part's master included), and a make inserting a key other than its own.
• A per-key outcome instead of a raise. With suppress_errors=True, populate reports for each key whether it succeeded and, if not, which stage failed (fetch, compute, or insert), the exception and its traceback, and what make printed. An optional per-key time limit stops a key that runs too long.

Codecs

Values stored in the table (blobs, numeric arrays, JSON) live in memory. A codec that writes to an object store (attachments, objects, <npy@>) writes to a temporary local store that lives as long as the instance, never to the pipeline's configured stores. An attachment is inserted from a file path and fetched decoded, as on the server.

Read-only use

An instance can be opened so that only populate writes: declarations, inserts, deletes, and drops refuse, except a make's inserts into its own table and its parts. A caller that has declared a draft and loaded its mirrors can then hand the instance to code it does not control.

Moving a draft between processes

• Export and import. A table's contents export to a JSON-safe form and load back: arrays with their dtype and shape, NaN and infinities, dates, decimals, and uuid are marked so they round-trip. Large values can be summarized on export (shape, a sample, minimum, maximum, mean) when only a preview is needed.
• A stable heading. A table's heading (primary key, attributes, types, codecs) has a canonical form, so a caller can hash it and tell whether a definition changed between runs.

Out of scope

• Branching tables that already hold data (changing a populated table's definition or rows on a branch). A different problem, with its own substrate.
• Persisting an instance. In-memory tables end with the instance; export and import cover moving their contents.

Open questions

1. The switch: a drafting flag on a server connection, or only the in-memory instance with mirrors, with a drafting connection built on top of it?
2. Object-store codecs: a temporary local store, or refusing such columns in drafts?
3. Which core types beyond those above cannot be held faithfully in memory, and does each refuse or match?
4. The per-key time limit: in the library, or left to the caller's process?

What this replaces

The previous version of this issue proposed declaring new tables into a physical draft schema, br_{branch_id}_{schema}, created on the server beside the pipeline's schema. Drafting in memory replaces it: nothing is created on the server, no draft schema needs creating, granting, or tearing down, and a read-only login is enough. Two parts of that proposal carry over: the binding rule (a table binds where it exists), and recording references in DataJoint rather than as constraints.