
Security News
Open Source CAI Framework Handles Pen Testing Tasks up to 3,600× Faster Than Humans
CAI is a new open source AI framework that automates penetration testing tasks like scanning and exploitation up to 3,600× faster than humans.
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.
The npm package typesafe-query-builder receives a total of 23 weekly downloads. As such, typesafe-query-builder popularity was classified as not popular.
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
CAI is a new open source AI framework that automates penetration testing tasks like scanning and exploitation up to 3,600× faster than humans.
Security News
Deno 2.4 brings back bundling, improves dependency updates and telemetry, and makes the runtime more practical for real-world JavaScript projects.
Security News
CVEForecast.org uses machine learning to project a record-breaking surge in vulnerability disclosures in 2025.