Connect databases, configure partitioning, and understand AnyLog's SQL dialect for querying distributed edge data.

AnyLog stores data in local relational databases on Operator nodes. Queries issued against the network are translated into SQL and executed on the relevant Operators, with results aggregated and returned to the querying node.

A node can connect to multiple databases simultaneously. Each database is identified by a logical name (the dbms parameter used in commands and queries). Additionally, the capabilities and limitations of the supported SQL derive from the incompatibility between the different databases.

Every AnyLog node hosts its data in a local relational database, but you never address that physical database directly. Instead, you work with a logical database — a name you choose, associated with whatever physical database actually backs it on that node. The same logical name can be backed by different physical databases on different nodes; a node operates identically either way. This decoupling is what lets AnyLog present data spread across many physical databases, on many machines, as a single queryable collection.

Supported databases

SQLite is a serverless, self-contained SQL database engine designed to be embedded within applications — i.e. a file annotated by the extension .db. In general the content is stored under !dbms_dir; though unless persistence is configured (ex. Docker volumes), the data may not continue to exist once the AnyLog agent reboots.
PostgreSQL (Postgres or psql) is an open source relational database that resides separately from the AnyLog agent.

While the two can be used interchangeably, Postgres is preferred when data needs to be persistent (ex. AnyLog agents of type operator and master/metadata manager); while SQLite is a better in-memory solution for a query Agent with logical database system_query.

With that said, small devices that are able to run AnyLog but not Postgres can utilize the SQLite (not in memory) option.

Database Commands

When defining a database connection, there’s no need to pre-create the logical database outside of AnyLog. In Postgres (and other actual databases) AnyLog is able to pre-connect to the “postgres” db and automatically create the new logical database. While with SQLite, the system automatically creates a new file for the database under !dbms_dir.

Disconnecting & Dropping database(s) works the same way but from the other direction. This means that AnyLog is also intelligent enough to tell you “this db is being used” and blocks the user from actually dropping the database.

Warning, when connecting to a database that’s not file-based, the user credentials being used to connect need to have the proper read/write permissions.

Connect to Database - connect dbms associates a logical database name with a physical database. Different physical databases need different connection details, all supplied as options on the same command:

Option	Description
`[db name]`	The logical name of the database
`type`	Physical database: `sqlite`, `psql`, or `pi` (PI System/OSIsoft historian — confirm current support before relying on this; not otherwise documented in this tree)
`user` / `password`	Credentials recognized by the physical database
`ip` / `port`	Physical database’s network location
`memory`	If `true`, tables are kept in RAM rather than on disk (SQLite only — not supported by PostgreSQL)
`connection`	A raw database connection string, as an alternative to the individual fields above
`autocommit`	If `false`, groups multiple statements into a single transaction
`unlog`	If `true`, skips writing changes to the write-ahead log — faster inserts, at the cost of durability on failure

Notes:

For SQLite, the logical name can include a path to control where the data is kept; otherwise it’s placed under the default location (!dbms_dir).

unlog = true is recommended for system_query (below), since query results there are disposable — but not for your actual user data.

```anylog connect dbms [logical-name] where type = [sqlite|psql] and [options]

SQLite

connect dbms my_data where type = sqlite connect dbms my_data where type = sqlite and memory = true # in-memory only

PostgreSQL

connect dbms my_data where type = psql and user = anylog and password = demo and ip = 127.0.0.1 and port = 5432

* Verify connection
```anylog
get databases                               # list all connected databases
get tables where dbms = my_data            # list tables in a database
get columns where dbms = my_data and table = my_table   # list columns

Disconnecting from database
```
disconnect dbms [db name]
```
Drop database - note the database must be disconnected before being dropped ```anylog drop dbms [dbm name] where type = [sqlite|psql] and [options]

SQLite

drop dbms my_data where type = sqlite

PostgreSQL

drop dbms my_data where type = psql and user = anylog and password = demo and ip = 127.0.0.1 and port = 5432

> Dropping database means all its content will be gone.

## Tables

Table creation is based on the content coming into the operator node and associated mapping (if relevant).

Before defining a new logical table, the operator node first utilizes the database and table name from the file
name (ex. `[db name].[table name].0.0.json`) in order to check whether the table already exists locally, and if not,
whether it exists on the blockchain. Then, based on the blockchain (i.e. not the first time this database/table is
seen), it defines the table in the local operator node's database.

This means that while operators are separate entities, the moment they reside on the same blockchain it is not possible
for 2 operators to have the same database and table name but different schemas.

> Automation of the create process is done when specifying `create_table=true` as part of the `run operator` command.

* Create table - this function only works when the table schema exists as a `table` policy on the blockchain, or is
one of the pre-defined tables (`blockchain.ledger` and `almgm.tsd_info`) in the platform

```anylog
create table [table-name] where dbms = [dbms-name]

Drop a table - if partitioning is defined, then dropping the table will also remove the associated partitioning.

drop table [table-name] where dbms = [dbms-name]

Dropping a table locally does not remove it from the blockchain. In order to remove the table from the blockchain a user needs to remove the table policy and the associated cluster policies.

Drop table across all the operators in the network - no matter the cluster the table resides under.

Unlike the standard drop table that only removes the local copy of the table, drop network table removes the table
from operator containing the data and removes its metadata definition, in one call. Since some nodes may be offline when this runs, use test network table both before and after, to confirm which nodes actually had the table and which of those the drop actually reached.

drop network table where name = [table name] and dbms = [dbms name] and master = [master_node]

drop network table where name = ping_sensor and dbms = lsl_demo and master = 10.0.0.25:2548

Table Partitioning

Partitioning splits a large table into time-based segments, so queries only scan the relevant slice instead of the entire table, and old data can be cleaned up automatically. Partitioning is transparent to users and applications — you always interact with data using the table’s name; AnyLog handles distributing the processing across the underlying partitions itself. Any date-time column on the table can be used as the partition column, not just timestamp/insert_timestamp.

Creating Partitioning

partition [dbms-name] [table-name] using [timestamp-column] by [interval]

# partition all the tables in the database 
partition my_data * using insert_timestamp by 1 week

# partition a specific table in the database 
partition my_data ping_sensor using timestamp by 1 day

Time interval options: year, month, week, day — singular or plural, optionally with a counter:

If this database also stores aggregation output (see Aggregation Functions ), avoid the wildcard (*) form above for that database — it forces raw and aggregation tables onto the same interval and retention. Partition (and schedule cleanup for) the raw table and the aggregation table separately instead.

View partitions

get partitions
get partitions where dbms = my_data
get partitions where dbms = my_data and table = ping_sensor

Drop a partition

Drops all partitions except the newest/active one (the active partition is never dropped):

drop partition where dbms=[db name] and table=[table name] and [keep=X]

# example
drop partition where dbms = my_data and table = ping_sensor

keep (optional) tells the system how many of the newest partitions to keep — e.g. keep=3 removes everything except the last 3. Without keep, the command removes every partition except the newest one — this can drop a lot of history in one call if run without keep on a table with many partitions, so double-check before running it unqualified.

Drop specific partitioned table ```anylog drop partition [partition table name] where dbms = [db name] and table = [table name]

example

drop partition par_ping_sensor_2026_07_27_01_h12_insert_timestamp where dbms=my_data and table=ping_sensor

> The example above uses `insert_timestamp` with an hourly-style partition name, but the only partitioning example
> shown earlier in this doc for `ping_sensor` uses the `timestamp` column, partitioned daily. Worth using a name
> consistent with that (daily, on `timestamp`) unless `ping_sensor` is genuinely partitioned differently in this
> context.

* Scheduler process to clean old partitions
```anylog
schedule time = 1 day and name = "Drop old data" task drop partition where dbms = my_data and table = ping_sensor and keep=3 

Supported databases

Database Commands

SQLite

PostgreSQL

SQLite

PostgreSQL

Table Partitioning

example

node_configs.env