Run your first SQL query

Before you start

An instance and an API key. Everything below is one endpoint:

export OC_URL='https://<your-instance>'
export OC_TENANT='<your-tenant>'
export OC_TOKEN='<your-api-key>'

The five steps

1. 1.
   Register a schema

   A schema declares a table and its columns. Register it once; every surface reads the same declaration. Full reference in the schema reference.

   curl -X POST "$OC_URL/v1/tenants/$OC_TENANT/schemas" \
     -H "Authorization: Bearer $OC_TOKEN" \
     -H "Content-Type: text/plain" \
     --data-binary @- <<'TOML'
   namespace = "shop"
   table = "products"
   primary_key = ["id"]
   
   [[columns]]
   name = "id"
   ty = "str"
   required = true
   
   [[columns]]
   name = "name"
   ty = "str"
   
   [[columns]]
   name = "price"
   ty = "i64"
   TOML

2. 2.
   Insert a row

   The row endpoint is the fast path for writes and the one to use for ingest. INSERT over SQL works too — see step five.

   curl -X POST "$OC_URL/v1/tenants/$OC_TENANT/rows/shop.products" \
     -H "Authorization: Bearer $OC_TOKEN" \
     -H "Content-Type: application/json" \
     -d '{"id":"sku-8842","name":"Carbon Marathon","price":149}'

3. 3.
   Select it back

   One endpoint, one JSON field. The response carries a kind so your code can switch on the statement type.

   curl -X POST "$OC_URL/v1/tenants/$OC_TENANT/sql" \
     -H "Authorization: Bearer $OC_TOKEN" \
     -H "Content-Type: application/json" \
     -d '{"sql":"SELECT id, name, price FROM shop.products WHERE price < 200"}'

4. 4.
   Group and aggregate

   Aggregates, GROUP BY and HAVING run in the engine, not in your application.

   SELECT brand, COUNT(*) AS n, AVG(price) AS avg_price
   FROM shop.products
   GROUP BY brand
   HAVING COUNT(*) > 2
   ORDER BY avg_price DESC

5. 5.
   Join two tables

   Joins resolve across schemas in the same tenant. Writes over SQL (INSERT, UPDATE) execute against the engine as well; for bulk ingest the row endpoints are still faster.

   SELECT o.id, p.name, o.qty
   FROM shop.orders o
   JOIN shop.products p ON o.product_id = p.id
   WHERE o.status = 'paid'

From a PostgreSQL client

The same data is reachable over the PostgreSQL wire protocol, so psql, DBeaver, pgAdmin and standard drivers connect directly. See connect a SQL client for the connection string and the compatibility matrix.

What to read next