Getting started
Building on a database usually means learning several things before you see a single result: a query language, the code that keeps each customer's data apart, the checks that decide who may see what, and the conversions that turn stored numbers into amounts.
Do I create a database, a schema or a table?
Note
No. HTQLDB arrives ready. Sign in, roles and permissions, commerce, inventory, finance, loyalty and content are already modelled, with their schemas, tables and rules in place. You send a statement; HTQLDB already knows who is asking, what they may see and which business the rows belong to, and answers with exactly that.
Most projects spend their first weeks building what every business needs before the first feature: users and permissions, orders and customers, ledgers, a content store. With HTQLDB that groundwork is done. Your first statement reads real structures, and your time goes to what makes your product different.
| Area | Ready for you |
|---|---|
| Identity and access | Sign in for people and for apps, members and staff, roles and grants. Every statement is checked against who is asking before it runs |
| Commerce | Orders and their lines, customers, products and variants, prices, quotes, returns and refunds |
| Inventory and purchasing | Stock by location, movements and transfers, purchase orders, goods received, suppliers |
| Finance and accounting | Journals and their entries, payment fees, prepaid expenses, gift card and loyalty liabilities |
| Loyalty | Members, points, tiers and family pools |
| Content | Content types, entries, versions and assets: a content store for your sites and apps |
| Reports | Ready analytics such as cash flow, monthly revenue, cohort retention and inventory valuation, read only |
| Links and settings | A link between any two records, and key-value settings for your account |
Tables you can query lists every table in these areas with its fields and their kinds.
| You might expect to create | What is already there |
|---|---|
| A database | One HTQLDB serves every business. You never connect to it; your statements reach it through the gateway, signed in as you or as your app |
| A schema | Each area above is a schema of its own, already in place. Add your own with CREATE SCHEMA |
| Tables | Already defined for every area. Add your own with CREATE TABLE |
Tip
When your product needs data of its own, create it: CREATE SCHEMA warehouse, then CREATE TABLE inventory IN warehouse WITH FIELDS {…}. HTQLDB names both for your account, so they never collide with another account's, and your statements keep using the short names. The account's owner creates them.
Why HTQL is simple
Here is a request most applications make every day: the paid orders, with each order's number and total. The statement is the whole request; the SQL beside it is the part a database would run, without the work around it.
What usually surrounds that SQL, HTQLDB already does:
| What you would otherwise write | What HTQL does for you |
|---|---|
| Permission checks in your application code before each call | Who is calling, and through which application, is checked before the statement runs, and a refusal says why |
| Conversions from stored cents to amounts, and from stored basis points to percentages | Money arrives as an amount with its currency, and percentages as plain numbers |
| A different response shape for reads, writes and errors | One answer shape for every statement: success, data, error and info |
So the statement says only what you want, and it reads the way you would ask for it.
Where your data lives
Where your data lives. Your account is a tenant: your business, or you as a member. HTQLDB is one database that serves every tenant, and you never connect to it directly. A statement reaches it through the gateway, signed in as you or as your app, and the tenant comes from that sign in, never from the statement.
| You ask | The answer |
|---|---|
| Do I create a database? | No. One HTQLDB serves every tenant |
| Which tables can I read? | The tables of the product areas your tenant uses. Each area is a channel, a schema of its own, such as content. A bare name finds its table in your request's channel first; a qualified name such as content.entries names the schema. What you may read and write follows your roles in the tenant. Tables you can query lists every table by area with its fields, and LIST TABLES returns the same list live |
| Where do I keep my own records? | In the platform's records, through the statements their pages describe; as content, an entry of a content type, whose fields are JSON read with e.fields.name; as a link between two records, with INSERT EDGE, which creates the edge collection in your tenant's own schema on its first write; or as a setting, in the KV store |
| Do I create a table or a schema? | Only when you need your own: the account's owner runs CREATE SCHEMA and CREATE TABLE … WITH FIELDS {…}, and HTQLDB names them for the account |
Signing in. On your computer, htql login signs you in and htql switch <name> chooses the tenant to act in. An app on a server uses an app credential created in the portal, exchanged for a token that lasts an hour. Either way the statement runs as that tenant and nothing else.
Before you start
Your application calls HTQLDB with its own credential, and a person acts through their own session; Calling HTQLDB under Security & governance explains both. The three statements below read the site's own content store, a table called entries, and their results were captured by running them, so you can compare what you write with what comes back. What the answer holds, and what each info field and the cost mean, is explained in The answer every statement returns.
1. Read a few rows
Ask for the first three pages of the site by their address. FILTER keeps the pages, SHOW chooses two fields, SORT orders them and TAKE keeps three.
2. Count them by kind
Group the entries by their kind and count each group, largest first. GROUP names what to group by, COUNT counts each group, and the names in RETURN become the fields of each row.
3. Shape your own answer
Keep rows under a name with LET, then build the answer you want with RETURN. The answer is one object: count is 3, and rows holds the three links' ids.
When a statement is refused
Warning
A statement that is refused returns success: false and a message that begins with a code, such as [HTQL-1003] for text HTQL cannot read. Nothing it asked for is done: a refused write changes no row.
Error codes lists each code and how to recover.
Tip
A statement that succeeds answers success: true with your rows in data, every time, whatever it did. Check success first, then read data.
Where to go next
Examples
One statement, the whole request.