arn-db

A knex-style query builder for Node.js. It talks to a Go query API (/v2/raw) over HTTP, and that API talks to Postgres.

Node app (arn-db) ──HTTP/JSON──▶ your-api.example.com/v2/raw (Go) ──▶ Postgres

Server-side only: every request is a POST with an apikey header. The API refuses browser requests (any request with an Origin header) and sends no CORS headers. Each key may be limited to a list of tables.

New here? Read GUIDE.md: every method, with recipes for nested AND/OR/NOT, relations, jsonb search and changes, counts, upsert, transactions and raw SQL.

Install

npm install arn-db

Set the API address and the app's key (in .env or the container env):

ARN_DB_URL=https://your-api.example.com/v2/raw
ARN_DB_KEY=<your API key>

import { db } from 'arn-db'          // or: const { db } = require('arn-db')

const { data, error } = await db('users').where('id', 1).single()

Importing arn-db loads ./.env into process.env (Node 20.12+; variables that are already set win), so your files need no dotenv import. db reads the two variables on its first query. ESM, CommonJS and TypeScript types are included.

For another key or server, make a client: createClient(url, key, { readFrom: 'replica' }).

Quick tour

// read
await db('users').select('id', 'name', 'city:meta->>city').where('active', true).orderBy('name').limit(20)
await db('users').where('votes', '>', 100).orWhere({ role: 'admin' })
await db('users').whereIn('id', [1, 2, 3]).whereNotNull('verified_at')
await db('users').whereBetween('age', [18, 30]).whereILike('email', '%@example.com')
await db('accounts').whereColumn('used', '<', 'limit')
await db('accounts').where(db.coalesce('last_read_at', 'created_at'), '<', db.now().minus('1 day'))

// nested groups: (id = 1 or id > 10) or (name = 'Tester')
await db('users').where((q) => q.where('id', 1).orWhere('id', '>', 10)).orWhere({ name: 'Tester' })

// one row, or null
const { data: user } = await db('users').where('email', email).single()

// count
const { data: n } = await db('users').where('active', true).count()             // a number

// write
await db('users').insert({ name, email }).returning('id')                         // [{ id }]
await db('users').where('id', id).update({ name, last_seen: db.now() })
await db('accounts').where('id', id).increment('total_used').updateJsonb('tags', 'addUnique', 'vip')
await db('posts').where('user_id', id).delete()

// upsert, find-or-create
await db('users').insert({ email, name }).onConflict('email').merge()
const { data } = await db('users').insert({ email, name }).onConflict('email').ignore().returning('id')

Every call resolves to { data, error, count, status } and never throws. A mistake the client can see (an unknown operator, a binding mismatch, an update without where) comes back as error.code === 'BAD_QUERY' and nothing is sent.

const { data, error } = await db('users').insert({ email })
if (error) {
  if (error.code === '23505') { /* email already used */ }
}

Where

| Method family (each has where/orWhere/whereNot/orWhereNot forms) | Example | |---|---| | where | .where('status', 1) · .where('votes', '>', 100) · .where({ a: 1, b: 2 }) · .where('deleted_at', null) | | whereIn | .whereIn('id', [1, 2]) · .whereNotIn('email', list) | | whereNull | .whereNull('deleted_at') · .whereNotNull('verified_at') | | whereBetween | .whereBetween('age', [18, 30]) | | whereLike / whereILike | .whereILike('email', '%@example.com') | | whereColumn | .whereColumn('updated_at', '>', 'created_at') | | whereExists | .whereExists('posts!user_id', (q) => q.where('published', true)) | | whereJsonb | .whereJsonb('tags', 'has', 'vip') | | whereRaw | .whereRaw('lower(email) = ?', [email]) |

Operators: = != <> > >= < <= like ilike not like not ilike in not in is is not. Anything else is a BAD_QUERY error, so a typo can never become SQL.

A function makes one set of parentheses, to any depth. The knex function () { this.where(...) } form works too.

jsonb

Search with whereJsonb(column, op, value):

| op | Matches when | |---|---| | has | the array contains the value | | hasAll | it contains every value | | hasAny | it contains at least one of the values (any element type) | | contains | the object (or array) includes these pairs / items, at any depth | | containedBy | it has nothing outside the list | | hasKey / hasAnyKey / hasAllKeys | the key(s) exist | | length | .whereJsonb('tags', 'length', '>', 2): the array size compared | | empty | [], {} or null | | equals | exactly this value (same order) |

Change with updateJsonb(column, op, value): append, appendOne, prepend, addUnique, remove, removeAt, setAt, set, merge, removeKey, increment. Use -> paths to work inside an object ('meta->labels'). It can be chained with update() and increment(), and the whole lot is one atomic statement.

Relations

await db('posts').select('title, author:users!user_id(name), comments(body), labels(name)')
await db('users').select('name, posts!user_id!inner(title)').where('posts.published', false)

// write related rows in the same statement (all or nothing)
await db('posts').insert({
  title: 'Hello',
  user_id: userId,
  comments: db.rel.create([{ body: 'first', author_id: userId }]),
  labels: db.rel.connect([labelId]),
}).returning('*')

db.rel.create, upsert, connectOrCreate, connect, disconnect, set, update and delete work for one-to-many, many-to-many and many-to-one links. See the table in GUIDE.md.

Transactions

The callback returns the steps without awaiting them. They run in order, and if any step fails, nothing is saved.

const { data, error } = await db.transaction((tx) => ({
  user: tx('users').insert({ email, name }).onConflict('email').ignore().returning('id'),
  post: tx('posts').insert({ user_id: tx.ref('user.id'), title }).returning('id'),
  n:    tx('posts').where('user_id', tx.ref('user.id')).count(),
}))
data.user[0].id
data.n             // a number

Raw SQL

db.raw(sql, bindings) is one SQL fragment, never a whole statement. It takes the knex binding forms: ? (a value), ?? (a column name), :name and :name:.

await db('users').whereRaw('lower(trim(email)) = ?', [email])
await db('users').select('id', db.raw('char_length(name)').as('name_len'))
await db('users').orderByRaw('length(name) desc, id')
await db('accounts').where('id', id).update({ score: db.raw('score * ?', [2]) })

Values are always sent as query parameters. The API key needs raw permission.

Read replicas

await db('posts').where('published', true).replica()
const reads = createClient(url, key, { readFrom: 'replica' })
await reads('users').where('email', email).primary()

Writes and transactions always use the primary.

Upgrading from 0.2

0.3 replaces the Supabase-style methods with knex-style ones. See GUIDE.md: Upgrading from 0.2 for the full table.

| 0.2 | 0.3 | |---|---| | db.from('t').select() | db('t') | | .eq('a', 1) · .lt('a', 1) · .in('a', [..]) | .where('a', 1) · .where('a', '<', 1) · .whereIn('a', [..]) | | .or((q) => q.eq(..).eq(..)) | .where((q) => q.where(..).orWhere(..)) | | .order('a', { ascending: false }) | .orderBy('a', 'desc') | | .upsert(row, { onConflict: 'email' }) | .insert(row).onConflict('email').merge() | | { n: db.ops.increment(1) } | .increment('n', 1) | | { tags: db.ops.append('x') } | .updateJsonb('tags', 'append', 'x') | | .overlaps('tags', [..]) / .contains(..) | .whereJsonb('tags', 'hasAny', [..]) / 'hasAll' | | db.col('b') | db.ref('b') or whereColumn |