hélice

Typesafe query builder

Keep your SQL. Lose the typos.

A fluent query builder for PostgreSQL. Describe your tables as plain TypeScript types, and your editor checks and completes your queries against them.

Get started Open the playground
pnpm add helice
import { Helice } from 'helice';

// Your schema, as plain TypeScript types
type User = { id: number; name: string; active: boolean };
type Post = { author_id: number; title: string; views: number };

const db = new Helice<{ user: User; post: Post }>();

const { query, args } = db.select('post')
	.join({ 'i#user': 'id = post.author_id' })
	.field({ title: 'post.title', author: 'user.name' })
	.where({ 'user.active': true, '>=:post.views': 500 })
	.orderBy({ 'post.views': 'DESC' })
	.limit(10)
	.build();
SELECT post.title AS title, user.name AS author
FROM post
INNER JOIN user
	ON user.id = post.author_id
WHERE (
	user.active = $1
	AND post.views >= $2
)
ORDER BY post.views DESC
LIMIT 10

args [ true, 500 ]

The clauses you already know

Fields, joins, CTEs, filters, ordering, pagination. Read more

const { query, args } = db.select('post')
	.field(['id', 'title'])
	.where({
		published : true,
		'&&:any'  : [
			{ '~~:title': '%TypeScript%' },
			{ '>=:views': 1000 },
		]
	})
	.orderBy({ views: 'DESC' })
	.limit(10)
	.build();
SELECT id, title
FROM post
WHERE (
	published = $1
	AND (
		(
			title ~~ $2
		)
		OR (
			views >= $3
		)
	)
)
ORDER BY views DESC
LIMIT 10

args [ true, "%TypeScript%", 1000 ]

Two vocabularies, one SQL

Compact symbols, or keys that read like the SQL they produce ? Choose per instance, or write your own set of keys : types and parsers follow it end to end.

DefaultSyntaxKeys

const { query, args } = db.select('post')
	.join({ 'i#user': 'id = post.author_id' })
	.field({ postId: 'post.id', author: 'user.name' })
	.where({ '>=:post.views': 500, '~~:post.title': '%hello%' })
	.build();

VerboseSyntaxKeys

const { query, args } = verbose.select('post')
	.join({ 'INNER JOIN user': 'id = post.author_id' })
	.field({ postId: 'post.id', author: 'user.name' })
	.where({ 'post.views >=': 500, 'post.title ~~': '%hello%' })
	.build();

Both produce

SELECT post.id AS postId, user.name AS author
FROM post
INNER JOIN user
	ON user.id = post.author_id
WHERE (
	post.views >= $1
	AND post.title ~~ $2
)
How syntax keys work

The compiler reads your schema

Table names, selected columns, the type of each value, what a join brings into scope : a mistake is a red line in your editor, not an error from your database in production.

Each line on the right is a real compile error : this repository's CI fails the day one of them stops being one.

Try it in the playground
// @ts-expect-error : there is no "pots" table
db.select('pots');

// @ts-expect-error : "views" is a number
db.select('post').where({ views: 'many' });

// @ts-expect-error : "email" belongs to user, which was never joined
db.select('post').field('email');

// @ts-expect-error : "nam" is not a column of user
db.insert('user').values({ nam: 'Alice' });

// @ts-expect-error : only "id" is allowed at call time
db.delete('user').prepare({ where: { user: ['id'] } })({ where: { name: 'Alice' } });

And the rest

Before you install

Hélice writes table and column names as they are, without quoting them. Names that collide with PostgreSQL reserved words (order, group…), or that contain uppercase letters or special characters, are not supported : stick to lowercase snake_case.

Getting started Install, describe your schema, choose your syntax.Clauses field, where, join, orderBy and returning.Queries What each query type accepts, prepare, build and execute.SELECT field, join, CTE, where, orderBy, limit, prepare optionsINSERT values, returning, runtime valuesUPDATE set, using, CTE, where, returning, prepare optionsDELETE using, CTE, where, returning, prepare options