Browse Apps
Apps · Deploy and Share

Data Stores

Choose where an App environment keeps its data: the project's DataSets, or a SQLite database that sleeps when idle.

3 min read

Every App environment has one Store: the tables and rows shared by everyone who uses it. You choose the engine when you create the environment, and it cannot change.

EngineWhere the rows liveGood for
SQLite (default)A database in its own container. It sleeps when idle.Most Apps. The same engine as botdojo-app dev.
DataSetsThe project's DataSets. Nothing to run or size.Small Apps, and environments that must always answer instantly.

Postgres is listed but not available yet. Both engines run the same App code. To change engines, create a new environment and load the data there.

What to keep in the Store

Keep state the App owns, such as a review queue or the status of a request.

When another system owns the official record, use the Store as working space: the App holds a proposed change and its review, and a function sends the approved change to the other system.

How a SQLite Store runs

  • It sleeps when idle. After 15 minutes with no calls, by default, BotDojo copies the database to the project's files and stops the container.
  • It wakes on the next call, such as opening the App or calling a function. That call waits a few seconds while the newest copy is restored.

The Data tab shows the Store's state: Sleeping, Starting, Running, Stopping, or Error. Use Start, Stop, Back up now, and Restart there.

Backups and what a failure can lose

By default, a running Store copies the whole database to the project's files 30 seconds after the last write, and at least every 5 minutes while writes continue. It also copies before a deploy that changes tables and before it sleeps or restarts. The last 5 copies are kept.

What happensWhat is lost
The Store sleeps, restarts, or its node is drainedNothing.
The database server in the container crashesNothing that was saved.
The container's node fails while the Store is awakeWrites since the last copy: 30 seconds to 5 minutes.
The newest copy is damagedNothing more. The copy before it is restored.

Size and settings

Choose a size when you create the environment. To change it, select Settings on the Data tab.

SizeCPU unitsMemoryDisk
Small (default)256256 MB5 GB
Medium512512 MB10 GB
Large10241 GB25 GB

Under Advanced, set CPU, memory, and disk separately, the idle time before sleep, and the backup timing and number of copies. Disk must be at least two and a half times the database's size. New settings apply when the Store next wakes, or on Restart.

botdojo-app env create staging --database sqlite --size medium
botdojo-app store settings --env staging --backup-quiet 15 --backup-max-interval 120
botdojo-app store status --env staging

Joins and SQL

An App's sql call takes one read-only SELECT and can join any tables the caller's roles can read. A reference field holds the target row's ID:

SELECT p.name, sum(e.amount) AS total
FROM projects p
JOIN expenses e ON e.project = p.id
GROUP BY p.id, p.name
  • Archived rows are hidden, so an inner join to an archived row drops the joined row.
  • SQL runs in the engine's dialect: PostgreSQL on DataSets, SQLite on SQLite Stores and in botdojo-app dev. Write SQL both accept: standard joins, aggregates, CASE, COALESCE, and ->> for a key of a JSON field.
  • Avoid :: casts, ILIKE, and engine-specific date functions.

Next steps