Prisma Query Builder

A lightweight, type-safe query builder for Prisma that simplifies complex SQL queries while maintaining the power of raw SQL. Built with TypeScript for enhanced type safety and developer experience.

Features

• 🔍 Type-safe query building
• 📝 Simple and intuitive API
• 🚀 Built-in pagination support
• 🔗 Supports complex joins and subqueries
• 🛠️ Works seamlessly with Prisma
• 🧩 Extensible and customizable

Installation

npm install @explita/prisma-query-builder
# or
yarn add @explita/prisma-query-builder
# or
pnpm add @explita/prisma-query-builder

Standalone Usage

import { PrismaClient } from "@prisma/client";
import { queryBuilder } from "@explita/prisma-query-builder";

const prisma = new PrismaClient();

// Example: Fetch active users with pagination
const [users, meta] = await queryBuilder({
  prisma,
  select: ["id", "name", "email", "createdAt"],
  table: "users",
  where: {
    status: "ACTIVE",
    lastLogin: { gte: new Date(Date.now() - 30 * 24 * 60 * 60 * 1000) }, // Last 30 days
  },
  orderBy: { createdAt: "DESC" },
}).exec.paginate({
  page: 1,
  limit: 10,
});

console.log(`Found ${meta.totalCount} active users`);
console.log(users);

As a Prisma Extension

You can also extend your Prisma client with the query builder:

import { PrismaClient } from "@prisma/client";
import { queryBuilderExtension } from "@explita/prisma-query-builder";

// Extend the Prisma client
const prisma = new PrismaClient().$extends(queryBuilderExtension());

// Now you can use queryBuilder directly on your Prisma client
const result = await prisma
  .queryBuilder({
    select: ["id", "name"],
    table: "users",
    where: { status: "ACTIVE" },
  })
  .exec();

Basic Query Examples

import { PrismaClient } from "@prisma/client";
import {
  queryBuilder,
  queryBuilderExtension,
} from "@explita/prisma-query-builder";

const prisma = new PrismaClient();

// Basic query
const result = await queryBuilder({
  prisma, // Not needed when using the extension
  select: ["id", "name", "email"],
  table: "users",
  where: {
    status: "ACTIVE",
    age: { gte: 18 },
    OR: [{ role: "ADMIN" }, { role: "EDITOR" }],
  },
  orderBy: { createdAt: "DESC" },
  limit: [10],
}).exec();

// With pagination
const [data, meta] = await queryBuilder({
  prisma, // Not needed when using the extension
  select: ["id", "title", "price"],
  table: "products p",
  where: {
    "p.status": "ACTIVE",
    "p.price": { lt: 100 },
  },
  join: [
    {
      table: "categories c",
      type: "LEFT",
      conditions: { "c.id": "p.category_id" },
    },
  ],
}).exec.paginate({
  page: 1,
  limit: 25,
});

API Reference

queryBuilder(options: QueryBuilder): QueryBuilderResult

Creates a new query builder instance.

Options

• prisma: PrismaClient instance (required for direct execution, not needed when using the extension)
• driver: Database driver ('postgres' | 'mysql' | 'sqlite'), defaults to 'postgres'
• select: Array of columns to select (supports raw SQL expressions and subqueries)
• table: The main table to query (can include alias, e.g., "products p")
• where: Conditions for filtering (supports complex conditions with AND/OR)
• join: Array of join definitions (supports LEFT, INNER, RIGHT, FULL joins)
• groupBy: Array of columns to group by
• orderBy: Sorting configuration (object with column: 'ASC'|'DESC')
• limit: [limit, offset] or [limit] for simple pagination (not recommended, use paginate() instead)

QueryBuilderResult

• raw: Get the raw SQL query (Sql object)
• exec<T = any>(): Execute the query and return a promise that can be chained with paginate()
  • T: Optional type parameter for the returned data shape
• toSQL(): Get the SQL string
• toParams(): Get the parameter values
• exec<T>().paginate(): Execute with pagination, returns a tuple of [T, PaginationMetadata]
  • T: Optional type parameter for the data items in the paginated results
• exec.paginate<T>(): Alternative syntax for exec<T>().paginate()

Pagination Response

When using pagination, you'll receive a tuple with two elements. There are two ways to call it:

// Using method chaining
const [data, meta] = await queryBuilder({...}).exec().paginate({
  page: 1,
  limit: 25
});

// Or using the alternative syntax
const [data, meta] = await queryBuilder({...}).exec.paginate({
  page: 1,
  limit: 25
});

// With type safety
interface Product {
  id: string;
  name: string;
  price: number;
}

const [products, meta] = await queryBuilder({
  // ...
}).exec.paginate<Product[]>({
  page: 1,
  limit: 25
});

//OR
const [products, meta] = await queryBuilder({
  // ...
}).exec<Product[]>().paginate({
  page: 1,
  limit: 25
});
// products is of type Product[]

• data: The paginated results (array of rows of type T)
• meta: Pagination metadata including page information and totals

Where Conditions

{
  // Simple equality
  status: 'ACTIVE',

  // Comparison operators
  age: { gt: 18 },
  price: { lte: 100 },

  // IN operator
  id: { in: [1, 2, 3] },

  // LIKE operator
  name: { like: '%john%' },

  // IS NULL/IS NOT NULL
  deletedAt: { is: null },

  // Logical operators
  AND: [
    { status: 'ACTIVE' },
    { role: 'ADMIN' }
  ],
  OR: [
    { status: 'PENDING' },
    { status: 'APPROVED' }
  ]
}

Pagination

// Using array destructuring for the result
const [data, meta] = await prisma
  .queryBuilder({
    // ... query options
  })
  .exec()
  .paginate({
    page: 1, // Current page (1-based, required)
    limit: 25, // Items per page (optional, default is 25)
  });

// The response is an array with two elements:
// - data: The paginated results
// - meta: Pagination metadata

// meta contains:
// {
//   isFirstPage: boolean,     // True if this is the first page
//   isLastPage: boolean,      // True if this is the last page
//   currentPage: number,      // Current page number
//   previousPage: number | null, // Previous page number or null if on first page
//   nextPage: number | null,  // Next page number or null if on last page
//   totalCount: number,       // Total number of items across all pages
//   page: number,             // Alias for currentPage
//   limit: number,            // Number of items per page
//   pageCount: number,        // Total number of pages
//   numberStarts: number      // The starting number for the current page (for display)
// }

Complex Query with Subqueries and Aggregations

const [data, meta] = await prisma
  .queryBuilder({
    select: [
      "p.id",
      "p.name",
      "p.code",
      "p.sku",
      "p.status",
      "pc.name AS category",

      // Total sold (from order_items)
      `(
      SELECT COALESCE(SUM(oi2.quantity)::INTEGER, 0)
      FROM order_items oi2
      WHERE oi2.product_id = p.id
    ) AS "totalSold"`,

      // Total sales amount
      `(
      SELECT COALESCE(SUM(oi2.total)::DECIMAL(10,2), 0)
      FROM order_items oi2
      WHERE oi2.product_id = p.id
    ) AS "totalSales"`,

      // Total purchased units
      `(
      SELECT COALESCE(SUM(pi2.units)::INTEGER, 0)
      FROM procurement_items pi2
      WHERE pi2.product_id = p.id
    ) AS "totalPurchased"`,

      // Total procurement cost
      `(
      SELECT COALESCE(SUM(pi2.total_cost_value)::DECIMAL(10,2), 0)
      FROM procurement_items pi2
      WHERE pi2.product_id = p.id
    ) AS "totalProcurement"`,

      // Current stock and price from active procurement
      `(
      SELECT pi2.total_units_left::INTEGER
      FROM procurement_items pi2
      WHERE pi2.product_id = p.id 
      AND pi2.status = 'ACTIVE'
    ) AS "currentStock"`,

      `(
      SELECT pi2.selling_price::DECIMAL(10,2)
      FROM procurement_items pi2
      WHERE pi2.product_id = p.id 
      AND pi2.status = 'ACTIVE'
    ) AS "currentPrice"`,
    ],
    table: "products p",
    join: [
      {
        table: "product_categories pc",
        type: "LEFT",
        conditions: { "pc.id": "p.category_id" },
      },
      {
        table: "procurement_items pi",
        type: "LEFT",
        conditions: { "pi.product_id": "p.id" },
      },
    ],
    where: {
      "p.company_id": companyId,
      "p.status": "ACTIVE",
      "pi.product_id": { neq: null },
      ...(branchId && { "pi.branch_id": branchId }),
      ...(categoryId && { "p.category_id": categoryId }),
    },
    groupBy: ["p.id", "pc.name"],
    orderBy: { totalSales: "DESC" },
  })
  .exec()
  .paginate({
    page: 1,
    limit: 25,
  });

Raw Query Execution

When you don't use the Prisma extension and don't want to pass the Prisma client to the query builder, you can get the raw SQL and execute it yourself:

import { PrismaClient } from "@prisma/client";
import { queryBuilder } from "@explita/prisma-query-builder";

const prisma = new PrismaClient();

// Get the raw SQL query
const query = queryBuilder({
  select: ["id", "name", "status"],
  table: "subjects",
  where: { status: "ACTIVE" },
});

// Execute the raw query with type safety
interface Subject {
  id: string;
  name: string;
  status: string;
}

const subjects = await prisma.$queryRaw<Subject[]>`${query.raw}`;

Global Configuration

You can configure the query builder globally when using it as a Prisma extension. This allows you to set default behaviors for all queries.

import { PrismaClient } from "@prisma/client";
import { queryBuilderExtension } from "@explita/prisma-query-builder";

// Configure the query builder with global settings
const prisma = new PrismaClient().$extend(
  queryBuilderExtension({
    // Database driver (default: 'postgres')
    driver: "postgres", // 'postgres' | 'mysql' | 'sqlite'

    // Pagination defaults
    pagination: {
      defaultPageSize: 25, // Default items per page
      maxPageSize: 100, // Maximum allowed items per page
    },

    // Query behavior
    logQueries: true, // Enable query logging
    throwOnEmpty: false, // Throw error when no results are found

    // Performance
    queryTimeout: 10000, // Query timeout in milliseconds (0 = no timeout)
    enableCache: true, // Enable query result caching
    cacheTtl: 60, // Cache time-to-live in seconds
  })
);

// Now all queries will use these settings by default
const [users] = await prisma
  .queryBuilder({
    select: ["id", "name"],
    table: "users",
  })
  .exec()
  .paginate({ page: 1 }); // Uses defaultPageSize from config

Configuration Options

• Database

  • driver: Database driver type ('postgres' | 'mysql' | 'sqlite'), defaults to 'postgres'

• Pagination

  • pagination.defaultPageSize: Default number of items per page (default: 25)
  • pagination.maxPageSize: Maximum allowed items per page (default: 100)

• Query Behavior

  • logQueries: Enable/disable query logging (default: false)
  • throwOnEmpty: Throw an error when no results are found (default: false)

• Performance

  • queryTimeout: Maximum query execution time in milliseconds (default: 0 = no timeout)
  • enableCache: Enable in-memory query result caching (default: false)
  • cacheTtl: Cache time-to-live in seconds (default: 60)

Per-Query Overrides

You can override global settings for individual queries:

// Override global settings for this query
const [recentOrders] = await prisma
  .queryBuilder({
    select: ["id", "total", "created_at"],
    table: "orders",
    // Override global settings
    logQueries: false, // Disable logging for this query
    queryTimeout: 5000, // Set a 5s timeout
  })
  .exec()
  .paginate({
    page: 1,
    limit: 50, // Can still be overridden in paginate()
  });

Caching Behavior

When caching is enabled, query results are cached in memory using the query parameters as the cache key. The cache respects the following:

• Only GET-like queries are cached by default
• Cache is automatically invalidated after the TTL expires
• Each query with different parameters gets its own cache entry

// First execution - caches the result
const result1 = await prisma
  .queryBuilder({
    select: ["id", "name"],
    table: "products",
    where: { status: "ACTIVE" },
  })
  .exec();

// Subsequent execution (within TTL) - returns cached result
const result2 = await prisma
  .queryBuilder({
    select: ["id", "name"],
    table: "products",
    where: { status: "ACTIVE" },
  })
  .exec();

Note: The cache is stored in memory and is not shared between server instances. For distributed caching, consider implementing a custom cache provider.

Getting Unsafe Raw SQL

For debugging or when you need to copy-paste a query into a database client, you can use the $unsafeQuery property. This returns the raw SQL with all values directly interpolated. Warning: This is not safe for use with user input as it's vulnerable to SQL injection.

const query = queryBuilder({
  select: ["id", "name", "email"],
  table: "users",
  where: {
    status: "ACTIVE",
    created_at: { gte: new Date("2023-01-01") },
  },
  limit: 10,
});

// Get the unsafe query with all values directly interpolated
const unsafeSql = query.$unsafeQuery;
console.log(unsafeSql);
// Example output: SELECT id, name, email FROM users WHERE status = 'ACTIVE' AND created_at >= '2023-01-01T00:00:00.000Z' LIMIT 10

// You can now copy-paste this into any SQL editor
// Note: This is for debugging/development purposes only
// Never use $unsafeQuery with user input

License

MIT

---

Built with ❤️ by Explita