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.
A short tour: upload a database, mount it in a Code step, update it from Python, and see what a failed run and a crossed change do.
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.The database has two tables:
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);
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.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.
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 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.
Open Cowork → Files, create a folder named Customers, and drag customers.db into it.
customers.db uploaded to the Customers folder. From now on it keeps versions like every workspace file.
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:
import sqlite3con = sqlite3.connect("files/Customers/customers.db") # creates the filecon.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.
2
Mount it in the Execute Code step
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.
Type part of a name to find a file anywhere in the workspace folder.
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.
3
Open it from Python
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:
con = sqlite3.connect("file:files/Customers/customers.db?mode=rw", uri=True)
4
Read from it
Query as usual. This step returns the ten most valuable customers in one region:
import sqlite3con = sqlite3.connect("file:files/Customers/customers.db?mode=rw", uri=True)con.row_factory = sqlite3.Rowrows = 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.
5
Write, commit and close
This is the nightly sync. It adds or updates the orders the HTTP Request step fetched, then recalculates each customer’s lifetime value:
import sqlite3orders = 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 closeoutput_data({"orders_saved": len(orders)})
The step's code opens the mounted copy at files/Customers/customers.db. The reference rates are mounted Read-only.
Commit what you want to keep. Changes you never commit are not saved, even when the run succeeds.
6
Run it and check
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.
The run saved customers.db as a new version.
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.
Each run that changes the database adds a version by the workflow, with a link to the run. Here your upload is kept as a checkpoint.
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.
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.
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.
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 run that raised an error. The database is listed as not saved, and its versions don't change.
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:
import sqlite3from datetime import datetime, timezonesynced_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()
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 with access to the folder can make one too, if you ask. Workflows can’t.
A checkpoint on the database before a large import. Nightly runs keep adding versions, and this one stays.
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 step that copies the database: CopyCustomers/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.
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.
Reference data mounted Read-only: the code reads it, and nothing is ever saved back.
Open it with ?mode=ro too, so SQLite itself refuses any write:
import sqlite3rates = 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.
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.
The run's database, saved as a conflicted copy beside the version someone saved while it ran. Its Details offer Compare with original and Keep this one.
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.
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.
Start a workflow with 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:
import sqlite3import pandas as pdcsv_path = "files/" + file_added_to_folder_1["file"]["path"]rows = pd.read_csv(csv_path, dtype=str) # every column as text: no type guessingcon = 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.
Keep a daily summary table
Run this after the sync, or on its own schedule. It adds or refreshes yesterday’s totals:
import sqlite3from datetime import date, timedeltaday = (date.today() - timedelta(days=1)).isoformat() # yesterday, as textcon = 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.
Turn a query into a report file
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 step (Write a file, Content: File from an earlier step, {{execute_code_1.files[0]}}).
import csvimport sqlite3con = 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.
import csvimport sqlite3from datetime import datecon = 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.
Work with several databases
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.
When to use a database server instead
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.
Is this a live connection to the file in Workspace files?
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.
Can two runs write to the same database at once?
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.
Can a coworker use the same database?
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.
Can I open the database on the Files page?
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.
Does Read-only lock the file?
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.
What happens to rows I delete?
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.
How big can the database get?
100 MB, the largest file a mount copies in. Archive old rows and run VACUUM to shrink it.
Can I use PostgreSQL instead?
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.
