Skip to content
A QueryBuilder for PostgreSQL written in TypeScript
TypeScript
Branch: development
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
.github/ISSUE_TEMPLATE
.vscode
src
test/query
.editorconfig
.gitignore
.nycrc
.prettierrc.json
.travis.yml
CHANGELOG.md
LICENSE
README.md
package-lock.json
package.json
tsconfig.json
tslint.json

README.md

node-pg-query-builder

Build Status dependencies Status devDependencies Status GitHub version

A QueryBuilder for PostgreSQL written in TypeScript

Install

npm

$ npm install --save @shinigami92/pg-query-builder

Status of the project

The project is currently under construction

Following needs to be implemented for 1.0.0:

  • Some missing comparison operators
  • Missing DataTypes
  • Implement joins
    • CROSS JOIN
    • INNER JOIN
    • LEFT JOIN
    • RIGHT JOIN
  • Test coverage close to 100%
  • TypeDocs

Planned for the future:

  • 1.1.0
    • Mutable QueryBuilders (INSERT, UPDATE, DELETE)
  • 1.2.0
    • Window Functions
  • Backlog
    • Having
    • Union
    • Distinct
    • Mathematical Functions and Operators
    • String Functions and Operators
    • JSON Functions and Operators
    • ...

How-To

import { Client } from 'pg';
import {
	AliasReference,
	and,
	cast,
	ColumnDefinition,
	DATE,
	eq,
	QueryBuilder,
	RegConfig,
	select,
	TableDefinition,
	TEXT,
	to_tsquery,
	to_tsvector,
	ts_rank_cd,
	TsQueryAliasReference,
	tsvector_matches_tsquery,
	TsVectorAliasReference,
	UUID
} from 'pg-query-builder';

// Create a meta-model that defines your table
class VFulltextSearchTable extends TableDefinition {
	public readonly searchtext: ColumnDefinition = new ColumnDefinition('searchtext', TEXT, this);
	public readonly user_id: ColumnDefinition = new ColumnDefinition('user_id', UUID, this);
	public readonly created_date: ColumnDefinition = new ColumnDefinition('created_date', DATE);
}

// Maybe you want aliases for prettier SQL
const asV: AliasReference = new AliasReference('v');

const VFulltextSearch: VFulltextSearchTable = new VFulltextSearchTable('v_fulltext_search', asV);

const asTextsearch: TsVectorAliasReference = new TsVectorAliasReference('textsearch');
const asQuery: TsQueryAliasReference = new TsQueryAliasReference('query');
const asRank: AliasReference = new AliasReference('rank');

// Build the query
const query: QueryBuilder = select(VFulltextSearch.__star, [ts_rank_cd(asTextsearch, asQuery), asRank])
	.from(VFulltextSearch)
	.crossJoin(to_tsquery(RegConfig.SIMPLE, cast('abc:*', TEXT)), asQuery)
	.crossJoin(to_tsvector(RegConfig.SIMPLE, VFulltextSearch.searchtext), asTextsearch)
	.where(
		and([
			eq(VFulltextSearch.user_id, cast('971acc92-5b1e-4dd4-b177-a0dee7a27c21', UUID)),
			tsvector_matches_tsquery(asTextsearch, asQuery)
		])
	)
	.orderBy(VFulltextSearch.created_date, [asRank, 'DESC'])
	.limit(10)
	.offset(0);

// To SQL-String
const sql: string = query.toSQL({ pretty: true, semicolon: true });

// Using pg
const queryConfig: QueryConfig = query.toQuery();
const client = new Client(dbConfig);
client.query(queryConfig);

Result:

SELECT v.*,
       ts_rank_cd(textsearch, query) AS rank
FROM v_fulltext_search AS v
CROSS JOIN to_tsquery('simple', 'abc:*'::text) AS query
CROSS JOIN to_tsvector('simple', v.searchtext) AS textsearch
WHERE v.user_id = '971acc92-5b1e-4dd4-b177-a0dee7a27c21'::uuid
  AND textsearch @@ query
ORDER BY created_date,
         rank DESC
LIMIT 10
OFFSET 0;

Why?

  • JSDoc - Move the mouse pointer over a function and get corresponding PostgreSQL specific explanation
  • Big projects with multiple members get better overview
  • Easier refactoring
  • Typesafe - Autocast to PostgreSQL data types or early interception if the data type is incompatible
  • Autocomplete - See what you can pass into a Where, OrderBy or Compare function

License

MIT License - 2018 Christopher Quadflieg

You can’t perform that action at this time.