Familiar query patterns
Build application screens and reports with filtering, sorting, joins and aggregates. Bind user input through positional parameters.
Explore the SQL reference All products Your application data.
SQL
Your application data.
Ready to query.
Filter, join and aggregate records in the same database as your vectors, relationships and search indexes.
From question to result.
Use SQL for the questions behind your application: which orders need attention, what customers bought and how activity changes over time.
01 Related tables
| id | region |
|---|---|
| c-01 | IN |
| c-02 | EU |
| Customer | Cents | Status |
|---|---|---|
| c-01 | 2,400 | paid |
| c-02 | 3,600 | paid |
| c-01 | 1,200 | paid |
| c-02 | 900 | pending |
02 One query
- JOINMatch customers
customer_id = id - WHEREKeep paid orders
status = 'paid' - GROUP BYTotal by region
SUM(total_cents)
03 Application result
Paid revenueBy customer region
| Region | Revenue |
|---|---|
| IN | 3,600 |
| EU | 3,600 |
Pending orders stay out of the total.
Define your tables
Declare fields, primary keys and indexes. Write records through the SQL or row API, keeping related tables in the namespaces your application uses.
Run and inspect
Send a SQL statement, read the returned rows and use EXPLAIN to check how the engine scans, filters and joins your data.
OriginChainDB implements a documented SQL subset. Supported statements differ between the HTTP API and PostgreSQL wire access.
Try a join In your application A lookup.
A lookup.
A join.
A clearer picture.
Start with the records behind a screen. Join related tables when the question crosses them, then aggregate when you need a summary.
The revenue query uses the sample tables above. It matches each paid order to a customer and adds the amounts within each region.
Read supported SQL syntaxQuery patternsSQL
01Join and aggregate
Paid revenue by region
SELECT c.region,
SUM(o.total_cents)
AS revenue_cents
FROM shop.orders o
JOIN shop.customers c
ON o.customer_id = c.id
WHERE o.status = 'paid'
GROUP BY c.region;02Find the records you need
The latest paid orders
SELECT id, customer_id,
total_cents
FROM shop.orders
WHERE status = 'paid'
ORDER BY placed_ms DESC
LIMIT 20;03Bind application input
Pass a customer ID as $1
SELECT id, total_cents
FROM shop.orders
WHERE customer_id = $1
LIMIT 20;Send the value for $1 in the request's params array.
Understand the work
Inspect before you tune.
A query plan shows how an answer is assembled. Use it to make a deliberate change, then check what that change achieved.
- 01
EXPLAINRead the plan
Check whether the engine scans the table or uses an index. Follow the filters, joins and aggregation stages.
- 02
WHERE + indexNarrow the input
Filter on an indexed field where possible. Return the columns you need and add a limit to exploratory queries.
- 03
EXPLAIN ANALYZEMeasure the execution
Run the query with execution details, compare the work performed and keep changes that help your workload.
Keep the whole query in view. Sorting can process the full input before a limit applies. Narrow that input first when working with large tables.
Explore query plansWhy SQL on OriginChainDB?
Records behind every result
Use shared record IDs to retrieve the business fields behind a vector match, graph connection or full-text search hit.
See the data modelsExecution you can inspect
Review query plans and index use before tuning a query, with EXPLAIN available through the same SQL endpoint.
Inspect a queryPut it to work.
Find the right SQL pattern
Check supported statements, response formats and query limits before adding a new query to your application.
SQL referenceReview PostgreSQL behavior
Check current transaction behavior, error handling and known differences on dedicated deployments.
PostgreSQL compatibilityConnect a database client
Use an enabled PostgreSQL wire endpoint. Check its preview scope and the read-only rules for database-user sessions.
Client connection guide