Skip to main content
Site SQL is an optional database for one published Site, next to Site Data. Each Site gets its own SQLite database (a Cloudflare Durable Object). It is available where the deployment turns on Cloudflare storage, and the Site’s owner turns it on per Site. Use Site Data for records and files with ready-made access modes. Use Site SQL when a Site needs tables, joins, aggregates or unique keys.

​
Set it up with an agent

Ask Opengeni, or use the tools directly:
  1. site_sql_enable turns the Site’s database on. The owner can also use the SQL database switch on the Site’s Data tab, which shows the database size and its number of queries; turning it off keeps the data.
  2. site_sql_migrate adds a named schema migration, such as create_votes with CREATE TABLE votes (...). Migrations are kept in order and replayed if the database is ever rebuilt.
  3. site_sql_define_query defines a named query the Site may run, with an audience:
:viewer is filled in by Opengeni with the signed-in viewer (or the guest’s visitor id), so one person cannot vote as another. :viewer_kind and :viewer_signed_in work the same way.

​
In Site code

Sites never send SQL; they can only run the named queries an owner defined. Errors are OpenGeniSiteSqlError with a code such as forbidden, not_found, validation, rate_limited, quota_exceeded or disabled. Rows come from other people: show them as text, never as HTML.

​
Managing data

site_sql_inspect shows tables, row counts, migrations, queries, usage and limits. site_sql_query and site_sql_execute run one statement for the owner, and site_sql_run runs a named query as a page would. An agent started from a Site page can only run that Site’s named queries its page could run; every other Site SQL tool refuses it. site_sql_export downloads the database as SQL, a table as CSV, or everything as JSON, even while the database is turned off. Large exports come in pieces: pass the returned nextCursor back as cursor and keep the pieces in order. SQL keeps every value exactly; CSV turns values into text. site_sql_import loads SQL, CSV or JSON (up to 2 MB per call; import the pieces of a large SQL export in order). A SQL import adds its tables first, then the rows, then any triggers, so triggers do not fire on imported rows. site_sql_reset empties the database and rebuilds the tables, or deletes everything. Deleting a workspace also deletes its Sites’ databases. Statements are checked: no ATTACH, no extensions, no VACUUM, no PRAGMA writes, and one statement per named query.

​
Limits and usage

On the free profile a Site’s database holds up to 50 MB, and the Site’s pages read 5 million and write 100,000 rows a month. Each viewer can make 60 calls a minute and the Site 600, and one call returns at most 1,000 rows and 1 MB. Guests’ writes also share the Site’s guest write limit with Site Data. The credits profile raises these. The owner’s own work (exports, imports, migrations and statements run with the tools) does not use up the monthly rows. A Site’s migrations hold at most 6 MB of SQL; load data with imports instead. Self-hosted deployments change the limits with the OPENGENI_SITES_SQL_* settings. Rows read, rows written and storage are recorded for the workspace like Site Data; they are not charged yet. See Site SQL internals.