> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cogniagent.ai/llms.txt
> Use this file to discover all available pages before exploring further.
# Use a SQLite database with Workspace files and the Code node
> Keep a SQLite database in Workspace files and read or update it from a workflow's Code node, with versions, checkpoints and safe saves.
Keep a small database in the folder your team already shares, and let a workflow read and update it with Python's built-in `sqlite3`. The Code node works on a copy of the file, and only a run that succeeds saves the database back, as a new version you can always roll back.
## What you'll build
A nightly workflow, **Nightly customer sync**, that fetches the day's orders and adds them to `Customers/customers.db` in Workspace files. It also reads discount rates from a second database that it must never change.
```mermaid theme={null}
flowchart LR
A["Scheduled Trigger
every night at 02:00"] --> B["HTTP Request
the day's orders"]
B --> C["Execute Code
files/Customers/customers.db (Read and write)
files/Reference/rates.db (Read-only)"]
C --> D["Workspace files
a new version of customers.db"]
```
The database has two tables:
```sql theme={null}
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT,
region TEXT, plan TEXT, lifetime_value REAL DEFAULT 0);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(id),
placed_at TEXT, total REAL);
```
## How it works
You **mount** a file or folder from Workspace files on an **Execute Code** step (the Code node). Before your code runs, the step copies it into a folder named `files` next to your code. Your code opens the copy like any local file. When the code ends, the step decides what to save.
```mermaid theme={null}
sequenceDiagram
participant W as Workspace files
participant S as Execute Code step
participant P as Your code
S->>W: Find each mount and check its size
W->>S: Copy each file in, at its current version
S->>P: Run, with the copy at files/Customers/customers.db
P->>P: Read, insert, update, commit, close
alt the code succeeded
S->>S: A fresh process compares every file with the copy it took
S->>W: Save each changed file as a new version, by this workflow
S->>W: Create files your code added under a mounted folder
else the code failed, or wrote to the error stream
S->>S: Discard every change
end
```
Four things follow from this:
* **It's a copy, not a live connection.** Your code changes a copy. Workspace files sees the result only after the run.
* **Only a successful run saves.** If the code raises an error, or writes anything to the error stream (a warning included), nothing is saved.
* **It never overwrites someone else's change.** If the file was saved in Workspace files while your code ran, the run's database is saved beside it as a copy instead.
* **It never deletes.** A file your code deletes stays in Workspace files.
## Before you start
* **Workspace files on your workspace.** In the app, **Cowork → Files** shows the **Workspace** place. In the Execute Code step, a **Workspace files** section sits under the code. If there is no such section, Workspace files is not on for your workspace.
* **A workflow you can edit**, with an [Execute Code](/nodes/actions/execute-code) step in it.
* **A `.db` file up to 100 MB**, or no database yet: you can create one from code (Step 1).
* **Some Python.** `sqlite3` is part of the standard library, so there is nothing to install.
The Files page accepts uploads up to 5 GB, but a mount refuses any file over 100 MB. Keep the database under 100 MB, or split it.
## Build it
Open **Cowork → Files**, create a folder named `Customers`, and drag `customers.db` into it.
**No database yet?** Create it from code once. Mount the empty `Customers` folder (next step) with **Read and write**, run this code, and the new file is saved to the folder:
```python theme={null}
import sqlite3
con = sqlite3.connect("files/Customers/customers.db") # creates the file
con.executescript("""
CREATE TABLE IF NOT EXISTS customers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT,
region TEXT,
plan TEXT,
lifetime_value REAL DEFAULT 0
);
CREATE TABLE IF NOT EXISTS orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER REFERENCES customers(id),
placed_at TEXT,
total REAL
);
""")
con.close()
output_data({"created": "Customers/customers.db"})
```
Then change the mount to the file itself, `Customers/customers.db`, as the next step shows.
In the step's **Workspace files** section, click **Add files or folder**. A new row appears. Click the folder button beside **File or folder**, find `customers.db` (type part of its name), and pick it. Then set the menu beside it to **Read and write**.
A new row starts as **Read-only**. Read-only mounts are never saved back, so remember to switch the database to **Read and write**.
Mount the database file itself, not its folder. Nothing beside a single-file mount is ever saved, so a stray file your code makes by mistake never reaches Workspace files, and the run copies only what it needs.
The copy is at `files/` plus the path, spelled exactly as Workspace files stores it: `files/Customers/customers.db`. Names in Workspace files match whatever the case, but the code's folder does not, so `files/customers/customers.db` would not open. Pick the file with the folder button and copy the path it shows.
Open it with `?mode=rw`, so a wrong path fails at once instead of quietly creating an empty database:
```python theme={null}
con = sqlite3.connect("file:files/Customers/customers.db?mode=rw", uri=True)
```
Query as usual. This step returns the ten most valuable customers in one region:
```python theme={null}
import sqlite3
con = sqlite3.connect("file:files/Customers/customers.db?mode=rw", uri=True)
con.row_factory = sqlite3.Row
rows = con.execute(
"SELECT name, email, lifetime_value FROM customers "
"WHERE region = ? ORDER BY lifetime_value DESC LIMIT 10",
("EMEA",),
).fetchall()
con.close()
output_data({"top_customers": [dict(row) for row in rows]})
```
Reading never changes the file, so a run that only reads adds no version.
This is the nightly sync. It adds or updates the orders the HTTP Request step fetched, then recalculates each customer's lifetime value:
```python theme={null}
import sqlite3
orders = http_request_1["data"]["orders"]
rows = [
(o["id"], o["customer_id"], o["placed_at"], o["total"])
for o in orders
]
con = sqlite3.connect("file:files/Customers/customers.db?mode=rw", uri=True)
try:
with con: # commits if the block succeeds, rolls back if it raises
con.executemany(
"INSERT INTO orders (id, customer_id, placed_at, total) "
"VALUES (?, ?, ?, ?) "
"ON CONFLICT(id) DO UPDATE SET total = excluded.total",
rows,
)
con.execute(
"UPDATE customers SET lifetime_value = ("
" SELECT COALESCE(SUM(total), 0) FROM orders"
" WHERE orders.customer_id = customers.id)"
)
finally:
con.close() # `with con` commits, but it does not close
output_data({"orders_saved": len(orders)})
```
Commit what you want to keep. Changes you never commit are not saved, even when the run succeeds.
Run the workflow, open the run, and click the Execute Code step. Its output has a `workspaceFiles` part. Under `written`, the database shows `outcome: "versioned"`: a new version was saved.
On the Files page, open the database's **Versions…**. The newest version is by your workflow, with the note “Written by the Code node”. In the file's Activity, **Open the run** takes you back to the run.
`outcome` can also read `created` (a new file under a mounted folder) or `coalesced`: two saves of the same file by one run within 10 minutes, for example by two Execute Code steps or by a Loop that runs the step again, join one version.
## What gets saved, and when
| The code… | Read-only mount | Read and write mount |
| - | - | - |
| succeeds and changed a file | discarded | saved as a new version, by the workflow, with the note “Written by the Code node” |
| succeeds and left a file as it was | nothing to save | nothing saved, no new version |
| succeeds and added a file | discarded | created, when it is inside a mounted **folder**; not saved beside a mounted file |
| succeeds and deleted a file | discarded | the file stays in Workspace files; it is listed in `deletedInSandbox` |
| fails, or writes anything to the error stream | discarded | nothing saved; each read-and-write mount is listed in `skipped` |
A few details:
* **Open connections.** Before the step looks for changes, it shuts down your code's Python process, which closes any connection you left open. Committed changes are saved whole, also in WAL mode. A transaction you never committed is not saved. The `-wal`, `-shm` and `-journal` files SQLite keeps beside the database are never saved.
* **Files at the top level.** A file your code writes under a plain name, like `report.csv`, is not a workspace file. It becomes the step's `files` output, as in [Creating files](/nodes/actions/execute-code#creating-files).
* **How much one run saves.** A file over 100 MB is never saved back, and that includes the database itself. New files are also capped at 2,000 files and 256 MB per run. A file past either limit is listed in `skipped`, and the step still succeeds.
* **When the workspace is full.** If saving a file back fails (the workspace's storage is full, most often), the step fails with “The code succeeded, but saving "…" back to Workspace files failed”. Files saved before it stay saved.
## When the code fails
A run that raises an error saves nothing, so a half-finished run never replaces good data. The step's output lists each read-and-write mount in `skipped` with the reason.
**A warning counts as a failure.** Anything written to the error stream fails the step, so nothing is saved. Python 3.12 warns when you pass a `date` or `datetime` straight to `sqlite3`. Store dates as ISO text instead:
```python theme={null}
import sqlite3
from datetime import datetime, timezone
synced_at = datetime.now(timezone.utc).isoformat(timespec="seconds")
con = sqlite3.connect("file:files/Customers/customers.db?mode=rw", uri=True)
with con:
con.execute("CREATE TABLE IF NOT EXISTS sync_log (synced_at TEXT, orders INTEGER)")
con.execute("INSERT INTO sync_log VALUES (?, ?)", (synced_at, 3))
con.close()
```
## Versions and checkpoints
Every run that changes the database adds a version, by the workflow. A file keeps its last three versions, and a workflow never pushes out a version a person made. Your upload is still removed from the history 30 days after a run replaces it. Make it a checkpoint to keep it for good.
To keep a version for good, make a **checkpoint** before a risky change. On the Files page, open the database's menu and choose **Create checkpoint…**, or open **Versions…** and name an older version. Checkpoints sit outside the three rolling versions, and no later save pushes them out. A [coworker](/cowork/files/sharing-and-access) with access to the folder can make one too, if you ask. Workflows can't.
To roll back, open **Versions…**, pick the version you want, and click **Restore**. It becomes the newest version. You can also **Download** any version and open it with a SQLite tool on your computer.
For a longer trail, add a [Workspace Files](/nodes/actions/workspace-files) step that copies the database: **Copy** `Customers/customers.db` to the folder `Backups`, with **If the name is taken** set to **Keep both**. The first copy is `customers.db`, and later ones are named `customers (copy).db`, `customers (copy 2).db` and so on. Every copy counts toward your workspace's storage. Run it on its own, less frequent schedule and clear out old copies.
## Read-only reference data
Mount data your code must never change as **Read-only**. Read-only doesn't lock the copy: your code can still change it, but nothing goes back.
Open it with `?mode=ro` too, so SQLite itself refuses any write:
```python theme={null}
import sqlite3
rates = sqlite3.connect("file:files/Reference/rates.db?mode=ro", uri=True)
discounts = {
(region, tier): discount
for region, tier, discount in rates.execute(
"SELECT region, tier, discount FROM rates"
)
}
rates.close()
con = sqlite3.connect("file:files/Customers/customers.db?mode=rw", uri=True)
rows = con.execute("SELECT name, region, plan FROM customers ORDER BY name").fetchall()
con.close()
output_data({
"discounts": [
{"customer": name, "discount": discounts.get((region, plan), 0)}
for name, region, plan in rows
]
})
```
Keep `?mode=ro` for Read-only mounts. On a database in WAL mode, even a read-only connection leaves `-wal` and `-shm` files beside it. In a Read-only mount they are discarded; inside a read-and-write folder they would be saved as new files.
## When two changes cross: conflicted copies
The step copies the database in at the start of the run. If someone saves a new version of it before the run ends (a person, a coworker, or another run), the run never overwrites it. The run's database is saved beside it as `customers (conflicted copy).db` and listed in the output's `conflicts`. A second one would be `customers (conflicted copy 2).db`.
Decide which one to keep:
* **Keep the original:** move the conflicted copy to the Trash.
* **Keep the run's version:** open the conflicted copy and choose **Keep this one**. Its bytes become the original's new version, and the copy goes to the Trash.
A database can't be compared as text: **Compare with original** offers both files as downloads, so check them with a SQLite tool. To avoid conflicts, don't let two runs that change the same database overlap. The one that finishes second is saved as the conflicted copy.
## Journal and WAL files
Leave the journal mode alone. The default mode works, and so does WAL; once you set WAL, it stays set in the saved file. In both, the step saves the database and never the `-wal`, `-shm` or `-journal` files beside it.
Don't use `journal_mode=PERSIST` or `TRUNCATE` on a database inside a mounted read-and-write **folder**. Both leave a `-journal` file behind after the connection closes, and a new file in a mounted folder is saved. With a single-file mount, as this guide uses, nothing beside the database is saved anyway.
## Limits
| | Limit |
| - | - |
| Mounts per Execute Code step | 10 |
| Copied in per run, all mounts together | 2,000 files, 256 MB, 100 MB per file |
| Saved back per run | 100 MB per file, the database included; new files also 2,000 files and 256 MB in all |
| Largest database you can mount | 100 MB (the Files page itself takes uploads up to 5 GB) |
| Run time of one step | 30 minutes |
| Memory | About 2 GB |
| Workspace storage | 20 GB. Checkpoints count toward it; other older versions and the Trash don't |
| Versions per file | The last 3, plus up to 3 checkpoints |
Mounts that pass a limit stop the step before your code runs, so a run never works on half a folder.
## Patterns
Start a workflow with [File Added to Folder](/nodes/triggers/file-added-to-folder) on the folder `Imports`, with **Only names like** `*.csv` and **One run per file**. In the Execute Code step, mount two things:
* `{{file_added_to_folder_1.file.path}}`, **Read-only**: the file that arrived (a mount path may use `{{…}}`)
* `Customers/customers.db`, **Read and write**
Load the CSV into a staging table with pandas, then copy it across in one statement:
```python theme={null}
import sqlite3
import pandas as pd
csv_path = "files/" + file_added_to_folder_1["file"]["path"]
rows = pd.read_csv(csv_path, dtype=str) # every column as text: no type guessing
con = sqlite3.connect("file:files/Customers/customers.db?mode=rw", uri=True)
try:
rows.to_sql("staging_orders", con, if_exists="replace", index=False)
with con:
con.execute(
"INSERT INTO orders (id, customer_id, placed_at, total) "
"SELECT CAST(id AS INTEGER), CAST(customer_id AS INTEGER), "
"placed_at, CAST(total AS REAL) "
"FROM staging_orders WHERE true "
"ON CONFLICT(id) DO UPDATE SET total = excluded.total"
)
con.execute("DROP TABLE staging_orders")
finally:
con.close()
output_data({"imported": len(rows)})
```
Keep `WHERE true`: without a `WHERE`, SQLite can't tell where the `SELECT` ends and `ON CONFLICT` begins, and refuses the statement. Read the CSV with `dtype=str`, too: on a large file with mixed columns, pandas can warn about the types it guessed, and a warning fails the step.
Run this after the sync, or on its own schedule. It adds or refreshes yesterday's totals:
```python theme={null}
import sqlite3
from datetime import date, timedelta
day = (date.today() - timedelta(days=1)).isoformat() # yesterday, as text
con = sqlite3.connect("file:files/Customers/customers.db?mode=rw", uri=True)
try:
with con:
con.execute(
"CREATE TABLE IF NOT EXISTS daily_totals "
"(day TEXT PRIMARY KEY, orders INTEGER, revenue REAL)"
)
con.execute(
"INSERT INTO daily_totals (day, orders, revenue) "
"SELECT ?, COUNT(*), COALESCE(SUM(total), 0) FROM orders "
"WHERE substr(placed_at, 1, 10) = ? "
"ON CONFLICT(day) DO UPDATE SET "
"orders = excluded.orders, revenue = excluded.revenue",
(day, day),
)
finally:
con.close()
```
Before a month-end change, make a checkpoint by hand on the Files page.
**To send it on:** write the file under a plain name. It becomes the step's `files` output, which a later step can attach to an email, or save with a [Workspace Files](/nodes/actions/workspace-files) step (**Write a file**, **Content: File from an earlier step**, `{{execute_code_1.files[0]}}`).
```python theme={null}
import csv
import sqlite3
con = sqlite3.connect("file:files/Customers/customers.db?mode=rw", uri=True)
rows = con.execute(
"SELECT c.name, o.id, o.placed_at, o.total FROM orders o "
"JOIN customers c ON c.id = o.customer_id ORDER BY o.placed_at"
).fetchall()
con.close()
with open("orders-report.csv", "w", newline="") as f: # a plain name: the files output
writer = csv.writer(f)
writer.writerow(["customer", "order", "placed_at", "total"])
writer.writerows(rows)
output_data({"rows": len(rows)})
```
**To keep it in Workspace files directly:** mount a small folder, such as `Reports/Exports`, with **Read and write**, and write the file there. A new file in a mounted folder is created in Workspace files.
```python theme={null}
import csv
import sqlite3
from datetime import date
con = sqlite3.connect("file:files/Customers/customers.db?mode=rw", uri=True)
rows = con.execute(
"SELECT c.name, o.id, o.placed_at, o.total FROM orders o "
"JOIN customers c ON c.id = o.customer_id ORDER BY o.placed_at"
).fetchall()
con.close()
path = f"files/Reports/Exports/orders-{date.today().isoformat()}.csv"
with open(path, "w", newline="") as f:
writer = csv.writer(f)
writer.writerow(["customer", "order", "placed_at", "total"])
writer.writerows(rows)
```
Every run copies the whole mounted folder in first, so mount a folder that stays small. Mounting all of `Reports` would copy every report each run, and stop the step once it passes 2,000 files or 256 MB.
Mount up to 10 files or folders on one step, each with its own access. Never mount a path inside another mounted path: the step refuses overlapping mounts. Mount the parent folder once instead, or each file on its own.
A SQLite file suits data one workflow updates at a time. If many runs, people or coworkers write at once, conflicted copies pile up. Use a hosted PostgreSQL or MySQL database instead: `psycopg2`, `pymysql` and `sqlalchemy` are installed, and the code can reach the public internet.
## Troubleshooting
| What you see | Why | What to do |
| - | - | - |
| “The workspace mount "…" was not found in this workspace.” | The path is misspelled, the file was moved, or it is in the Trash | Pick the file with the folder button, or restore it from the Trash |
| “Cannot mount "…": the mounts would copy more than …” | The mounts together pass 2,000 files or 256 MB | Mount a smaller folder, or the file itself |
| “Cannot mount "…": "…" is … MB, over the limit per file.” | A mounted file, often the database, is over 100 MB | Download it, archive old rows and run `VACUUM`, then upload it as a new version. Or split the database |
| “The workspace mounts "…" and "…" overlap: one is inside the other.” | One mount is inside another | Mount the parent once, or each file on its own |
| `sqlite3.OperationalError: unable to open database file` | The path's spelling differs from Workspace files (case counts here), or the file isn't mounted | Use the path exactly as the picker shows it, after `files/` |
| `no such table` | A misspelled file name made a new, empty database | Open with `?mode=rw` (Step 3) so a wrong path fails instead |
| The step failed, but the output looks right | Something wrote to the error stream, often a warning | Fix the warning, or add `warnings.filterwarnings("ignore")` |
| A `DeprecationWarning` about the default date or datetime adapter | A `date` or `datetime` was passed to `sqlite3` | Store dates as ISO text with `.isoformat()` |
| The step succeeded, but nothing changed in Workspace files | The mount is Read-only, the code didn't commit, or the new file was outside a mounted folder | Check `workspaceFiles.written` and `skipped` in the step's output |
| “Not saved: larger than the 100 MB limit per file.” | The database grew past 100 MB | Archive old rows and run `VACUUM`, or split the database |
| A `(conflicted copy)` file appeared | Someone, or another run, saved the database while the run worked on it | See [When two changes cross](#when-two-changes-cross-conflicted-copies) |
| “The code succeeded, but saving "…" back to Workspace files failed: …” | The workspace's storage is full, or saving failed for good | Free some space. Files saved before it stay saved |
| “Could not check the read-write mounts for changes: …” | The check after the code could not finish, so nothing was saved | Run it again; if it keeps failing, contact support |
| “Workspace files aren't available here, so the code fails to start while files are mounted.” | Workspace files is off for this workspace | Remove the mounts |
## Common questions
No. The step copies the database in before your code runs, and saves it back after a successful run. While the code runs, Workspace files still holds the version from before the run.
They can, but nothing is merged. The run that finishes first saves a new version; the second is saved beside it as a conflicted copy. Don't let runs that change the same database overlap.
Yes. A coworker that can reach Workspace files works in the same folder, and its saves keep history too. Since both write whole files, avoid working on the database at the same moment.
The Files page doesn't show a database's tables, and double-clicking it downloads it. Open it with a SQLite tool on your computer, or query it from a workflow.
No. Read-only means nothing is saved back. Your code can still change its copy, which is discarded. Open the database with `?mode=ro` to make SQLite refuse writes too.
Deleting rows changes the database, so a successful run saves it as a new version. Deleting the database file itself never removes it from Workspace files.
100 MB, the largest file a mount copies in. Archive old rows and run `VACUUM` to shrink it.
Yes. For data many writers change at once, use a hosted PostgreSQL or MySQL database with `psycopg2` or `pymysql`, both installed in the Execute Code step.
## Related
Everything the Python step can do, including its Workspace files section.
The folder your team, coworkers and workflows share.
Versions, checkpoints, restore and conflicted copies.
Start a workflow when a file arrives in a folder.
This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.