Database

MicroB2B keeps its configuration, messages, history and users in a database. This page helps you choose between SQLite and PostgreSQL and explains their settings.

The contents of the messages are not in the database. They are in the payload storage.

Choosing a database

	SQLite	PostgreSQL
What it is	One file on the MicroB2B server	A separate database server
Setup	Nothing to install	You run or rent a PostgreSQL server
Good for	One MicroB2B server. SQLite uses one connection	A company that already runs PostgreSQL, or a database on its own server
Backup	Copy the file with `sqlite3`	`pg_dump`

SQLite is the default and needs no setup. Choose PostgreSQL if you already manage PostgreSQL servers and their backups, or want the database on a server of its own.

Settings

Setting	What it does	Default
`DB_TYPE`	`sqlite3` or `postgres`	`sqlite3`
`DB_URL`	Where the database is	`file:microb2b.db`

Examples of DB_URL:

`DB_TYPE`	`DB_URL`
`sqlite3`	`file:/var/lib/microb2b/microb2b.db`
`postgres`	`postgres://user:password@host:5432/db?sslmode=require`

A relative SQLite path, such as the default file:microb2b.db, is relative to the working directory. Use a full path to be sure where the file is.

SQLite

MicroB2B opens SQLite in WAL mode. That lets another program, such as the sqlite3 shell or a backup script, read the database while MicroB2B writes to it.

Use a local disk. Some network file systems cannot do WAL mode. MicroB2B then refuses to start.
Keep the whole directory. SQLite keeps -wal and -shm files next to the database file. A container must mount the directory, not the file.
MicroB2B sets its own SQLite options. An option you put in DB_URL yourself wins over MicroB2B’s value for that option.

PostgreSQL

Create an empty database and a user who owns it. Put both in DB_URL. Use sslmode=require or stricter when the database is on another machine.

MicroB2B keeps a pool of connections to PostgreSQL. You can tune it:

Setting	What it does	Default
`DB_MAX_OPEN_CONNS`	Most connections open at once. `0` means no limit	`25`
`DB_MAX_IDLE_CONNS`	Idle connections kept open. Must not be above `DB_MAX_OPEN_CONNS`	`10`
`DB_CONN_MAX_LIFETIME`	Close a connection after this age, such as `30m`. `0` means never	`30m`
`DB_CONN_MAX_IDLE_TIME`	Close a connection that was idle this long. `0` means never	`5m`

Keep DB_MAX_OPEN_CONNS below the max_connections of your PostgreSQL server. MicroB2B logs the values it uses as database pool configured.

SQLite ignores these settings and logs a note if you set them. A value that is not valid stops MicroB2B, with an error that names the variable.

Tables and upgrades

MicroB2B creates the tables it needs when it starts. It never changes a table that already exists. A release that changes the tables needs an empty database. See upgrading.

What a new database contains

The first time MicroB2B starts with an empty database, it sets up a working configuration. It does this once. If you delete something later, it stays deleted.

Your organization, named local. It has no AS2 profile yet.
A receiver as2_receiver_default and a sender as2_sender_default.
A file poller file_poller_default. It picks up files from data/outbox, where {to}/{filename} names the partner by folder. Processed files go to data/processed.
A file writer file_writer_default. It writes to data/inbox, as {from}/{original_filename}.
Two routes, from any party to any party: the receiver to the file writer, and the file poller to the sender.

The data/... directories are relative to the working directory of MicroB2B. Creating a partner does not create a folder. The file poller creates its directory when it starts, and the file writer creates folders when it writes.

See routes, file poller and file writer to change this setup.

Required settings Reverse proxy