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.

AreaReady for you
Identity and accessSign in for people and for apps, members and staff, roles and grants. Every statement is checked against who is asking before it runs
CommerceOrders and their lines, customers, products and variants, prices, quotes, returns and refunds
Inventory and purchasingStock by location, movements and transfers, purchase orders, goods received, suppliers
Finance and accountingJournals and their entries, payment fees, prepaid expenses, gift card and loyalty liabilities
LoyaltyMembers, points, tiers and family pools
ContentContent types, entries, versions and assets: a content store for your sites and apps
ReportsReady analytics such as cash flow, monthly revenue, cohort retention and inventory valuation, read only
Links and settingsA 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 createWhat is already there
A databaseOne 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 schemaEach area above is a schema of its own, already in place. Add your own with CREATE SCHEMA
TablesAlready 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 writeWhat HTQL does for you
Permission checks in your application code before each callWho 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 percentagesMoney arrives as an amount with its currency, and percentages as plain numbers
A different response shape for reads, writes and errorsOne 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 askThe 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.