Drizzle Query Parser

A query string parser for PostgreSQL with Drizzle ORM, inspired by mongoose-query-parser. This library helps you convert URL query strings into Drizzle ORM queries for PostgreSQL.

Installation

npm install drizzle-query-parser

Features

• Parse URL query parameters into Drizzle ORM compatible queries
• Support for all common filtering operators
• Field selection
• Sorting
• Pagination (limit & offset)
• Type casting
• Regex pattern matching
• Predefined query templates

Usage

import { DrizzleQueryParser } from 'drizzle-query-parser';
import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';

// Initialize your database schema and connection
const db = drizzle(new Pool({ /* connection config */ }));

// Initialize the parser
const parser = new DrizzleQueryParser({
  dateFormat: 'yyyy-MM-dd',
  blacklist: ['password', 'token']
});

// Parse a query string
const queryOptions = parser.parse('type=public&rating>=4.5&country=GB,US', yourSchema);

// Use the parsed query with Drizzle
const results = await db.select()
  .from(yourTable)
  .where(queryOptions.filter)
  .limit(queryOptions.limit)
  .offset(queryOptions.offset);

Filtering Operators

| SQL Equivalent | URI | Example | Result | |--------------- | -------------------- | ----------------------- | ------------------------------------------------------- | | = | key=val | type=public | eq(table.type, 'public') | | > | key>val | count>5 | gt(table.count, 5) | | >= | key>=val | rating>=9.5 | gte(table.rating, 9.5) | | < | key<val | createdAt<2023-01-01 | lt(table.createdAt, new Date('2023-01-01')) | | <= | key<=val | score<=-5 | lte(table.score, -5) | | != | key!=val | status!=success | ne(table.status, 'success') | | IN | key=val1,val2 | country=GB,US | inArray(table.country, ['GB', 'US']) | | NOT IN | key!=val1,val2 | lang!=fr,en | notInArray(table.lang, ['fr', 'en']) | | IS NOT NULL | key | phone | isNotNull(table.phone) | | IS NULL | !key | !email | isNull(table.email) | | ~ | key=/value/<opts> | email=/@gmail\.com$/i | sql\table.email ~ '(?i)@gmail.com$'` | |!~ |key!=/value/|phone!=/^06/ |sql`table.phone !~ '^06'`` |

Advanced Features

Field Selection

Select specific fields:

?select=id,name,email

Exclude specific fields: