Data Stores
Choose where an App environment keeps its data: the project's DataSets, or a SQLite database that sleeps when idle.
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.
| Engine | Where the rows live | Good for |
|---|---|---|
| SQLite (default) | A database in its own container. It sleeps when idle. | Most Apps. The same engine as botdojo-app dev. |
| DataSets | The 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 happens | What is lost |
|---|---|
| The Store sleeps, restarts, or its node is drained | Nothing. |
| The database server in the container crashes | Nothing that was saved. |
| The container's node fails while the Store is awake | Writes since the last copy: 30 seconds to 5 minutes. |
| The newest copy is damaged | Nothing 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.
| Size | CPU units | Memory | Disk |
|---|---|---|---|
| Small (default) | 256 | 256 MB | 5 GB |
| Medium | 512 | 512 MB | 10 GB |
| Large | 1024 | 1 GB | 25 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.