typesafe-query-builder
Advanced tools
Create and fetch PostgresSQL selects, joins and JSON aggregations and let Typescript infer the resulting data type.
Create and fetch PostgresSQL selects, joins and JSON aggregations and let Typescript infer the resulting data type.
npm install --save typesafe-query-builder
Write your database schema in typescript:
import { table, column } from 'typesafe-query-builder'
export const Systems = table('systems', {
id: column('id').integer(),
name: column('user_id').integer(),
})
export const Users = table('games', {
id: column('id').integer().default(),
title: column('name').string(),
system: column('system_id').number(),
})
import { Client } from 'pg'
export const client = new Client({...})
await client.connect()
import { query } from 'typesafe-query-builder'
const systemsAndGames = await query(Systems)
.join(Games, ({ eq }) => eq(Games.systemId, Systems.id))
.select(Systems.all(), Games.include('title'))
.fetch(client)
console.log(systemsAndGames)
// => [
// {id: 1, name: 'Switch', title: 'Mario Kart'},
// {id: 2, name: 'Playstation 4', title: 'The Last of Us', },
// {id: 2, name: 'Playstation 4', title: 'Uncharted 4'},
// ]
// Result has the correct type inferred from the schema
const name: string = systemsAndGames[0].name
// Error: Type 'number' is not assignable to type 'string':
const label: string = systemsAndGames[0].id
import { query } from 'typesafe-query-builder'
const systemsAndGames = await query(Systems)
.join(Games)
.select(Systems.all(), (subquery) =>
subquery(Games)
.selectJsonObjectArray({ key: 'games' }, Games.include('id', 'title'))
.where(({ eq }) => eq(Games.systemId, Systems.id)),
)
.fetch(client)
// receive a ready-to-use nested JSON object
console.log(usersWithItems)
// => [
// {
// id: 1,
// name: 'Switch',
// games: [
// {
// id: 1,
// title: 'Mario Kart',
// },
// ]
// },
// {
// id: 2,
// name: 'Playstation 4',
// games: [
// {
// id: 2,
// title: 'The Last of Us',
// },
// {
// id: 3,
// title: 'Uncharted 4',
// },
// ]
// },
// ]
_ to all internal methods / fields (=== those which are not
defined in src/types) to make it clear when console.logging query objects
that _ methods are all internalquery.NOW constant that will generate an sql now() function call to use in insert and where expression paramsassertNotNull() to remove null from the inferred type for a subselect by using a runtime checkjson_build_object expressions)const customQuery = sql(sql.columnsList(Manufacturers), {
system: sql.columnsJson(Systems),
})`
SELECT ${sql.columnsList(Manufacturers)},
${sql.columnsJson(Systems)} AS system
FROM ${sql(Manufacturers)}
JOIN ${sql(Systems)} ON ${sql(Systems.manufacturerId)} = ${sql(
Manufacturers.id,
)}
WHERE ${sql(Manufacturers.name)} ilike '%a%'
AND ${sql(Manufacturers.id)} IN ${sql.paramArrayOf(Manufacturers.id, 'ids')}
ORDER BY ${sql(Systems.name)} DESC
`
CROSS/LEFT JOIN LATERAL (SELECT ... WHERE <lateral-join-condition> ORDER BY ... LIMIT ...) [ON true]
see the excellent answers of Mr. Brandstetter:
.join or
.leftJoin and also support json aggregationalias(aliasName): Table method to Table to be able to use the same table many times in a query via an explicit aliasunion and unionAll for merging queriesnpm install to fetch all deps
npm run test-database:start to start a dockered postgres server that loads the test schema
npm run test-database:psql to start a psql connected to the test database
npm run test:watch to run the tests in watch mode
FAQs
Create and fetch PostgresSQL selects, joins and JSON aggregations and let Typescript infer the resulting data type.
We found that typesafe-query-builder demonstrated a healthy version release cadence and project activity because the last version was released less than a year ago. It has 0 open source maintainers collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
Snyk's use of malicious npm packages for research raises ethical concerns, highlighting risks in public deployment, data exfiltration, and unauthorized testing.
Research
Security News
Socket researchers found several malicious npm packages typosquatting Chalk and Chokidar, targeting Node.js developers with kill switches and data theft.
Security News
pnpm 10 blocks lifecycle scripts by default to improve security, addressing supply chain attack risks but sparking debate over compatibility and workflow changes.