Prisma DataTable

A powerful and type-safe DataTable integration for Prisma ORM with advanced filtering, sorting, pagination, global search, and relation support.

Features

✨ Advanced Filtering - 12 filter types (eq, neq, like, gt, gte, lt, lte, in, notIn, between, isNull, isNotNull)
🔍 Global Search - Search across multiple fields with a single query
📊 Smart Pagination - Built-in pagination with metadata
🔗 Relation Support - Filter and search through nested relations
📦 Column Selection - Control which fields to return
🛡️ SQL Injection Safe - All queries are parameterized through Prisma
📝 TypeScript First - Full type safety with TypeScript
🎯 Array Relations - Proper support for one-to-many relations

Installation

npm install @your-org/prisma-datatable
# or
yarn add @your-org/prisma-datatable
# or
pnpm add @your-org/prisma-datatable

Quick Start

1. Define Your Schema Config

import { SchemaConfig } from "@your-org/prisma-datatable";

const USER_SCHEMA: SchemaConfig = {
  model: "User",
  searchableFields: ["name", "email", "posts.title"],
  relations: {
    posts: {
      model: "Post",
      isArray: true, // One-to-many relation
    },
  },
};

2. Create DataTable Endpoint

import { DTReqAdapter, DTResAdapter } from "@your-org/prisma-datatable";
import { prisma } from "./prisma";

async function getUsersDataTable(request: DataTableRequest) {
  const adapter = new DTReqAdapter(request, USER_SCHEMA);
  const prismaQuery = adapter.toPrismaQuery();

  const [data, total] = await Promise.all([
    prisma.user.findMany(prismaQuery),
    prisma.user.count({ where: prismaQuery.where }),
  ]);

  return DTResAdapter.fromPrisma(data, total, request);
}

3. Use in Your API

// Express example
app.post("/users/datatable", async (req, res) => {
  const result = await getUsersDataTable(req.body);
  res.json(result);
});

// Fastify example
fastify.post("/users/datatable", async (request, reply) => {
  const result = await getUsersDataTable(request.body);
  return reply.send(result);
});

Request Examples

Basic Pagination

{
  "page": 1,
  "perPage": 10
}

Global Search

{
  "globalSearch": "john",
  "page": 1,
  "perPage": 10
}

Searches across all fields defined in searchableFields.

Advanced Filtering

{
  "filters": {
    "status": { "type": "eq", "value": "active" },
    "age": { "type": "gte", "value": 18 },
    "email": { "type": "like", "value": "@gmail.com" },
    "posts.published": { "type": "eq", "value": true }
  }
}

Sorting

{
  "sort": [
    { "column": "createdAt", "direction": "desc" },
    { "column": "name", "direction": "asc" }
  ]
}

Include Relations

{
  "includeRelations": ["posts", "profile"],
  "page": 1
}

Select Specific Columns

{
  "selectColumns": ["id", "name", "email", "posts.title", "posts.createdAt"]
}

Combined Example

{
  "page": 1,
  "perPage": 20,
  "globalSearch": "developer",
  "filters": {
    "status": { "type": "eq", "value": "active" },
    "posts.published": { "type": "eq", "value": true }
  },
  "sort": [{ "column": "createdAt", "direction": "desc" }],
  "includeRelations": ["posts", "profile"]
}

Response Format

{
  "data": [...],
  "meta": {
    "current_page": 1,
    "per_page": 10,
    "from": 1,
    "to": 10,
    "total": 45,
    "last_page": 5
  }
}

Filter Types

| Type | Description | Example | | ----------- | --------------------------- | -------------------------------------------------- | | eq | Equals | { "type": "eq", "value": "active" } | | neq | Not equals | { "type": "neq", "value": "deleted" } | | like | Contains (case-insensitive) | { "type": "like", "value": "john" } | | gt | Greater than | { "type": "gt", "value": 18 } | | gte | Greater than or equal | { "type": "gte", "value": 18 } | | lt | Less than | { "type": "lt", "value": 65 } | | lte | Less than or equal | { "type": "lte", "value": 65 } | | in | In array | { "type": "in", "value": ["active", "pending"] } | | notIn | Not in array | { "type": "notIn", "value": ["deleted"] } | | between | Between range | { "type": "between", "value": [18, 65] } | | isNull | Is null | { "type": "isNull" } | | isNotNull | Is not null | { "type": "isNotNull" } |

Schema Config Options

interface SchemaConfig {
  model: string; // Prisma model name
  jsonFields?: string[]; // JSON field names for JSON filtering
  relations?: Record<string, SchemaConfig>; // Nested relations
  searchableFields?: string[]; // Fields for global search
  isArray?: boolean; // Mark as array relation (one-to-many)
}

TypeScript Support

Full TypeScript support with type inference:

import type {
  DataTableRequest,
  DataTableResponse,
} from "@your-org/prisma-datatable";

// Type-safe request
const request: DataTableRequest = {
  page: 1,
  perPage: 10,
  filters: {
    status: { type: "eq", value: "active" },
  },
};

// Type-safe response
const response: DataTableResponse<User> = await getUsersDataTable(request);

Security

• ✅ SQL Injection Safe - All values are parameterized through Prisma
• ✅ Type Validation - Use Zod or similar for request validation
• ✅ Field Whitelisting - Control searchable fields via schema config

License

MIT

Contributing

Contributions are welcome! Please open an issue or submit a pull request.

Support

• GitHub Issues: https://github.com/yourusername/prisma-datatable/issues
• Documentation: https://github.com/yourusername/prisma-datatable#readme