Databases

A database keeps your app's data in tables, using SQL (the SQLite kind). Add as many as you need; each has its own name, and your code reaches it as env.NAME. The first one is called DB unless you name it.

Good for: users and accounts, orders, settings. Billed: per row read and written, plus how much it holds.

From your app's codeCopy Anchor Link

No connection string and no password: the database is just there.

const { results } = await env.DB
  .prepare('select * from orders where customer = ?')
  .bind(email)
  .all()

await env.DB.prepare('insert into orders (item, qty) values (?, ?)').bind('Sourdough', 2).run()

From the dashboardCopy Anchor Link

Open the database to see its Data: your tables and views in a list (with how many rows each, and a box to find one by name), and the picked table's rows like a spreadsheet.

• Search looks in the text columns. Pick a column under In to search just that one; numbers in a number column match exactly. Tables with more than 10,000 rows search when you press Enter.
• Click a row to see every field, edit it, or delete it. Add row fills in defaults and the next id for anything you leave empty.
• Columns shows what each column holds in plain words, whether it must be filled in, and which tables it points to.
• Views (saved queries over other tables) can be read, searched and sorted, but not changed.
• Binary data (files kept in a column) shows its size. Open the row to download it, replace it with a file up to 900 KB, or clear it.

For anything else, choose Run SQL. Type SQL and press Run (or Ctrl/⌘ + Enter). You can run several statements at once.

From anywhere elseCopy Anchor Link

For migrations, seeding and scripts on your own machines:

const db = ocp.app(appId).database('DB')
await db.query('create table if not exists orders (id integer primary key, item text, qty integer)')
const rows = await db.rows('select * from orders where id = ?', [1])
// Refused before it runs if it would change anything (and allowed for read-only keys):
await db.query('select count(*) from orders', [], { readOnly: true })

Each statement returns up to 1,000 rows. SQL that only reads counts as reading, so read-only keys can run it. See Data for the details.

Agents read with the query_database tool, which never changes anything, and change data with execute_sql, which asks the person first.

Take a copyCopy Anchor Link

Download a copy (on the Data view, or the database's Settings) saves the whole database as a .sql file: every table's definition and rows, then its indexes, triggers and views. It's made with ordinary queries a page at a time, so your app keeps working while it runs, and text, numbers, empty values and binary data come back exactly. Use it for backups, or to take your data elsewhere: sqlite3 new.db < copy.sql loads it into SQLite.

Load a SQL file (Settings) runs a .sql file against the database, up to 10 MB: a copy you downloaded, or any SQL. Load a copy into an empty database: rows already there stay, so tables that exist already make it stop. If a statement fails it stops there and says how far it got; what ran before stays.

# A copy, as a file (read-only keys may take one).
curl https://opencloudport.com/api/v1/apps/$APP_ID/services/$DB_ID/export -H "Authorization: Bearer $OCP_KEY" -o copy.sql

# A link that downloads a fresh copy without a key, for 15 minutes.
curl https://opencloudport.com/api/v1/apps/$APP_ID/services/$DB_ID/export-link -H "Authorization: Bearer $OCP_KEY"

# Load a file into a database.
curl -X POST https://opencloudport.com/api/v1/apps/$APP_ID/services/$DB_ID/import \
  -H "Authorization: Bearer $OCP_KEY" -H "content-type: application/sql" --data-binary @copy.sql

With the SDK: db.export() (the file as text), db.exportUrl() and db.import(sql). Agents have export_database, which hands back the download link, not the file.

NamesCopy Anchor Link

Names are letters, digits and underscores, starting with a letter or underscore: DB, ORDERS, CUSTOMERS. A name can't be used twice in one app, including by a variable. An app can have up to 20 named services (databases, buckets and email together).