SDK

Your first table

This takes a few minutes once the engine is running and has one source. Here the source is a MongoDB replica set, the [sources.mongo] entry of the engine's config (section 2). The commands are the same on Windows and Linux.

axialdb sources
axialdb create-table sales.orders --source mongo --mode live --query "SELECT _id, total, status FROM shop.orders WHERE status <> 'draft'"
axialdb tables
axialdb query "SELECT status, COUNT(*) AS orders FROM sales.orders GROUP BY status"

1. sources lists the sources the engine knows. If yours is missing, stop and read section 2.
2. create-table makes a live table named sales.orders from the collection orders in the MongoDB database shop, keeping the documents whose status is not draft. The name is the AxialDB table. The MongoDB database is in the query's FROM. Nothing is created in MongoDB. For a live table, a DBA must first turn on post-images for shop.orders (section 3, step 3).
3. tables lists every table on the engine, one schema.table per line.
4. query runs ordinary SQL over those names. Rows are printed, and nothing is stored.

total is a text column here because the query has no CAST. To add it up, write CAST(total AS DECIMAL(12,2)) AS total in the create query. The rules are in The MongoDB create query.

1. What is in the box

There are two downloads for each system, Windows and Linux: the engine package and the optional MySQL shim package. Both are free and have no expiry. 6.0.0 was released on 2026-10-27; the zips are on the download page.

• Everyone installs the engine package, on the engine's machine. It reads MySQL and MongoDB with nothing installed on them.
• Add the MySQL shim to a MySQL server only to create and query tables from inside MySQL; the engine reads MySQL without it. Use the two packages of the same version together.

Where the files go

The engine listens on 127.0.0.1:9742 unless you change it. The command line uses that address when you give no --engine.

2. The engine's configuration

The engine reads one file, axialdb.toml. Restart the engine after you change it. The file holds passwords, so only the service account and administrators should be able to read it. The shipped file has more sections ([data], [logging], [license] and others) with working values. This section covers the ones you change to connect databases.

[helper]
listen = "127.0.0.1:9742"            # the engine's address. Beyond this machine: section 6

[engine]
live_apply_interval_secs = 1         # how often captured changes are applied to live tables
live_compact_interval_secs = 3600    # how often the stored changes of live tables are tidied

[default_naming]                     # where a new table lands, for every source
mirror_host_schema = true
target_schema = "analytics"
prefix_source = false
prefix_host_schema = false

[sources.orders]                     # a MySQL server. The name is yours
kind = "mysql"
host = "orders-db.example.com"
port = 3306
user = "axialdb"
password = "..."
capture_user = "axialdb_cdc"
capture_password = "..."
capture_server_id = 19001

[sources.mongo]                      # a MongoDB replica set
kind = "mongo"
uri = "mongodb://axialdb:...@mongo-eu.example.com:27017/?replicaSet=rs0"

Sources

Each database instance is a source: one [sources.<name>] section for each MySQL server and each MongoDB replica set. The addresses and passwords live here and nowhere else. A create never carries a connection string: one in a MySQL COMMENT would show in SHOW CREATE TABLE, and one on the command line would land in shell history.

• Source names are unique on the engine. A name is fixed once a table uses it, because the table's change log and position hang off the name.
• The name shows in axialdb sources, in --source, and in table names only when prefix_source puts it there.
• One engine serves any number of sources of either kind, on any machines.
• If a source is later removed from the config, its tables become failed with a reason that names it (section 7).

A MySQL source

The two accounts, for the address the engine connects from (here the engine runs on the MySQL server's machine), as in the engine's install guide:

CREATE USER 'axialdb'@'127.0.0.1' IDENTIFIED BY 'choose-a-password';
GRANT SELECT, SHOW VIEW ON *.* TO 'axialdb'@'127.0.0.1';
CREATE USER 'axialdb_cdc'@'127.0.0.1' IDENTIFIED BY 'choose-another-password';
GRANT REPLICATION SLAVE, REPLICATION CLIENT, SELECT ON *.* TO 'axialdb_cdc'@'127.0.0.1';

SELECT for axialdb may be narrowed to the databases your tables read. On a server with the MySQL shim, axialdb also needs CREATE, DROP and INSERT.

• No capture account, no live tables. A live create on a source without capture_user is refused with "capture is not configured for source ...". Snapshots still work.
• Server settings. Capture connects only to a server with binlog_format=ROW, binlog_row_image=FULL, binlog_row_metadata=FULL and gtid_mode=ON. The engine checks at every connect. If one is wrong, capture does not start, and axialdb sources shows why.
• A replica needs no plugin. With the MySQL shim, AxialDB keeps its own CREATE TABLE ... ENGINE=AXIALDB and DROP TABLE out of the binlog. A live table shows data as of what capture has read, so with a replica it trails the main instance by the replica's delay.
• You point capture at the right server. The engine does not check that the capture address is a replica of the main instance.
• Each MySQL source has its own capture task and change log. A change log follows one MySQL server. See "One source is one server" in section 6.

A MongoDB source

• MongoDB 8.0 and later, replica sets. One member is enough; a standalone server has no change stream, and sharded clusters are not supported.
• The account in the URI needs the built-in read role on the source database. It needs no write access.
• A live table needs post-images turned on for its source collection (section 3, step 3).
• The naming keys of the next part can be set under a MongoDB source too.

Naming: where a table lands

Every table has a name schema.table, unique on the engine. BI tools show the schema. The naming keys decide it. They are four keys, set for every source in [default_naming], and set again directly under a source (next to kind) to change it for that source only. A key set under a source wins.

With sources named orders (MySQL) and mongo (MongoDB), a table created as demo_perf.order_lines_av on MySQL, and as customers with FROM crm.customers on MongoDB, lands here:

On MongoDB, a schema typed with the name (create-table mongo.customers) replaces the schema the rules would pick, and nothing else: with prefix_source it becomes mongo.mongo_customers.

Inside MySQL, with the MySQL shim, a create names another schema in its comment: COMMENT='live; schema=reporting' puts the AxialDB table into the schema reporting.

The rules

• Order of precedence. --schema wins. Next, on MongoDB, a schema typed with the name. Otherwise the source's own keys decide, and [default_naming] fills in the keys the source does not set.
• --schema is exact. The table lands in that schema under its own name, with no prefixes.
• Names are lowercase. A name with capitals is lowercased without a message, whether you typed it or it came from the host. A MySQL table keeps the name MySQL stores (Demo.Order_Lines); AxialDB's copy of the name is demo.order_lines.
• Allowed characters. An AxialDB schema or table name matches ^[a-z][a-z0-9_]{0,63}$. A name that would get any other character (a hyphen from a database demo-perf, say), or a prefixed name over 64 characters, is refused with a request for --schema or another name. Nothing is replaced silently. This rule is about the AxialDB name only: a collection such as orders-2024 is read with FROM "orders-2024" (quoted, as in the MongoDB create query); give its table a name of your own, such as sales.orders_2024.
• Reserved schemas. axialdb (engine status), information_schema and public. Names such as mysql_ and mongo_ are ordinary.
• A name is taken once. A create onto a name that exists is refused, on every source. Drop the table first. If two sources have the same database or table names, separate them with a target_schema of their own for one source, or with prefix_source.
• Computed once. The name is computed when the table is created and stored. Changing the naming config never renames an existing table. There is no rename.
• What MySQL lists, on a server with the MySQL shim. SHOW TABLES lists MySQL tables only, in the database the create named. A schema that is not mirrored is not a MySQL database, so analytics.order_lines_av shows in AxialDB while MySQL shows demo_perf.order_lines_av.
• With the defaults a table from MySQL has the same name in AxialDB and in Tableau as its database and table in MySQL.

Other settings

• [engine]: live_apply_interval_secs (shipped value 1) is how often captured changes are applied to live tables, for every source. live_compact_interval_secs (shipped value 3600) is how often the stored changes of live tables are tidied.
• [access]: secrets, TLS and the network check. See section 6.
• [license]: leave as shipped. To lift the caps, copy the license file axialdb.lic beside axialdb.toml and restart the engine. The caps are 5 tables, 2 of them live, across the whole engine, MySQL and MongoDB tables together. A license removes the caps and nothing else.

3. Several databases, step by step

This walk-through ends with one query over a MySQL table and a MongoDB table. The engine runs on a machine of its own, engine.example.com. If your engine shares a machine with MySQL, skip the secrets: a connection from the engine's own machine needs none.

1. Install the engine. Put the engine package on engine.example.com and start the service. Check it on the engine's machine with axialdb sources.
2. Prepare the MySQL server (orders-db.example.com).
   • Optional: to create and query tables from inside MySQL, install the MySQL shim package: the plugin file that matches SELECT VERSION(), its bridge library, and the helper functions script. The engine reads the server without it.
   • For live tables, check the server settings. Expect ROW, FULL, FULL, ON:

     SELECT @@binlog_format, @@binlog_row_image, @@binlog_row_metadata, @@gtid_mode;

   • Create the engine's account and the capture account for the engine's address ('axialdb'@'engine.example.com', 'axialdb_cdc'@'engine.example.com').
   • With the shim, give the plugin its settings file, axialdb-shim.toml (section 6). Its source must be the name in the engine's config, here orders.
3. Prepare MongoDB (nothing is installed).
   • Create a user for the engine, with the read role on the database.
   • For a live table, a DBA enables post-images once on each source collection (the engine's account does not need the right):

     db.runCommand({ collMod: "customers", changeStreamPreAndPostImages: { enabled: true } })

4. Write the engine's config. One [sources.<name>] for each, plus the secrets for an engine that other machines reach:

   [helper]
   listen = "0.0.0.0:9742"
   
   [access]
   client_secret = "a long random text"           # for the axialdb command line, ODBC, JDBC
   # tls_cert = "engine-cert.pem"                 # see section 6
   # tls_key = "engine-key.pem"
   
   [sources.orders]
   kind = "mysql"
   shim_secret = "another long random text"       # only with the shim: what orders-db's plugin proves
   host = "orders-db.example.com"
   port = 3306
   user = "axialdb"
   password = "..."
   capture_user = "axialdb_cdc"
   capture_password = "..."
   capture_server_id = 19001
   
   [sources.mongo]
   kind = "mongo"
   uri = "mongodb://axialdb:...@mongo-eu.example.com:27017/?replicaSet=rs0"

   Restart the engine.
5. Check the links. On a server with the shim, SELECT axialdb_init(); in MySQL opens a connection to the engine, proves the secret and says which source this server is. From your PC:

   axialdb sources --engine engine.example.com:9742 --secret-file client.secret

   Both sources should be listed, and the MySQL source's capture state should be ok.
6. Make a MySQL table. Inside MySQL, with the shim, as we did on our lab (the column list is shortened):

   CREATE TABLE demo_perf.order_lines_av
     ENGINE=AXIALDB COMMENT='live' AS
   SELECT ol.id, ol.order_id, ol.line_total,
          ol.customer_id, o.order_date, o.channel
   FROM order_lines AS ol
   LEFT JOIN orders AS o ON ol.order_id = o.id

   The same table can be made from the command line with axialdb create-table demo_perf.order_lines_av --source orders --mode live --query "SELECT ...".
7. Make a MongoDB table.

   axialdb create-table mongo.customers --source mongo --mode live \
     --query "SELECT _id, CAST(customer_id AS BIGINT) AS customer_id, legal_name, address.city AS city, address.state AS state FROM crm.customers"

   (On Windows cmd, end the first line with ^ instead of \, or put it all on one line.)
8. List the tables. axialdb tables --engine engine.example.com:9742 --secret-file client.secret prints one schema.table per line. You should see demo_perf.order_lines_av and mongo.customers.
9. Ask one question of both. This is the query we ran on our lab with sample data. demo_perf.order_lines_av has 3,920,000 rows; mongo.customers has 10,000 documents.

   SELECT c.state,
          COUNT(DISTINCT c.customer_id) AS customers,
          COUNT(*) AS order_lines,
          ROUND(SUM(o.line_total), 2) AS revenue
   FROM demo_perf.order_lines_av o
   JOIN mongo.customers c ON c.customer_id = o.customer_id
   GROUP BY c.state
   ORDER BY revenue DESC
   LIMIT 5

   Run it with axialdb query "..." and the same --engine and --secret-file. The result on our lab:

   state	customers	order_lines	revenue
   FL	1037	406504	24659544.0
   MO	522	204624	12741176.0
   AZ	523	205016	12588688.0
   VA	524	205408	12076736.0
   OR	500	196000	12044984.0

   This query was not timed by the method on the measurements page.

Adding a second MySQL server

Add another section, with a name of its own. When that server has the MySQL shim, give its plugin the same name in its settings file:

# in the engine's axialdb.toml
[sources.billing]
kind = "mysql"
shim_secret = "a third long random text"     # only with the shim
host = "billing-db.example.com"
port = 3306
user = "axialdb"
password = "..."
capture_user = "axialdb_cdc"
capture_password = "..."
capture_server_id = 19002

# in billing-db's axialdb-shim.toml, only with the shim
engine = "engine.example.com:9742"
source = "billing"
secret_file = "/etc/axialdb/shim.secret"

Tables from every source can be joined in one query. The split into schemas organizes the data; it never blocks a query. A query's result is not stored and does not count toward the caps. If both servers have a database and a table of the same name, the second create is refused as taken; separate them with the naming keys (section 2) or --schema.

4. The command line

The axialdb command line is a thin client of the engine. It needs the engine's address and nothing else. It does not read the engine's config and it never connects to MySQL or MongoDB: a create or a drop is a request to the engine, which holds the accounts and runs the work on the source.

The same help is printed by axialdb --help:

axialdb --version
axialdb sources
axialdb tables [--schema name] [--source name]
axialdb query [--schema name] "SQL"
axialdb create-table name [--source name] [--schema name] [--mode snapshot|live] --query "SELECT ..."
axialdb drop-table schema.table
axialdb export-tables > tables.json
axialdb create-tables < tables.json

Connecting

--engine host:port

Every command takes it. Without it the engine is 127.0.0.1:9742.

--secret-file path

An engine on another machine asks for its client secret. This names a file that holds it. The environment variable AXIALDB_SECRET works too. There is no flag for the secret itself, because a flag stays in shell history and in the process list.

--tls-ca path

Encrypts the connection. The file is the PEM file with the engine's certificate (or the authority that signed it).

• On the engine's own machine, neither a secret nor TLS is needed.
• A flag a command does not have is an error. A flag given twice is an error. Both --name value and --name=value work.
• Output goes to standard output. Counts and messages go to standard error. Exit code 0 is success, 1 is a refusal or an unreachable engine, 2 is a wrong command line.
• AxialDB names are lowercase: capitals you type are lowercased.
• axialdb --version prints the version and build of the command line itself, not of the engine.

sources

axialdb sources [--engine host:port]

Lists the sources the engine knows, in seven columns: name, kind, address, identity, capture (the address changes are read from), capture_status and tables (how many tables come from the source). No secrets are shown. The capture state is ok, not configured, or down since <time>: <reason>. The same list is the table axialdb.sources. The output is a header line and then one tab-separated line for each source.

tables

axialdb tables [--schema name] [--source name]

Prints every table on the engine, one schema.table per line, from every source. --schema and --source only narrow the list, and they combine.

create-table

axialdb create-table name [--source name] [--schema name] [--mode snapshot|live] --query "SELECT ..."

name

The table that is created. The form depends on the source (below).

--source name

One of the names in axialdb sources. You can leave it out when the engine has exactly one source.

--mode snapshot|live

snapshot (the default) copies once. live keeps the table current. Any other value is refused.

--query "SELECT ..."

Required. The rows to keep. On MySQL it is MySQL SQL. On MongoDB it is AxialDB's small dialect (below).

--schema name

The AxialDB schema, exactly. It wins over the naming rules and over a schema typed with the name, and the table gets no name prefix.

On a MySQL source

• The name is database.table: the MySQL database and table, as on that server. A name without a database is refused. The query may read any databases. MySQL runs it in its own dialect.
• On a server with the MySQL shim the engine runs CREATE TABLE <database>.<table> ENGINE=AXIALDB [COMMENT='live'] AS <query> there, with the source's user, and the table also appears in MySQL. On a server without it the engine copies the query's result itself, and the table exists only in AxialDB.
• The AxialDB table gets the same name unless the naming rules or --schema say otherwise.
• A live create returns when the table is healthy, or says which status it was left in.
• A live MySQL table can be one table (InnoDB or MyISAM), or a fact table with LEFT JOIN or inner JOIN to dimension tables on their primary keys; source columns only; a primary key on every source table; a view or a subquery only when it is a plain select; ORDER BY only with LIMIT; no GROUP BY. See the architecture page and live-tables.md.

axialdb create-table demo_perf.orders_wide --source orders --mode live \
  --query "SELECT o.id, o.customer_id, o.amount, c.segment FROM demo_perf.orders o LEFT JOIN crm.customers c ON c.id = o.customer_id" \
  --schema reporting

This makes the AxialDB table reporting.orders_wide. On a server with the MySQL shim, MySQL also gets the table demo_perf.orders_wide. The fact table's key o.id and the join column o.customer_id are in the select list, as a live table needs.

On a MongoDB source

• Nothing is created in MongoDB, so the name is the AxialDB table: schema.table, or the table name alone to let the naming rules pick the schema.
• The MongoDB database is in the query: FROM database.collection. A FROM without the database is refused.
• The engine reads the query, connects with the source's URI, copies the collection, and for live follows the change stream.

axialdb create-table sales.orders --source mongo --mode live --query "SELECT _id, total, status FROM shop.orders WHERE status <> 'draft'"

Checks at create

• A name that already exists, on every source. Drop the table first.
• A sixth table, or a third live table, on the free version. The create is refused with a clear message and nothing else changes.
• A live create on a MySQL source with no capture_user.
• A live MySQL query outside what is listed above. There is no silent fallback to a snapshot. ENUM and SET columns work in a snapshot and are refused in a live table.
• A column type AxialDB does not hold (binary columns, BIT, spatial types, a DECIMAL wider than 38 digits). The message names the column. TIME is kept as text.

Column types

An AxialDB table has seven column types: integer (64-bit), double, decimal up to 38 digits, boolean, timestamp, date and text. A MySQL column keeps its own type (DATE is a date, DATETIME and TIMESTAMP are timestamps, DECIMAL is exact, TINYINT(1) is a boolean). A time with a zone is converted to UTC, and a time without one is taken as UTC. Through the command line, ODBC and JDBC you see UTC. A query that arrives through a MySQL session is evaluated in that session's time zone.

The MongoDB create query

For a MongoDB source, --query is not MongoDB's language and not MySQL's. It is a small SQL dialect that AxialDB reads and turns into a MongoDB read and a column list. Anything outside it is refused at create, with a message that names the construct.

SELECT _id,
       CAST(customer_id AS BIGINT) AS customer_id,
       legal_name,
       address.city AS city,
       contacts[0].first_name AS customer_0_first_name
FROM crm.customers
WHERE address.state = 'GA' AND CAST(customer_id AS BIGINT) >= 100

SELECT

Field paths only. A dot steps into a nested object. [n] reads an array element, counting from 0 as MongoDB does. AS names the column. A nested path without AS becomes the path with _ between steps (address_city). A missing field, or a step into a value of the wrong kind, is NULL. A nested object or array named as a whole column is stored as plain JSON text.

FROM

One collection, written database.collection. A standard MongoDB view is accepted for a snapshot only. Joins and reshaping stay in MongoDB: build the collection you want there and read it.

_id

Required, under that name. It is the key of the table. It is never cast and never renamed; both are refused. It is stored as text: an ObjectId as its 24 hex characters, a string as is, a number as its digits, a document as plain JSON. For a typed copy, select the field a second time: SELECT _id, CAST(_id AS BIGINT) AS customer_id, ....

WHERE

=, <>, <, <=, >, >=, IN, IS [NOT] NULL, AND, OR, NOT, on field paths (with an optional CAST) and literals. A missing field is NULL.

Refused

SELECT *, joins, GROUP BY, ORDER BY, LIMIT, and functions other than CAST. Group and sort when you query the table.

Types

Types are declared with CAST(path AS BIGINT | DOUBLE | DECIMAL(p,s) | BOOLEAN | TIMESTAMP | DATE | VARCHAR). Each cast gives a real column of that type. A path without a cast is a text column. No schema is guessed from the data.

• A failed cast is an error, not NULL. A missing field or a BSON null gives NULL. A value that is present and does not convert does not: 'abc' or 3.5 as BIGINT, or text that is not a timestamp. At create, the create is refused and nothing is published. On a live table, the table fails closed (section 7). The message names the _id, the column and the value.
• Dates in text. Only ISO forms are read. With Z or an offset, text is that instant. Without a zone it is that time in UTC. 2026-01-31 is a day: it casts to DATE, not TIMESTAMP. 1/31/2026 and the like fail.
• Nothing is rounded. A value with more digits or decimal places than its DECIMAL(p,s) is a failed cast.
• WHERE is typed and checked at create. A path has the type of its cast, or text without one. A literal of another kind is refused, and the message shows the fix: n = 5 is refused; CAST(n AS BIGINT) = 5 and n = '5' are accepted. A numeric _id needs the cast too.
• Literals. A number for BIGINT, DOUBLE and DECIMAL; TRUE or FALSE for BOOLEAN; '2026-01-31' for DATE. A TIMESTAMP literal is a complete instant with its zone: '2026-02-01T00:00:00Z' or '2026-02-01T00:00:00-05:00'.
• A period includes its start and excludes the next start: CAST(at AS TIMESTAMP) >= '2026-01-01T00:00:00-05:00' AND CAST(at AS TIMESTAMP) < '2026-02-01T00:00:00-05:00'.
• A whole array or object is not compared. Pick an element (tags[0]) or a nested field (address.city).
• Left to right. Conditions are evaluated in the order written and stop as soon as the result is known, so a guard written first protects a cast written after it: kind = 'order' AND CAST(amount AS BIGINT) > 5. The casts in the SELECT list run only for documents the WHERE keeps.
• Column names are lowercase, and lowercased silently (AS firstName gives firstname). Two columns of the same name are refused, and the message names both paths. A field or collection name with other characters is written in double quotes: SELECT _id, "first-name" AS first_name, address."zip code" AS zip FROM "orders-2024". A quoted name that is not a valid column name needs AS.
• Note. Two documents whose _id gives the same stored key (the number 1 and the text "1") refuse the create, and the message names both. After the create of a live table this is not checked: a later document with the stored key of another replaces that row.

drop-table

axialdb drop-table schema.table

• The name is the AxialDB name, as axialdb tables lists it. It is the same on every source, and there is no --source.
• A drop removes everything the table consists of. For a MySQL table, when the server has the shim, the engine drops the MySQL table too. For a MongoDB table there is only the AxialDB table.
• It prints dropped schema.table, or no such table schema.table. A drop removes whatever exists and reports success; a table half there is not refused.
• Inside MySQL, with the shim, DROP TABLE on an AxialDB table removes both halves. It must be a statement of its own: a DROP TABLE that names an AxialDB table together with other tables is refused.
• ALTER TABLE, RENAME TABLE and TRUNCATE TABLE on an AxialDB table are refused, each with a message that says to drop the table and create it again. AxialDB tables are read-only copies: you never INSERT, UPDATE or LOAD into them.

query

axialdb query [--schema name] "SQL"

• Runs engine SQL over schema.table names. Rows are returned and nothing is stored. It does not use a table slot or a live slot.
• Filters, joins and aggregates work across any schemas, from any source.
• --schema lets the tables of one schema be written without the schema. It does not hide the other schemas.
• Output is a header line, then one tab-separated line per row. A NULL is an empty cell. The row count goes to standard error as N rows.

axialdb query "SELECT c.segment, SUM(o.amount) FROM app.orders_av AS o JOIN demo_perf.customers_av AS c ON c._id = o.customer_id WHERE o.amount > 0 GROUP BY c.segment"
axialdb query "SELECT 'eu' AS region, COUNT(*) FROM app_eu.orders_av UNION ALL SELECT 'us', COUNT(*) FROM app_us.orders_av"

Two engine tables describe the engine itself, in the reserved schema axialdb: axialdb.tables (with the columns schema_name, table_name, mode, status, source, host_database, host_object and shims) and axialdb.sources.

axialdb query "SELECT schema_name, table_name, mode, status FROM axialdb.tables"

export-tables and create-tables

axialdb export-tables > tables.json
axialdb create-tables < tables.json

These two are the recovery pair. They are described in section 8.

5. Connecting a BI tool

ODBC and JDBC are built on the same client as the command line, so they see the same tables, with the same schema.table names, from every source. Both are read-only. They are in the free download, not an add-on.

• Date, timestamp and decimal columns arrive as real types. A timestamp has no zone, and values are in UTC.
• The engine's client secret is the password. The user name is ignored. An engine on the same machine asks for none; a password that a BI tool sends anyway is not used there.

ODBC

Driver={AxialDB};Server=127.0.0.1;Port=9742;DbIdentity=demo_perf;

For an engine on another machine:

Driver={AxialDB};Server=engine.example.com;Port=9742;DbIdentity=demo_perf;AllowRemote=1;PWD=<client secret>;TlsCa=C:\certs\engine-cert.pem;

In a DSN, set DbIdentity in the AxialDB setup dialog.

Register the driver once, with the script that ships in its folder. It is registered under the name AxialDB, which is the name Tableau's customization file expects.

• Windows: put the folder where it will stay, then run .\register-axialdb-odbc.ps1 from an elevated PowerShell. Windows records the path of axialdb_odbc.dll. If PowerShell answers that the script is not digitally signed, the zip was unpacked with the mark Windows puts on a browser download: Unblock-File .\*.ps1 and run it again, or powershell -ExecutionPolicy Bypass -File .\register-axialdb-odbc.ps1. Unblocking the zip before you unpack it (right-click, Properties, Unblock) avoids this for every file in it.
• Linux: install unixODBC, then run sudo bash register-axialdb-odbc.sh. The driver is copied to /usr/local/lib/axialdb/ and registered for every user.

The driver is 64-bit.

JDBC

jdbc:axialdb://engine.example.com:9742
jdbc:axialdb://engine.example.com:9742/demo_perf

• The optional path is the default schema for unqualified names. schema.table works without it.
• The driver class is com.axialdb.jdbc.AxialDbDriver.
• The password field is the engine's client secret. The user name is ignored.
• The driver property tlsCa names a PEM file with the engine's certificate and encrypts the connection.
• The driver needs two files side by side: axialdb-jdbc.jar and the native library (axialdb_jdbc.dll on Windows, libaxialdb_jdbc.so on Linux). It loads the native library from the folder that holds the jar. If that fails, name the file with the Java option -Daxialdb.native=<full path of the library>. Java 17 or later.
• A date, timestamp or decimal column comes back as java.sql.Date, java.sql.Timestamp or java.math.BigDecimal. A timestamp has no zone and the driver does not shift it.

Tableau

Tested by hand with Tableau Desktop on 6.0.0.

1. Install the Tableau customization file axialdb_odbc.tdc that ships with the ODBC driver: run .\install-tableau-tdc.ps1 from the driver's folder (the unblock note above applies to it too), or copy the file yourself. Tableau reads it from the Datasources folder of your Tableau repository, for example Documents\My Tableau Repository\Datasources. Without this file, Tableau does not work well with the driver.
2. Restart Tableau Desktop completely. Restart it again whenever you replace the .tdc file or the driver.
3. Connect, choose Other Databases (ODBC), pick the AxialDB driver. To work in one schema, put DbIdentity=<schema>; in String Extras; leave it out to see every schema, so a join of a MySQL table and a MongoDB table runs in the engine. For an engine on another machine, the same string also carries AllowRemote=1;, PWD= and TlsCa=.
4. Pick tables from the list, or use Custom SQL.

Prefer Live connections: a live worksheet pushes its GROUP BY to the engine, so a large table can be aggregated in the view. Drag-and-drop, Custom SQL, Live and Extract on small tables were the flows tested.

Power BI

Tested by hand with Power BI Desktop on 6.0.0.

1. Choose Get Data, then ODBC.
2. Give the connection string, a DSN, or the advanced options. DbIdentity=<schema>; is optional, as for Tableau.
3. Use Custom SQL, or a narrow query, for large or wide tables.

Power BI Import has no Live mode like Tableau's. Do not Import a full wide table: write an aggregate, or select fewer columns.

DBeaver

Tested by hand on 6.0.0, with the JDBC driver.

Put axialdb-jdbc.jar and the native library in the same folder. Then:

1. Open Database, then Driver Manager, then New.
2. Class name: com.axialdb.jdbc.AxialDbDriver.
3. URL template: jdbc:axialdb://{host}:{port}.
4. Libraries: add axialdb-jdbc.jar.
5. Make a new connection with the engine's host and port 9742. Any user name works. The password is the engine's client secret, when it has one.

6. The engine on another machine

The engine can run on a machine of its own, and one engine can serve several MySQL servers. The shim's plugin goes on each MySQL server that should create and query tables from inside MySQL, and each server is one [sources.<name>] entry in the engine's config. MongoDB needs nothing: the engine connects out to it.

What crosses the network

• The shim's plugin opens connections to the engine's port, 9742. The rows of a CREATE TABLE ... ENGINE=AXIALDB go to the engine over them, and query results come back.
• The engine opens connections to MySQL: to the main instance when a live table is created (it reads the source tables' columns and keys and the server's position there, also for a CREATE typed in MySQL) and for create-table and drop-table, and to the main instance or a replica to read the binlog for live tables. It connects out to MongoDB.
• Clients (the command line, ODBC, JDBC) connect to the engine's port.

On the engine's machine

[helper]
listen = "0.0.0.0:9742"                    # or one address of this machine

[access]
client_secret = "a long random text"       # for the axialdb command line, ODBC, JDBC
# tls_cert = "engine-cert.pem"             # both, to accept TLS
# tls_key = "engine-key.pem"

[sources.orders]                           # one entry per MySQL server; the name is yours
kind = "mysql"
shim_secret = "another long random text"   # only with the shim: what this server's plugin proves
host = "orders-db.example.com"
port = 3306
user = "axialdb"
password = "..."
capture_user = "axialdb_cdc"               # live tables only
capture_password = "..."
capture_server_id = 19001                  # different for every source

• A connection from the engine's own machine needs no secret.
• A connection from another machine proves a secret before it may do anything. The secret is not sent: the engine sends a random challenge, and the caller answers with a keyed hash of it. The engine answers with a proof of its own, so each side knows the other holds the secret. A wrong secret, or none, is refused.
• client_secret opens everything a client does. A source's shim_secret opens only what that server's plugin does, and only for that source's tables.
• Treat client_secret as an administrator's secret. Whoever holds it can create and drop tables. The engine writes every request that changes something and does not come from a plugin to its log as admin request, with the caller's address, how it was let in (this machine, client secret, open network), and the table. Queries are not logged.
• The engine does not start with listen beyond its own machine and no secret in the config. allow_open_network = true under [access] turns that check off: anyone who reaches the port may then query, create and drop, and through the engine acts on MySQL under the source's account. Use it on a closed lab network only.
• With tls_cert and tls_key set, every connection from another machine has to use TLS: each plugin (tls = true) and each client. The engine's own machine may still connect in the clear.
• Open port 9742 on the engine's machine for the MySQL servers and the clients. On each MySQL server, let the engine's machine reach MySQL's port, and create the accounts for its address with the grants listed in section 2. Restart the engine after changing its config.

On each MySQL server with the shim

Install the plugin, the bridge and the helper functions, as the MySQL shim package's guide says. There is no engine and no axialdb.toml on this machine. The server has a small file of its own, axialdb-shim.toml, found through the AXIALDB_CONFIG variable of the MySQL service:

engine = "engine.example.com:9742"         # the engine's machine
source = "orders"                          # this server's [sources.<name>] on the engine
secret_file = "/etc/axialdb/shim.secret"   # one line: the source's shim_secret
# tls = true
# tls_ca = "/etc/axialdb/engine-cert.pem"  # the engine's certificate, or the authority that signed it
query_timeout_secs = 300
publish_retry_secs = 15
log_level = "info"

• With the engine on the same machine, engine is 127.0.0.1:9742 and the secret file is not read.
• A key the file does not have is an error. If you point AXIALDB_CONFIG at the engine's config by mistake, the message says so.
• A server whose source is not a MySQL source of the engine is refused, with a message that names it.

Linux:

sudo mkdir -p /etc/axialdb
sudo cp axialdb-shim.toml /etc/axialdb/axialdb-shim.toml       # then edit engine and source
printf '%s\n' 'another long random text' | sudo tee /etc/axialdb/shim.secret >/dev/null
sudo chown mysql:mysql /etc/axialdb/shim.secret
sudo chmod 600 /etc/axialdb/shim.secret
sudo mkdir -p /etc/systemd/system/mysql.service.d
sudo tee /etc/systemd/system/mysql.service.d/axialdb.conf >/dev/null <<'EOF'
[Service]
Environment=AXIALDB_CONFIG=/etc/axialdb/axialdb-shim.toml
EOF
sudo systemctl daemon-reload
sudo systemctl restart mysql

Windows: put axialdb-shim.toml and shim.secret in C:\ProgramData\AxialDB\mysql\. Keep the Machine variable AXIALDB_CONFIG at C:\ProgramData\AxialDB. Let only the MySQL service account and administrators read shim.secret. Restart MySQL.

Check from MySQL:

SELECT axialdb_init();

It opens a connection to the engine, proves the secret, and says which source this server is. An error names what is wrong: the engine cannot be reached, the secret does not match, or the source is not in the engine's config.

One source is one server

The engine learns a server's id (@@server_uuid) from its plugin. Two servers must not use the same source name. Every table records the server it was made on, snapshot and live alike. While a source has any table, the engine refuses a different server under that name and names both ids. After a main instance has really been replaced by a server with a new id, drop the old server's tables, snapshot tables too, and create them again (section 8). A server restored with its data folder keeps its id and needs none of this.

Clients on another machine

tls_ca is a PEM file with the engine's certificate, or with the certificate of the authority that signed it. A self-signed certificate works. The certificate has to name the address the clients use for the engine (a DNS name or an IP address in its subject alternative names).

When the connection is lost during a create in MySQL

• Before MySQL has committed the CREATE TABLE: the statement fails and MySQL has no table. The engine removes what it had received.
• After MySQL has committed and before the engine was told to publish: the plugin keeps trying for publish_retry_secs. If that fails too, the statement ends with an error that says the table is created in MySQL and not published. The next statement that uses the table publishes it. Until then a query on it fails with a message, and DROP TABLE removes both halves. When nobody uses the table, the engine settles it itself about once an hour, which takes between one and two hours after the create.

Upgrades

The plugin and the engine do not have to be replaced in the same moment. The plugin tells the engine which protocol it speaks. When the two differ, the engine refuses that server's plugin with a message that says which side is older. The release notes say when the protocol changes and which side to upgrade first.

7. Statuses, and what to do

A live table reports its state in the status column of axialdb.tables. A source reports the state of its capture in axialdb sources.

axialdb query "SELECT schema_name, table_name, mode, status FROM axialdb.tables"
axialdb sources

What a live MySQL table keeps following

Adding a column, adding or changing an index, changing a column the table does not use, another table, a view, a user, and CREATE DATABASE leave a live table following its source. A statement stops a table only when it can change the table's rows or the columns it uses.

Capture states in axialdb sources

• ok: capture is running (or has nothing to capture).
• not configured: the source has no capture_user. Snapshots work; a live create is refused.
• down since <time>: <reason>: capture cannot reach the source or the server settings are wrong. The reason names which. Tables of that source show stale since.

Common messages and what to do

• "already exists": the name is taken. Drop the table first, or separate the sources with naming keys or --schema.
• "capture is not configured for source ...": set capture_user and capture_password for that source and restart the engine.
• A command line error that names a flag: the command does not take that flag. Run axialdb --help.
• A MongoDB create refused: the message names the path or construct and shows what to write (a CAST, a literal, AS, the database in FROM).

8. Recovery

Everything in the engine is derived data. A table can be built again from its definition and its source, so only the list of definitions has to survive. AxialDB has a manual recovery procedure. It is manual, and supported. There is no automatic failover.

Save the definitions

axialdb export-tables > tables.json

This writes one entry for every table that came from a source: source, table (the name as create-table takes it), schema only when --schema was given, mode and query. Keep the file off the engine's machine, for example with a nightly job. If the engine's machine is lost, the file is what you need.

Create the tables again

axialdb create-tables < tables.json

• It creates each table through the engine, as create-table would, and prints each result.
• A table that fails does not stop the others. A line on standard error names it and says why, and the last line reads N created, M failed. If any failed, the exit code is 1.
• Names stay. They are computed from the same config, so each table comes back with the same schema.table. BI workbooks and ODBC or JDBC connections need no change.
• The cost is time, not data. Each table is unavailable while it is copied again in full.
• A file written by a build before 6.0.0 with MongoDB entries is refused, because its FROM names no database. Export again with the new build.

After a MySQL main server is lost

1. Promote the replica, or restore the server. When the server had the shim, install the shim's plugin on the new main instance.
2. In the engine's config, point the source at the new main instance, and capture at its new replica (or at the new main instance when there is no replica yet).
3. Restart the engine and drop the old tables, which can no longer be followed.
4. Run axialdb create-tables < tables.json.

If the new server has a new id, the old server's tables, snapshot tables too, are dropped first (section 6, "One source is one server"). A change log follows one MySQL server: a live create from a new server is refused while live tables of the old one are left.

After the engine's machine is lost

Install a new engine, restore axialdb.toml, and run create-tables from the saved file. The same steps move tables to another engine, or to another server in a planned move.