tally-server
v0.1.1
Published
Kysely adapter and Fetch handler serving the Tally Query Protocol from SQL databases.
Readme
tally-server
Serves the Tally Query Protocol from a SQL database. A Kysely adapter compiles filter, sort, group, aggregate, facet and page requests into SQL, and a Fetch-standard handler exposes any DataSource over HTTP with server sent events for live patches.
Not published yet.
Install
npm install tally-server tally-core tally-protocol kyselyKysely is a peer dependency. Bring the driver for your database: pg, mysql2, better-sqlite3, or node:sqlite through the included bridge.
Adapter
import { DatabaseSync } from 'node:sqlite'
import { Kysely, SqliteDialect } from 'kysely'
import { createSchema } from 'tally-core'
import { createKyselyAdapter, nodeSqlite, sqliteDialect } from 'tally-server'
const db = new Kysely({
dialect: new SqliteDialect({ database: nodeSqlite(new DatabaseSync('app.db')) }),
})
const schema = createSchema({ key: 'id', columns })
const accounts = createKyselyAdapter({ db, table: 'accounts', schema, dialect: sqliteDialect })| Option | Default | Notes |
| ------------ | -------- | ------------------------------------------------------------------------------------------- |
| db | | A Kysely instance. |
| table | | Table or view name. |
| schema | | From createSchema. The key must be a field name, not a function. It need not be a column. |
| dialect | sqlite | sqliteDialect, postgresDialect, mysqlDialect or your own SqlDialect. |
| columns | | Column id to SQL column name, for { fullName: 'full_name' }. |
| flags | | Named sql expressions that flag filters compile to. |
| pagination | offset | offset or cursor. Cursors are opaque and encode the offset. |
| maxLimit | 1000 | Page sizes above this are clamped. Requests without a page return at most this many rows. |
| mutable | true | false refuses PATCH /rows. |
| today | now | ISO date that relative ranges such as last 30 days resolve against. |
| bins | 20 | Histogram bins. |
| maxTerms | 50 | Terms facet size. |
| maxGroups | 1000 | Group summaries are cut to the first this many groups, and pages past them are empty. |
| onError | | Receives the database error behind a rejected mutation. |
Every request is validated with tally-protocol before any SQL runs. Mutations run in one transaction and return the canonical rows read back from the database. A database error rejects them with Mutation failed rather than the driver message.
Semantics
The adapter passes the tally-protocol conformance suite on SQLite, Postgres and MySQL, so filters, sorts, groups, aggregates, facets and pages match the in-memory engine:
- Empty means
NULL, and for text columns also''. Empty values sort last in both directions and never match comparisons, exceptneq,notContainsandnotIn, which keep them. - Text comparisons are case insensitive through
lower(). SQLite folds ASCII only. - Enum columns sort and facet in option order.
- Date groups bucket to
YYYY-MM-DD, ISO Monday weeks,YYYY-MM,YYYY-QnorYYYY. - Search matches the stored value of searchable columns, not the formatted text.
- Group order follows the sort on the group column when there is one, otherwise option or natural order, newest dates first.
- Empty values form one group,
NULLand''alike, which sorts last and pages like any other group.
Dialects
A SqlDialect supplies the few functions SQL engines disagree on: text, lower, float, floor, dayNumber, dateBucket, plus encode and decode for values crossing the driver. sqliteDialect, postgresDialect and mysqlDialect ship with the package. Implement it for SQL Server or another engine and pass it as dialect.
With pg and mysql2, date columns come back as local Date objects and are converted to ISO dates. Numeric aggregates arrive as strings and are converted to numbers. On MySQL, booleans are stored as tinyint and decoded back to true and false. Group queries are valid under the default ONLY_FULL_GROUP_BY mode.
import { Kysely, MysqlDialect } from 'kysely'
import { createPool } from 'mysql2'
import { createKyselyAdapter, mysqlDialect } from 'tally-server'
const db = new Kysely({
dialect: new MysqlDialect({ pool: createPool({ uri: process.env.DATABASE_URL }) }),
})
const accounts = createKyselyAdapter({ db, table: 'accounts', schema, dialect: mysqlDialect })Handler
import { createTallyHandler } from 'tally-server'
const handler = createTallyHandler({ source: accounts, schema, basePath: '/api/accounts' })The handler is (request: Request) => Promise<Response>, so it mounts anywhere Fetch is the interface:
app.all('/api/accounts/*', (c) => handler(c.req.raw))| Route | Body | Response |
| ------------- | ------------------------ | ----------------------------------------------------------- |
| GET /schema | | { version, columns, capabilities, key } |
| POST /query | QueryRequest | QueryResponse |
| PATCH /rows | { version, mutations } | MutationResult, or 405 when the source is read only |
| GET /events | | text/event-stream of patch events carrying a RowPatch |
| Option | Default | Notes |
| -------------- | --------- | -------------------------------------------------------------------- |
| source | | Any DataSource. |
| schema | | From createSchema. |
| basePath | | Prefix stripped before routing. |
| heartbeatMs | 15000 | Interval of keep-alive comments on event streams. |
| maxBodyBytes | 1048576 | Larger request bodies are refused with 413. |
| onError | | Receives unexpected errors. The response only says Internal error. |
Accepted mutations are broadcast to every open event stream, and patches from a source with live capability are relayed, so the schema route always reports live: true. An event stream whose consumer falls more than 1024 events behind is closed, and the client reconnects. Invalid requests return 400 with { code, message, details } where every detail names the path of a problem. Unknown routes return 404, wrong methods 405 with an Allow header, and unexpected errors 500 with { code: 'internal' }.
Testing your own adapter
import { runConformance } from 'tally-protocol'
const report = await runConformance((request) => adapter.query(request), {
capabilities: adapter.capabilities,
})
expect(report.failures).toEqual([])