SQL
Prepare a table for SQL
Choose fields, add useful indexes and read the returned values correctly.
A SQL query can use the table and columns you have registered.
- Write CREATE TABLEChoose the fields, types and primary key.
- Run the definitionInspect the response before inserting data.
- Add a small sampleUse values that satisfy the declared types and rules.
- Read and inspect the tableVerify the stored fields before adding more queries or indexes.
Prepare the table
- Define the table's primary key and columns.
- Register the schema before writing rows.
- Add indexes for the filters your application actually uses.
- Use
EXPLAINto check whether a query uses an index or scans the table.
The example below uses its own shop.orders table; it is separate from the quickstart's quickstart.products table.
Example orders schema
namespace = "shop"
table = "orders"
primary_key = ["id"]
[[columns]]
name = "id"
ty = "str"
required = true
[[columns]]
name = "customer"
ty = "str"
[[columns]]
name = "amount_cents"
ty = "i64"
[[columns]]
name = "status"
ty = "str"
[[columns]]
name = "notes"
ty = "str"
[[columns]]
name = "placed_ms"
ty = "u64"
# Turns WHERE status = '...' into a sub-linear lookup.
[[indexes]]
name = "by_status"
columns = ["status"]Understand returned values
| Value | What your application should do |
|---|---|
| Text, UUID or IP address | Keep the returned string. |
| Decimal | Keep the exact decimal text; use a decimal library for calculations. |
| Large integer | Use a client that preserves its precision. Do not coerce it to a JavaScript number. |
| Boolean | Read it as true or false. |
| JSON or list | Read the object or array, rather than treating it as prose. |
| Date or timestamp | Inspect the returned column type and format before converting it. |
Older clients that decode every JSON number as a floating-point value can round large integers. The newer SDK contract preserves numeric strings; check the SDK version you are using.
Temporal columns can be returned as formatted text. Calculations over those columns may return a different representation, so test the exact expression your application uses.
Choose the next task
- Write and read rows.
- Run SQL queries, including filters and grouped results.
- Use transactions when several changes must be handled together.
- Choose column types.