Pg-Raw

A modern library for easily generating PostgreSQL raw queries through a clean and simple API.

This isn't an ORM or query executor - it focuses solely on generating SQL strings, allowing you to execute these queries using Knex, Drizzle, Prisma, or your preferred PostgreSQL client or tool.

Installation

You can install the library using your preferred package manager:

# NPM 
npm install @knaadh/pg-raw

# Yarn
yarn add @knaadh/pg-raw

# Bun
bun add @knaadh/pg-raw

Usage

Here’s a simple example showing how to use the pg-raw library:

import { findMany, FindManyParams } from '@knaadh/pg-raw';

// Basic usage example
const params: FindManyParams = {
  table: 'users',
  query: {
    select: {
      id: true,
      name: true,
    },
    where: {
      email: '[email protected]',
    },
    limit: 1,
  },
};

console.log(findMany(params));
// Output: SELECT "id", "name" FROM "users" WHERE "email" = '[email protected]' LIMIT 1

Additionally, you can also define types to ensure code completion and partial type safety

import { findMany, FindManyParams } from '@knaadh/pg-raw';

interface User {
  id: number;
  name: string;
  email: string;
}

const params: FindManyParams<User> = {
  table: 'users',
  query: {
    select: {
      id: true,
      name: true,
    },
    where: {
      email: '[email protected]',
    },
    limit: 1,
  },
};

console.log(findMany(params));
// Output: SELECT "id", "name" FROM "users" WHERE "email" = '[email protected]' LIMIT 1

The examples demonstrate how to construct a query to find a user by their email address, selecting only their id and name, and limiting the result to one row. The first example does not use types, while the second example utilizes types for added code completion and partial type safety.

Executing Generated Queries

To execute the generated queries, you can use any Postgres client such as node-postgres, postgres.js, or ORMs like Prisma that support raw queries.

Here's an example using node-postgres:

import { findMany, type FindManyParams } from "@knaadh/pg-raw";
import { Client } from "pg";

const client = new Client({
	connectionString: process.env.DATABASE_URL,
});
await client.connect();

const params: FindManyParams = {
	table: "employee",
	query: {
		select: {
			id: true,
			first_name: true,
			last_name: true,
		},
		where: {
			gender: "$1",
		},
		limit: 10,
	},
};

const employeesQuery = findMany(params);

const data = await client.query(employeesQuery, ["F"]);

console.log(data.rows);

You can find the full documentation in the repository available on GitHub.

Features

• Simple and intuitive API for generating PostgreSQL queries
• Supports generation of SELECT, INSERT, UPDATE, and DELETE queries
• Flexible query building with support for complex conditions and joins
• Automatically generates SQL queries to fetch and aggregate relational data into a single column
• Compatible with all PostgreSQL clients such as node-postgres, postgres.js or ORMs that support raw queries such as Drizzle, Prisma.

License

This package is MIT licensed