frontqloffline

API documentation

Every route, query parameter and header the local api understands, with an example for each. The examples use the same-origin entry point; the same calls work through connect() and sdk.fetch() from the bundle.

Other documentation

Alongside this reference, these pages go deeper on individual parts of the API.

Install and offline use

Import the JavaScript file. It contains the complete SDK — the API, the built-in database engine and the caching logic — so there is nothing else to download or host.

import frontql from 'https://offline.frontql.dev/sdk.js'

// Caching starts automatically in the background.

Reading a response

Every request resolves to a real Response whose JSON body is the original envelope:

import frontql from 'https://offline.frontql.dev/sdk.js'

const res = await frontql.fetch('/items?sort=-id')
const { err, result, count } = await res.json()

Get

Read a collection. /<collection> returns every row (at most 1000 unless you page); /<collection>/1,2 returns specific ids.

const res = await frontql.fetch('/items?sort=-id')
const { err, result } = await res.json()

const two = await frontql.fetch('/items/1,2')

Column selection, aliases and hidden columns:

await frontql.fetch('/items?fields=id,name,qty')          // query parameter
await frontql.fetch('/items?fields=label=name')           // name as label
await frontql.fetch('/items', { headers: { fields: 'id,name' } })
await frontql.fetch('/items?fields=id,name,done', { headers: { hidden: 'done' } })

Post

Create a row. result carries the number of rows written and the new row id.

await frontql.fetch('/items', {
  method: 'POST',
  body: JSON.stringify({ name: 'Widget', qty: 3, done: 0 }),
})
// { err: false, result: { rowsAffected: 1, lastInsertId: 6 } }

An array body is one multi-row INSERT:

await frontql.fetch('/items', {
  method: 'POST',
  body: JSON.stringify([
    { name: 'Gadget', qty: 8, done: 1 },
    { name: 'Anvil', qty: 1, done: 0 },
  ]),
})
// INSERT INTO items (name, qty, done) VALUES (?, ?, ?), (?, ?, ?)

Put

Update rows by id. The id lives in the path, or in the body for a bulk update.

await frontql.fetch('/items/5', {
  method: 'PUT',
  body: JSON.stringify({ name: 'Renamed', done: 1 }),
})

await frontql.fetch('/items/1,2,3', {                     // several ids at once
  method: 'PUT',
  body: JSON.stringify({ done: 1 }),
})

Operators on the column name update in place — +, -, *, /:

await frontql.fetch('/items/5', {
  method: 'PUT',
  body: JSON.stringify({ 'qty+': 1 }),                    // qty = qty + ?
})

Bulk update: an array body, with id on every row, and no id in the path.

await frontql.fetch('/items', {
  method: 'PUT',
  body: JSON.stringify([{ id: 1, 'qty+': 1 }, { id: 2, 'qty+': 1 }]),
})

Delete

await frontql.fetch('/items/5', { method: 'DELETE' })
await frontql.fetch('/items/1,2', { method: 'DELETE' })

await frontql.fetch('/items', {                          // scoped by a filter
  method: 'DELETE',
  headers: { filter: 'done:1' },
})

Like PUT, a delete with no id and no filter matches nothing.

SQL

POST /sql-<name> runs one statement with bound parameters. The part after sql- is only a route marker — it names no table.

await frontql.fetch('/sql-db', {
  method: 'POST',
  body: JSON.stringify({ sql: 'select * from items where qty > ?', params: [2] }),
})
const res = await frontql.fetch('/sql-db', {
  method: 'POST',
  body: JSON.stringify([
    { sql: 'select count(*) as items from items' },
    { sql: 'select status, count(*) as n from orders group by status' },
  ]),
})

Search

?search= takes a small expression language: column operator value, with , for AND and | for OR.

OperatorMeansExample
:equalsname:widget
!:not equalsdone!:1
~LIKE, * is the wildcard, case-insensitivename~*wid*
::in, values separated by /id::1/2/3
!::not inid!::1/2/3
> < >: <:comparisons (>=, <=)qty>:5
await frontql.fetch('/items?search=name~*a*')
await frontql.fetch('/items?search=qty>:5,done:1')            // AND
await frontql.fetch('/items?search=name~*a*|name~*o*')        // OR

The column is prefixed with the collection for you. To search a joined table, prefix with the foreign key (see Join):

await frontql.fetch('/orders?search=user_id.name~*a*', {
  headers: { collections: 'user_id:users' },
})

Pagination

?page=<number>,<size> limits the returned rows. count still reports the total number of matches.

const res = await frontql.fetch('/items?sort=id&page=2,10')
const { result, count } = await res.json()

Sort

?sort= takes a comma-separated list; a leading - sorts descending.

await frontql.fetch('/items?sort=-qty,name')

await frontql.fetch('/orders?sort=user_id.name', {        // a joined column
  headers: { collections: 'user_id:users' },
})

Filter

The filter header is the same expression language as search, applied as the WHERE clause.

await frontql.fetch('/items', { headers: { filter: 'qty>:5,done:1' } })

await frontql.fetch('/orders', { headers: { filter: 'status:paid|status:shipped' } })

It also carries session placeholders, which is how a read is scoped to the signed-in row:

await frontql.fetch('/orders', { headers: { session, filter: 'user_id:{id}' } })

Join

The collections header lists relations as foreign_key:table. Joined columns are selected in fields by table name.

const res = await frontql.fetch('/orders?fields=id,item,total,users.name', {
  headers: { collections: 'user_id:users' },
})
const { result } = await res.json()
// [ { id: 1, item: 'Widget', total: 30, user_id: { name: 'Ada Admin' } }, … ]

Where In

Three spellings, depending on what you already have:

await frontql.fetch('/items/1,2,3')                            // ids in the path

await frontql.fetch('/items?search=id::1/2/3')                 // id IN (?, ?, ?)

await frontql.fetch('/items', { headers: { filter: 'id!::1/2/3' } })   // NOT IN

Authentication

POST /auth-<collection> matches one row of your own table and answers with it plus a signed, expiring session.

const res = await frontql.fetch('/auth-users', {
  method: 'POST',
  headers: { fields: 'id,name,role' },
  body: JSON.stringify({ email: 'ada@example.com', password: 'secret' }),
})
const { err, result, session } = await res.json()
// result:  { id: 1, name: 'Ada Admin', role: 'admin' }
// session: 'eyJpZCI6…'

Send the session back on later requests:

await frontql.fetch('/orders', { headers: { session } })

Permissions

The permission header is an expression evaluated against the session before the request runs. If it is false the request stops.

await frontql.fetch('/items', { headers: { session, permission: '{role}:admin' } })

await frontql.fetch('/items', {
  headers: { session, permission: '({role}:admin)|({role}:editor)' },
})

Near By

?nearby=<lat>,<lng> gives a point and the nearby header names the two columns holding it. A nearby distance in kilometres is added to every row and the rows come back nearest first.

const res = await frontql.fetch('/places?nearby=26.14,91.73', {
  headers: { nearby: 'lat,lng' },
})
const { result } = await res.json()
// [ { id: 3, name: 'Tezpur', lat: 26.63, lng: 92.8, nearby: 120.4 }, … ]

Validation

The validation header is a JSON object mapping a field to rules, joined with |. It runs before a POST or PUT is written.

RulePasses when
requiredthe value is present — 0 counts
string number boolean integerthe value has that JS type
emailthe string looks like an email
min:n max:n>= / <=
len:n minlen:n maxlen:nstring length
await frontql.fetch('/items', {
  method: 'POST',
  headers: {
    validation: JSON.stringify({ name: 'required|minlen:3', qty: 'integer|min:1' }),
  },
  body: JSON.stringify({ name: 'widget', qty: 3 }),
})

A failure names the rules that did not pass, per field:

// body: { name: 'ab', qty: 'four' }
// -> { err: true, result: { name: 'error:minlen:3', qty: 'error:integer' } }

Raw SQL can validate its bound parameters too. Supply a validation array whose rules line up with the positions in params:

await frontql.fetch('/sql-db', {
  method: 'POST',
  body: JSON.stringify({
    sql: 'insert into items (name, qty) values (?, ?)',
    params: ['widget', 3],
    validation: ['required|minlen:3', 'integer|min:1'],
  }),
})
// on failure: { err: true, sql: '…', params: ['ab', 3], result: ['error:minlen:3'] }

Headers at a glance

HeaderUsed byValue
fieldsGET, authcolumns to return, alias=column renames
hiddenGET, writescolumns (or body keys) to drop
filterGET, PUT, DELETEexpression, with {session} placeholders
collectionsGETforeign_key:table, comma-separated
nearbyGETthe two coordinate columns, e.g. lat,lng
sessionGET, PUT, DELETE, permissionsthe token from /auth-<collection>
permissionanyexpression over the session
validationPOST, PUTJSON rules per field
expiryauthsession lifetime in seconds

File storage

The named storage export stores files in the browser's own offline storage. It is part of sdk.js, so there is no additional JavaScript file to download.

import frontql, { storage } from 'https://offline.frontql.dev/sdk.js'

Upload

Pass a standard FormData object. Every file entry is stored; an optional text field named folder places the files below that relative folder.

const formData = new FormData()
formData.append('image', fileInput.files[0])
formData.append('folder', 'products')

const result = await storage.upload(formData)
const path = result.files.image
// { files: { image: 'products/179002967073789.png' }, success: 1 }

Repeated files under the same field name produce an array of paths. Generated names are numeric and keep a safe filename extension.

Get

storage.get(path) returns a File, which is also a Blob. Its original filename, MIME type and last-modified time are restored.

const file = await storage.get(path)
const imageUrl = URL.createObjectURL(file)

image.src = imageUrl

// Release the temporary URL when the preview is no longer needed.
URL.revokeObjectURL(imageUrl)

The generated blob: URL is temporary. Save the storage path in your database, then call storage.get(path) again when a future page needs the file.

Remove

const removed = await storage.remove(path)
// true when a file was removed; false when it was already missing

Open the complete file-storage example →

Import and export

frontql.import() restores a UTF-8 SQL dump incrementally. It accepts a File, Blob, Response, ReadableStream, async iterable, or SQL string. The complete input is never loaded into memory.

import frontql from 'https://offline.frontql.dev/sdk.js'

const file = fileInput.files[0]
const result = await frontql.import(file, {
  onProgress: ({ bytes, statements }) => {
    console.log(bytes, statements)
  },
})
// { bytes: 1073741824, statements: 840221 }

frontql.export() opens the browser's save dialog and streams the SQL dump directly to the selected file. It does not collect the complete export in memory.

downloadButton.addEventListener('click', async () => {
  const result = await frontql.export({
    filename: 'frontql-export.sql',
    batchSize: 100,
    onProgress: ({ table, rows }) => console.log(table, rows),
  })

  console.log(result)
  // { filename: 'frontql-export.sql', rows: 125000, bytes: 84200321 }
})

Important: call frontql.export() directly inside a user action such as a click or key event. Do not put a timer or another awaited operation before it, because the browser may block the save dialog.

// Advanced: stream directly to your own destination
const result = await frontql.export({
  writable: yourWritableStream,
  batchSize: 100,
})
console.log(result.rows, result.bytes)

Open the streaming import/export example →