prisma-ts-select

v0.1.8

Published

11 days ago

![npm version](https://img.shields.io/npm/v/prisma-ts-select) ![build](https://github.com/adrianbrowning/prisma-ts-select/actions/workflows/CI.yml/badge.svg) ![license](https://img.shields.io/github/license/adrianbrowning/prisma-ts-select)

0High
0Medium
0Low

gingacodemonkey

prisma generator SQL type-safe typescript

prisma-ts-select

npm version license

Test Matrix

| | SQLite | MySQL | PostgreSQL | |----------------|--------|-------|------------| | Prisma v6 | | | | | Prisma v7 | | | |

Summary

prisma-ts-select is a TypeScript utility for enhancing the Prisma ORM. It simplifies the selection of fields in Prisma queries, ensuring type safety and reducing boilerplate when working with nested fields. Ideal for developers seeking an efficient, type-safe way to select data with Prisma in TypeScript.

[!NOTE] Fully tested on SQLite, MySQL, and PostgreSQL. Known exceptions:
HAVING on SQLite requires either an aggregate function in SELECT or a GROUP BY clause, and can only reference columns from SELECT or GROUP BY.

Installation

Install via:

npm install prisma-ts-select
pnpm add prisma-ts-select

Setup

Schema

Add both generators to prisma/schema.prisma. The output path is relative to the schema file.

generator prisma-ts-select {
  provider = "prisma-ts-select"
  output   = "../generated/prisma-ts-select"
}

Options

| Option | Default | Description | |--------|---------|-------------| | output | (required) | Output directory, relative to schema file | | packageName | prisma-ts-select-<hash> | Package name in the generated package.json. Defaults to a stable hash of the output path — set this when referencing the generated output as a workspace package. |

Example with packageName:

generator prisma-ts-select {
  provider    = "prisma-ts-select"
  output      = "../generated/prisma-ts-select"
  packageName = "my-app-prisma-types"
}

Then register as a workspace package and import by name:

npm / yarn — package.json:

{
  "workspaces": ["generated/prisma-ts-select"]
}

pnpm — pnpm-workspace.yaml:

packages:
  - generated/prisma-ts-select

Then add it as a dependency:

pnpm add my-app-prisma-types

import tsSelectExtend from 'my-app-prisma-types/extend-v7.js'

The client generator differs between Prisma versions:

Prisma v6

generator client {
  provider = "prisma-client-js"
  output   = "../generated/prisma"
}

Prisma v7

generator client {
  provider = "prisma-client"
  output   = "../generated/prisma"
}

Then generate:

pnpm exec prisma generate

Client — Prisma v6

No driver adapter needed. Import from the generated extend-v6.js:

import { PrismaClient } from './generated/prisma/index.js'
import tsSelectExtend from './generated/prisma-ts-select/extend-v6.js'

export const prisma = new PrismaClient().$extends(tsSelectExtend)

Client — Prisma v7

Prisma v7 requires a driver adapter. Install the adapter for your database and import from the generated extend-v7.js:

SQLite

pnpm add @prisma/adapter-better-sqlite3

import { PrismaClient } from './generated/prisma/client.ts'
import tsSelectExtend from './generated/prisma-ts-select/extend-v7.js'
import { PrismaBetterSqlite3 } from '@prisma/adapter-better-sqlite3'

const adapter = new PrismaBetterSqlite3({ url: process.env.DATABASE_URL })
export const prisma = new PrismaClient({ adapter }).$extends(tsSelectExtend)

MySQL

pnpm add @prisma/adapter-mariadb

import { PrismaClient } from './generated/prisma/client.ts'
import tsSelectExtend from './generated/prisma-ts-select/extend-v7.js'
import { PrismaMariaDb } from '@prisma/adapter-mariadb'

const url = new URL(process.env.DATABASE_URL!)
const adapter = new PrismaMariaDb({
  host: url.hostname, port: +url.port,
  user: url.username, password: url.password,
  database: url.pathname.slice(1),
})
export const prisma = new PrismaClient({ adapter }).$extends(tsSelectExtend)

PostgreSQL

pnpm add @prisma/adapter-pg

import { PrismaClient } from './generated/prisma/client.ts'
import tsSelectExtend from './generated/prisma-ts-select/extend-v7.js'
import { PrismaPg } from '@prisma/adapter-pg'

const adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL })
export const prisma = new PrismaClient({ adapter }).$extends(tsSelectExtend)

Supported DBs

Fully tested on:

SQLite
MySQL
PostgreSQL

Other DBs will be added when I have chance.

Usage

Generator

Set up the needed generator.


generator prisma-ts-select {
  provider = "prisma-ts-select"
}

Run the prisma generator to build the needed files

pnpm exec prisma generate --generator prisma-ts-select

After that is done, we can extend the PrismaClient:

import { PrismaClient } from "@prisma/client";
import prismaTSSelect from "prisma-ts-select/extend";

const prisma = new PrismaClient().$extends(prismaTSSelect);

Then we can use it like:

const results = await prisma.$from("<table>")
    .select("<column>")
    .run()
console.log(results);

API

The way the methods are chained, are heavily inspired by Dr Milan Milanović with his How To Learn SQL? > Queries Execution Order post.

Sources
1. from
2. join(s)
where
groupBy
select
having
orderBy
limit
offset

`.$from`

This takes the base table to work from.

Example

prisma.$from("User");

Example - With Table Alias

prisma.$from("User u");

SQL

FROM User AS `u`;

Note: Alias can be inline (space-separated) or as second parameter. Note: Table aliases are particularly useful for self-joins where you need to join a table to itself with different aliases.

`.$with`

Defines one or more Common Table Expressions (CTEs) that can be referenced in .join() calls or used directly as the base table via .from('cteName').

| Param | Description | |-------|-------------| | name | CTE name — used to reference the CTE in join() or from() | | query | Any query built with .$from() |

Chain .with(name, query) before .from() to define additional CTEs.

Example — CTE as joined table

const posts = prisma.$from("Post").select("id").select("authorId").select("title");

prisma.$with("pp", posts)
      .from("User")
      .join("pp", "authorId", "User.id")

SQL

WITH pp AS (
SELECT id, authorId, title 
FROM Post) 
FROM User 
JOIN pp ON pp.authorId = User.id;

Example — CTE as base table

Use .from('cteName') to query a CTE directly, without a real table as the base.

const posts = prisma.$from("Post").select("id").select("title");

prisma.$with("pp", posts)
      .from("pp")
      .select("pp.id")
      .select("pp.title")

SQL

WITH pp AS (
SELECT id, title 
FROM Post) 
SELECT pp.id AS `pp.id`, pp.title AS `pp.title` 
FROM pp;

Type safety: only CTEs declared in .$with() / .with() are accepted by .from(). Unknown CTE names are rejected at compile time.

Example — Multiple CTEs

const posts = prisma.$from("Post").select("id").select("authorId").select("title");
const users = prisma.$from("User").select("id").select("name");

prisma.$with("pp", posts)
      .with("uu", users)
      .from("User")
      .join("pp", "authorId", "User.id")

SQL

WITH pp AS (
SELECT id, authorId, title 
FROM Post), uu AS (
SELECT id, name 
FROM User) 
FROM User 
JOIN pp ON pp.authorId = User.id;

Table Aliases

Table aliases allow you to give tables shorter or more meaningful names in your queries. This is especially useful for:

Self-joins (joining a table to itself)
Long table names
Clearer query readability

Table Alias Syntax Options

Multiple syntaxes supported:

Inline in .$from(): prisma.$from("User u") - Note: Second parameter syntax .$from("User", "u") is NOT supported
Inline in .join(): .join("Post p", "authorId", "User.id")
Object syntax: .join({table: "Post", src: "authorId", on: "User.id", alias: "p"})

Table Aliases with Joins

Inline Alias Syntax

prisma.$from("User u")
      .join("Post p", "authorId", "u.id")
      .select("u.name")
      .select("p.title");

Object Syntax

prisma.$from("User u")
      .join({table: "Post", src: "authorId", on: "u.id", alias: "p"})
      .select("u.name")
      .select("p.title");

SQL

SELECT name, title 
FROM User AS `u` 
JOIN Post AS `p` ON p.authorId = u.id;

Note: The object syntax provides a foundation for future enhancements like multiple join conditions and complex WHERE-style conditions in joins.

Self-Joins with Aliases

Self-joins require aliases to distinguish between the different "instances" of the same table:

prisma.$from("User u1")
      .joinUnsafeTypeEnforced("User u2", "id", "u1.id")
      .select("u1.name", "user1Name")
      .select("u2.name", "user2Name");

SQL

SELECT u1.name AS `user1Name`, u2.name AS `user2Name` 
FROM User AS `u1` 
JOIN User AS `u2` ON u2.id = u1.id;

Table.* with Aliases

You can use the alias.* syntax to select all columns from an aliased table:

prisma.$from("User u")
      .select("u.*");

SQL

SELECT id, email, name, age 
FROM User AS `u`;

With joins:

prisma.$from("User u")
      .join("Post p", "authorId", "u.id")
      .select("u.*")
      .select("p.*");

SQL

SELECT u.id AS `u.id`, u.email AS `u.email`, u.name AS `u.name`, u.age AS `u.age`, p.id AS `p.id`, p.title AS `p.title`, p.content AS `p.content`, p.published AS `p.published`, p.createdAt AS `p.createdAt`, p.authorId AS `p.authorId`, p.lastModifiedById AS `p.lastModifiedById`, p.metadata AS `p.metadata` 
FROM User AS `u` 
JOIN Post AS `p` ON p.authorId = u.id;

Joins

Dialect Support

| Method | SQLite | MySQL | PostgreSQL | |--------|--------|-------|------------| | join / innerJoin / crossJoin / leftJoin | ✓ | ✓ | ✓ | | rightJoin | ✗ | ✓ | ✓ | | fullJoin | ✗ | ✗ | ✓ |

Each method has *UnsafeTypeEnforced and *UnsafeIgnoreType variants with the same dialect restrictions.

Nullability Semantics

| Join type | Effect on result type | |-----------|----------------------| | join / innerJoin / crossJoin | No nullable change — both sides guaranteed to match | | leftJoin | Joined table fields become T \| null | | rightJoin | Base table fields become T \| null | | fullJoin | Both sides become T \| null |

`.join`

Using the defined links (foreign keys) defined in the schema, provides a type-safe way of joining on tables.

Example

prisma.$from("User")
      .join("Post", "authorId", "User.id");

Type-safe join

SQL

The resulting SQL will look like:

FROM User 
JOIN Post ON Post.authorId = User.id;

Parameters

| column | Description | |-------------|----------------------------------------------------------------------------------------------------------------------------------------------------------------| | table | The table to join on (supports inline alias: "Post p" or "Post", "p"). TS autocomplete will show tables that can join with previously defined tables on. | | field | Column on table. TS autocomplete will show known columns that this table, can join with previously defined tables on. | | reference | Table.Column to a previously defined table (either the base, or another join), with a FK that is defined in the schema definition. | | where | (optional) Criteria added to the ON clause (ON a = b AND ...). Same syntax as .where(). Keys scoped to the joined table only. | | joinType | (optional) Join variant. One of "INNER", "LEFT", "LEFT OUTER", "RIGHT", "RIGHT OUTER", "FULL", "FULL OUTER", "CROSS". Default: plain JOIN. |

Alternative Syntaxes:

// Inline alias
.join("Post p", "authorId", "User.id")

// Object syntax
.join({
  table: "Post",
  src: "authorId",
  on: "User.id",
  alias: "p",           // optional
  joinType: "LEFT",     // optional
  where: { "Post.published": true }  // optional
})

Join Type

Control the SQL join variant via the joinType option:

prisma.$from("User")
      .join("Post", "authorId", "User.id", { joinType: "LEFT" })

FROM User 
LEFT JOIN Post ON Post.authorId = User.id;

CROSS JOIN has no ON clause — it is suppressed automatically:

prisma.$from("User")
      .joinUnsafeIgnoreType("Post", "id", "User.id", { joinType: "CROSS" })

FROM User 
CROSS JOIN Post;

joinType and where can be combined — where is ignored for CROSS:

prisma.$from("User")
      .join("Post", "authorId", "User.id", {
        joinType: "LEFT",
        where: { "Post.published": true }
      })

FROM User 
LEFT JOIN Post ON Post.authorId = User.id AND Post.published = true;

Join-level WHERE

Conditions placed on the ON clause instead of the top-level WHERE:

prisma.$from("User")
      .join("Post", "authorId", "User.id", { where: { "Post.published": true } })

FROM User 
JOIN Post ON Post.authorId = User.id AND Post.published = true;

Supports the same MongoDB-inspired operators as .where() — $AND, $OR, $NOT, $NOR:

prisma.$from("User")
      .join("Post", "authorId", "User.id", {
        where: {
          $AND: [
            { "Post.published": true },
            { "Post.id": { op: ">", value: 0 } }
          ]
        }
      })

FROM User 
JOIN Post ON Post.authorId = User.id AND (Post.published = true AND Post.id > 0);

Type safety: only "JoinedTable.field" keys are accepted — other tables' fields are rejected at compile time.

`.joinUnsafeTypeEnforced`

Unlike the .join command, this will allow you to join on columns that are not explicitly linked by a FK, but have the same type.

Example

prisma.$from("User")
      .joinUnsafeTypeEnforced("Post", "title", "User.name");

joinUnsafeTypeEnforced

SQL

The resulting SQL will look like:

FROM User 
JOIN Post ON Post.title = User.name;

Parameters

| column | Description | |-------------|----------------------------------------------------------------------------------------------------------------------------------------------------------------| | table | The table to join on (supports inline alias: "Post p" or "Post", "p"). TS autocomplete will show tables that can join with previously defined tables on. | | field | Column on table. TS autocomplete will show known columns that this table, can join with previously defined tables on. | | reference | Table.Column to a previously defined table (either the base, or another join), with a column that is of the same type. | | where | (optional) Criteria added to the ON clause. See Join-level WHERE. | | joinType | (optional) Join variant. See Join Type. |

`.joinUnsafeIgnoreType`

Unlike the .joinUnsafeIgnoreType command, this will allow you to join on columns that are not explicitly linked by a FK, and do not have the same type.

Example

prisma.$from("User")
      .joinUnsafeIgnoreType("Post", "id", "User.name");

joinUnsafeIgnoreType

SQL

The resulting SQL will look like:

FROM User 
JOIN Post ON Post.id = User.name;

Parameters

| column | Description | |-------------|----------------------------------------------------------------------------------------------------------------------------------------------------------------| | table | The table to join on (supports inline alias: "Post p" or "Post", "p"). TS autocomplete will show tables that can join with previously defined tables on. | | field | Column on table. TS autocomplete will show known columns that this table, can join with previously defined tables on. | | reference | Table.Column to a previously defined table (either the base, or another join). Referencing any column, of any type. | | where | (optional) Criteria added to the ON clause. See Join-level WHERE. | | joinType | (optional) Join variant. See Join Type. |

`.manyToManyJoin`

Joins through Prisma's implicit or explicit many-to-many junction tables. Automatically detects the junction table and join columns from the generated schema.

Example

prisma.$from("M2M_Post")
      .manyToManyJoin("M2M_Post", "M2M_Category");

SQL

FROM M2M_Post 
JOIN _M2M_CategoryToM2M_Post ON _M2M_CategoryToM2M_Post.B = M2M_Post.id 
JOIN M2M_Category ON M2M_Category.id = _M2M_CategoryToM2M_Post.A;

Parameters

| Param | Type | Description | |-------|------|-------------| | targetTable | string | Target table, optionally with alias: "M2M_Category" or "M2M_Category mc" | | options.refName | string? | Junction ref name — required when multiple M2M relations point to the same target | | options.source | string? | Explicit source as "alias.column" — useful when the source table is aliased |

With Alias

prisma.$from("M2M_Post")
      .manyToManyJoin("M2M_Post", "M2M_Category mc");

FROM M2M_Post 
JOIN _M2M_CategoryToM2M_Post ON _M2M_CategoryToM2M_Post.B = M2M_Post.id 
JOIN M2M_Category AS `mc` ON mc.id = _M2M_CategoryToM2M_Post.A;

Named Junction (`refName`)

Use refName when a model has multiple M2M relations to the same target:

prisma.$from("MMM_Post")
      .manyToManyJoin("MMM_Post", "MMM_Category", { refName: "M2M_NC_M1" });

FROM MMM_Post 
JOIN _M2M_NC_M1 ON _M2M_NC_M1.B = MMM_Post.id 
JOIN MMM_Category ON MMM_Category.id = _M2M_NC_M1.A;

Explicit Source (`source`)

Use source to pin the source alias and column when the source table is aliased:

prisma.$from("M2M_Post mp")
      .manyToManyJoin("mp", "M2M_Category mc");

FROM M2M_Post AS `mp` 
JOIN _M2M_CategoryToM2M_Post ON _M2M_CategoryToM2M_Post.B = mp.id 
JOIN M2M_Category AS `mc` ON mc.id = _M2M_CategoryToM2M_Post.A;

`.innerJoin`

Alias for .join — explicitly emits INNER JOIN. Same type-safe FK constraints.

Example

prisma.$from("User")
      .innerJoin("Post", "authorId", "User.id")

SQL

FROM User 
INNER JOIN Post ON Post.authorId = User.id;

`.innerJoinUnsafeTypeEnforced`

Same-type column join, INNER semantics.

prisma.$from("User")
      .innerJoinUnsafeTypeEnforced("Post", "title", "User.name")

FROM User 
INNER JOIN Post ON Post.title = User.name;

`.innerJoinUnsafeIgnoreType`

Any-column join, INNER semantics.

prisma.$from("User")
      .innerJoinUnsafeIgnoreType("Post", "id", "User.name")

FROM User 
INNER JOIN Post ON Post.id = User.name;

`.leftJoin`

FK-safe LEFT JOIN. Joined table fields become T | null in the result type.

Example

prisma.$from("User")
      .leftJoin("Post", "authorId", "User.id")

SQL

FROM User 
LEFT JOIN Post ON Post.authorId = User.id;

`.leftJoinUnsafeTypeEnforced`

Same-type column join, LEFT semantics.

prisma.$from("User")
      .leftJoinUnsafeTypeEnforced("Post", "title", "User.name")

FROM User 
LEFT JOIN Post ON Post.title = User.name;

`.leftJoinUnsafeIgnoreType`

Any-column join, LEFT semantics.

prisma.$from("User")
      .leftJoinUnsafeIgnoreType("Post", "id", "User.name")

FROM User 
LEFT JOIN Post ON Post.id = User.name;

`.crossJoin`

Produces a cartesian product — no ON clause. All dialects supported.

Example

prisma.$from("User")
      .crossJoin("Post")

SQL

FROM User 
CROSS JOIN Post;

`.crossJoinUnsafeTypeEnforced` / `.crossJoinUnsafeIgnoreType`

Type-permission variants — still emit CROSS JOIN with no ON clause (takes only a table argument).

prisma.$from("User")
      .crossJoinUnsafeTypeEnforced("Post")

FROM User 
CROSS JOIN Post;

`.rightJoin`

MySQL / PostgreSQL only — not supported by SQLite.

Base table fields become T | null. Use when the joined table drives the result set.

Example

prisma.$from("Post")
      .rightJoin("User", "id", "Post.authorId")

SQL

FROM Post RIGHT JOIN User ON User.id = Post.authorId;

`.rightJoinUnsafeTypeEnforced`

prisma.$from("Post")
      .rightJoinUnsafeTypeEnforced("User", "name", "Post.title")

`.rightJoinUnsafeIgnoreType`

prisma.$from("Post")
      .rightJoinUnsafeIgnoreType("User", "id", "Post.title")

`.fullJoin`

PostgreSQL only — not supported by SQLite or MySQL.

Both sides become T | null. Use for outer joins where either side may have no match.

Example

prisma.$from("User")
      .fullJoin("Post", "authorId", "User.id")

SQL

FROM User FULL JOIN Post ON Post.authorId = User.id;

`.fullJoinUnsafeTypeEnforced`

prisma.$from("User")
      .fullJoinUnsafeTypeEnforced("Post", "title", "User.name")

`.fullJoinUnsafeIgnoreType`

prisma.$from("User")
      .fullJoinUnsafeIgnoreType("Post", "id", "User.name")

Where

`.where`

The where syntax takes inspiration from how mongoDB does queries.

TypeSyntax

type WhereClause = {
  "Table.Column": <value>
    | [<value>, ...<value>[]]                          // scalar array → IN
    | { "op": "<condition>", "value": <value> }
    | [{ "op": "<condition>", "value": <value> }, ...]  // op-array → OR
  "$AND": [WhereClause, ...Array<WhereClause>],
  "$OR":  [WhereClause, ...Array<WhereClause>],
  "$NOT": [WhereClause, ...Array<WhereClause>],
  "$NOR": [WhereClause, ...Array<WhereClause>]
}

Operation types

| Op | Description | Supported Types | |-------------|-------------|-----------------------| | IN | | Numbers, String, Date | | NOT IN | | Numbers, String, Date | | BETWEEN | | Numbers, Date | | LIKE | | String | | NOT LIKE | | String | | IS NULL | | * | | IS NOT NULL | | * | | > | | Numbers, Date | | >= | | Numbers, Date | | < | | Numbers, Date | | <= | | Numbers, Date | | != | | Numbers, String, Date | | = | | Numbers, String, Date |

Examples

| Type | Description | Example | SQL | |--------------|--------------------------------------------------------------------------|------------------------------------------------------------------------------------------------------------------------------------------------------------------|-------------------------------------------------------------------| | Table.Column | A particular Table.Column name | .where({ "User.age": 20, "User.name": {op: "LIKE", value:"Stuart%"},}) | (User.age = 20 AND User.name LIKE "Stuart%") | | $AND | Will join all items with a AND | .where({ $AND:[ {"User.age": {op: ">", value:20}}, {"User.age": {op: "<", value:60}},]}) | (User.age > 20 AND User.age < 60) | | $OR | Will join all items with a OR | .where({ $OR:[ {"User.name": {op: "LIKE", value:"a%"}}, {"User.name": {op: "LIKE", value:"d%"}},]}) | (User.name LIKE "a%" OR User.name LIKE "d%") | | $NOT | Will wrap statement in a NOT (/*...*/) and join any items with a AND | .where({ $NOT:[ {"User.age": 20 }, { "User.age": {op: "=", value:60}, "User.name": "Bob", },]}) | (NOT (User.age = 20 AND (User.age = 60 AND User.name = "Bob"))) | | $NOR | Will wrap statement in a NOT (/*...*/) and join any items with a OR | .where({ $NOR:[ {"User.age": 20 }, { "User.age": {op: "!=", value:60}, "User.name": "Bob", },]}) | (NOT (User.age = 20 OR (User.age != 60 AND User.name = "Bob"))) | | Array (scalar) | Non-empty array of values → SQL IN | .where({ "User.name": ["Alice", "Bob"] }) | User.name IN ('Alice', 'Bob') | | Array (op-objects) | Non-empty array of op-objects → OR chain | .where({ "User.name": [{ op: "LIKE", value: "A%" }, { op: "LIKE", value: "B%" }] }) | (User.name LIKE 'A%' OR User.name LIKE 'B%') |

Columns

prisma.$from("User")
      .joinUnsafeIgnoreType("Post", "id", "User.name")
      .where({
        "User.age": 20,
        "User.name": {op: "LIKE", value: "Stuart%"},
      });

$AND

prisma.$from("User")
      .joinUnsafeIgnoreType("Post", "id", "User.name")
      .where({
        $AND: [
          {"User.age": {op: ">", value: 20}},
          {"User.age": {op: "<", value: 60}},
        ]
      });

$OR

prisma.$from("User")
      .joinUnsafeIgnoreType("Post", "id", "User.name")
      .where({
        $OR: [
          {"User.name": {op: "LIKE", value: "a%"}},
          {"User.name": {op: "LIKE", value: "d%"}},
        ]
      });

$NOT

prisma.$from("User")
      .joinUnsafeIgnoreType("Post", "id", "User.name")
      .where({
        $NOT: [
          {"User.age": 20},
          {
            "User.age": {op: "=", value: 60},
            "User.name": "Bob",
          },
        ]
      });

$NOR

prisma.$from("User")
      .joinUnsafeIgnoreType("Post", "id", "User.name")
      .where({
        $NOR: [
          {"User.age": 20},
          {
            "User.age": {op: "!=", value: 60},
            "User.name": "Bob",
          },
        ]
      });

Array (Scalar → IN)

prisma.$from("User")
      .joinUnsafeIgnoreType("Post", "id", "User.name")
      .where({
        "User.name": ["Alice", "Bob"],
      });

Array (Op-Object → OR)

prisma.$from("User")
      .joinUnsafeIgnoreType("Post", "id", "User.name")
      .where({
        "User.name": [
          { op: "LIKE", value: "A%" },
          { op: "LIKE", value: "B%" },
        ],
      });

`.whereNotNull`

Removes null from the column's type union and adds an IS NOT NULL condition to the WHERE clause. Type narrowing is reflected in all downstream .select() calls.

Example

prisma.$from("User")
      .join("Post", "authorId", "User.id")
      .whereNotNull("User.name")

whereNotNull

SQL

The resulting SQL will look like:

FROM User 
JOIN Post ON Post.authorId = User.id 
WHERE (User.name IS NOT NULL);

`.whereIsNull`

Narrows the column's type to exactly null and adds an IS NULL condition to the WHERE clause.

Example

prisma.$from("User")
      .join("Post", "authorId", "User.id")
      .whereIsNull("Post.content")

whereIsNull

SQL

The resulting SQL will look like:

FROM User 
JOIN Post ON Post.authorId = User.id 
WHERE (Post.content IS NULL);

`.where` — fn overload (SQL expressions)

Pass a callback instead of a criteria object to apply SQL functions as conditions. The callback receives the same select-fn context as .select(), giving access to upper, lower, length, count, avg, etc.

prisma.$from("User")
      .where(({ upper }) => [[upper('name'), { op: 'LIKE', value: 'John%' }]])
      .select("name")

SELECT name 
FROM User 
WHERE UPPER(name) LIKE 'John%';

Each array element is an [SQLExpr<T>, condition] pair — multiple pairs are AND-ed. The condition type is inferred from SQLExpr<T>: string expressions accept LIKE/NOT LIKE, numeric expressions accept >, <, BETWEEN, etc.

`.whereRaw`

When you want to write a complex where, or you just don't want the TypeSafety offered by the other methods, you can use .whereRaw.

Example

prisma.$from("User")
      .join("Post", "authorId", "User.id")
      .whereRaw("this is a raw where statement");

SQL

The resulting SQL will look like:

FROM User 
JOIN Post ON Post.authorId = User.id 
WHERE this is a raw 
where statement;

Group By

Will allow you to pass a list of columns, that haven been specified from the .$from and any .join methods.

Example

prisma.$from("User")
      .join("Post", "authorId", "User.id")
      .groupBy(["name", "Post.content"]);

groupBy

SQL

The resulting SQL will look like:

FROM User 
JOIN Post ON Post.authorId = User.id 
GROUP BY name, Post.content;

Selecting

`.selectDistinct`

Will add the keyword DISTINCT after the select.

Example

prisma.$from("User")
      .selectDistinct()
      .select("User.name");

SQL

The resulting SQL will look like:

SELECT DISTINCT name 
FROM User;

`.selectAll`

Works slightly differently to *. The limitation of * in JS, is that if you have 2 tables with the same name, you will only get back the last, based on a join. This method will explicitly list all the tables from the $from and .join. So you get the table.column in the respose.

Example - Single Table

prisma.$from("User")
      .selectAll();

SQL

The resulting SQL will look like:

SELECT id, email, name, age 
FROM User;

Example - Join table

prisma.$from("User")
      .join("Post", "authorId", "User.id")
      .selectAll();

SQL

The resulting SQL will look like:

SELECT User.id AS `User.id`, User.email AS `User.email`, User.name AS `User.name`, User.age AS `User.age`, Post.id AS `Post.id`, Post.title AS `Post.title`, Post.content AS `Post.content`, Post.published AS `Post.published`, Post.createdAt AS `Post.createdAt`, Post.authorId AS `Post.authorId`, Post.lastModifiedById AS `Post.lastModifiedById`, Post.metadata AS `Post.metadata` 
FROM User 
JOIN Post ON Post.authorId = User.id;

`.selectAllOmit`

Like .selectAll, but excludes specific columns. Accepts Table.column or bare column references.

Example - Single Table

prisma.$from("User")
      .selectAllOmit(["User.email"]);

SQL

SELECT id, name, age 
FROM User;

Example - Multiple Columns

prisma.$from("User")
      .selectAllOmit(["User.email", "User.age"]);

Example - With Join

prisma.$from("User")
      .join("Post", "authorId", "User.id")
      .selectAllOmit(["User.email", "Post.content"]);

Note: * and Table.* are not valid arguments — use Table.column or bare column references.

`.select`

You can supply either; *, Table.* OR table.field and then chain them together.

Example - `*`

prisma.$from("User")
      .select("*");

SQL

The resulting SQL will look like:

SELECT * 
FROM User;

Example - `Table.*` (Single Table)

prisma.$from("User")
      .select("User.*");

SQL

The resulting SQL will look like:

SELECT id, email, name, age 
FROM User;

Example - `Table.*` (With Join)

prisma.$from("User")
      .join("Post", "authorId", "User.id")
      .select("User.*")
      .select("Post.*");

SQL

The resulting SQL will look like:

SELECT User.id AS `User.id`, User.email AS `User.email`, User.name AS `User.name`, User.age AS `User.age`, Post.id AS `Post.id`, Post.title AS `Post.title`, Post.content AS `Post.content`, Post.published AS `Post.published`, Post.createdAt AS `Post.createdAt`, Post.authorId AS `Post.authorId`, Post.lastModifiedById AS `Post.lastModifiedById`, Post.metadata AS `Post.metadata` 
FROM User 
JOIN Post ON Post.authorId = User.id;

[!NOTE] When using Table.* with joins, all columns are automatically aliased with the table name prefix to avoid column name conflicts.

Example - Chained

prisma.$from("User")
      .select("name")
      .select("email");

SQL

The resulting SQL will look like:

SELECT name, email 
FROM User;

Example - Join + Chained

prisma.$from("User")
      .join("Post", "authorId", "User.id")
      .select("name")
      .select("Post.title");

SQL

The resulting SQL will look like:

SELECT name, title 
FROM User 
JOIN Post ON Post.authorId = User.id;

Example - Column Aliases

prisma.$from("User")
      .select("User.name", "username");

prisma.$from("User")
      .select("User.id", "userId")
      .select("User.email", "emailAddress");

prisma.$from("User")
      .select("User.id")
      .select("User.name", "username")
      .select("User.email");

SQL

The resulting SQL will look like:

SELECT User.name AS `username` 
FROM User;

SELECT User.id AS `userId`, User.email AS `emailAddress` 
FROM User;

SELECT id, User.name AS `username`, email 
FROM User;

Example - Aliases with Joins

prisma.$from("User")
      .join("Post", "authorId", "User.id")
      .select("User.name", "authorName")
      .select("Post.title", "postTitle");

SQL

The resulting SQL will look like:

SELECT User.name AS `authorName`, Post.title AS `postTitle` 
FROM User 
JOIN Post ON Post.authorId = User.id;

[!NOTE] When using column aliases, you can reference the alias in ORDER BY clauses. The returned type will use the alias names instead of the original column names.

Having

.having accepts two overloads — a criteria object (same syntax as .where) or a fn callback for SQL expressions and aggregate functions.

Criteria object

prisma.$from("User")
      .join("Post", "authorId", "User.id")
      .groupBy(["name", "Post.content"])
      .having({
        "User.name": {
          "op": "LIKE",
          "value": "bob%"
        }
      })
      .select("email");

SELECT email 
FROM User 
JOIN Post ON Post.authorId = User.id 
GROUP BY name, Post.content HAVING User.name LIKE 'bob%';

fn overload — aggregate functions

Pass a callback returning Array<[SQLExpr<T>, condition]> pairs. The callback receives the full select-fn context, including all aggregate and string functions.

`countAll()` with comparison op

prisma.$from("User")
      .join("Post", "authorId", "User.id")
      .groupBy(["User.name"])
      .having(({ countAll }) => [[countAll(), { op: '>', value: 1 }]])
      .select("User.name")

SELECT name 
FROM User 
JOIN Post ON Post.authorId = User.id 
GROUP BY User.name HAVING COUNT(*) > 1;

`count(col)` with bigint value

prisma.$from("User")
      .join("Post", "authorId", "User.id")
      .groupBy(["User.name"])
      .having(({ count }) => [[count('User.id'), { op: '>=', value: 2n }]])
      .select("User.name")

SELECT name 
FROM User 
JOIN Post ON Post.authorId = User.id 
GROUP BY User.name HAVING COUNT(User.id) >= 2;

String expr — `upper(col)` LIKE

prisma.$from("User")
      .join("Post", "authorId", "User.id")
      .groupBy(["User.name"])
      .having(({ upper }) => [[upper('User.name'), { op: 'LIKE', value: 'John%' }]])
      .select("User.name")

SELECT name 
FROM User 
JOIN Post ON Post.authorId = User.id 
GROUP BY User.name HAVING UPPER(User.name) LIKE 'John%';

Multiple pairs in one .having() call are AND-ed together. .having() can also be chained — each call appends an AND condition.

Order By

.orderBy, takes an array of column names, with the optional suffix of ASC or DESC.

Example

prisma.$from("User")
      .join("Post", "authorId", "User.id")
      .orderBy(["name", "Post.content DESC"]);

SQL

FROM User 
JOIN Post ON Post.authorId = User.id 
ORDER BY name, Post.content DESC;

Limit

.limit, takes the number of rows you would like to return.

Example

prisma.$from("User")
      .join("Post", "authorId", "User.id")
      .limit(1);

SQL

FROM User 
JOIN Post ON Post.authorId = User.id 
LIMIT 1;

Offset

.offSet, the number of rows to skip. Requires .limit to have been used first.

Example

prisma.$from("User")
      .join("Post", "authorId", "User.id")
      .limit(1)
      .offset(1);

SQL

FROM User 
JOIN Post ON Post.authorId = User.id 
LIMIT 1 
OFFSET 1;

Select Functions

Pass a callback to .select() to use SQL expressions and aggregate functions. The callback receives a context object with all available functions for the active dialect.

Shared (all dialects)

`lit(value)` — SQL literal

Produces a typed SQL literal from a JS value.

Example

prisma.$from("User")
      .select(({ lit }) => lit("hello"), "greeting");

`countAll()` — COUNT(*)

The most common aggregate. Always produces COUNT(*).

Example

prisma.$from("User")
      .select(({ countAll }) => countAll(), "total");

SQL

SELECT COUNT(*) AS `total` 
FROM User;

`count(col)` — COUNT(col)

prisma.$from("User")
      .select(({ count }) => count("User.id"), "cnt");

`countDistinct(col)` — COUNT(DISTINCT col)

prisma.$from("User")
      .select(({ countDistinct }) => countDistinct("User.id"), "cnt");

`sum(col)` / `avg(col)` / `min(col)` / `max(col)`

Standard numeric aggregates. Return types vary by dialect — sum and avg return Decimal on MySQL (matching Prisma's numeric precision model), number on SQLite and PostgreSQL. min/max always return T | null (NULL for empty sets) where T is the column's TypeScript type.

| Function | SQLite | MySQL | PostgreSQL | |---|---|---|---| | sum(col) | number | Decimal | number | | avg(col) | number | Decimal | number | | min(col) | T \| null | T \| null | T \| null | | max(col) | T \| null | T \| null | T \| null |

prisma.$from("User")
      .select(({ sum }) => sum("User.age"), "total");

String Functions (all dialects)

| Function | SQL | Returns | |---|---|---| | upper(col) | UPPER(col) | string | | lower(col) | LOWER(col) | string | | length(col) | LENGTH(col) | number | | trim(col) | TRIM(col) | string | | ltrim(col) | LTRIM(col) | string | | rtrim(col) | RTRIM(col) | string | | replace(col, from, to) | REPLACE(col, 'from', 'to') | string |

Note: MySQL LENGTH() returns byte-length (not char-length). For character-length on multi-byte strings use a dialect-specific fn.

DateTime Functions (all dialects)

All dialects provide these functions. Return types differ for year/month/day/hour/minute/second — see note below.

| Function | SQL (MySQL / PG / SQLite) | Returns | |---|---|---| | now() | NOW() / NOW() / datetime('now') | Date | | curDate() | CURDATE() / CURRENT_DATE / date('now') | Date | | year(col) | YEAR(col) / EXTRACT(YEAR FROM col)::integer / strftime('%Y', col) | number (SQLite: string) | | month(col) | MONTH(col) / EXTRACT(MONTH FROM col)::integer / strftime('%m', col) | number (SQLite: string) | | day(col) | DAY(col) / EXTRACT(DAY FROM col)::integer / strftime('%d', col) | number (SQLite: string) | | hour(col) | HOUR(col) / EXTRACT(HOUR FROM col)::integer / strftime('%H', col) | number (SQLite: string) | | minute(col) | MINUTE(col) / EXTRACT(MINUTE FROM col)::integer / strftime('%M', col) | number (SQLite: string) | | second(col) | SECOND(col) / EXTRACT(SECOND FROM col)::integer / strftime('%S', col) | number (SQLite: string) |

Note: year, month, day, hour, minute, and second return string on SQLite because strftime() always returns text (e.g. '2024', '03'). MySQL and PostgreSQL return number.

DateTime column args also accept SQLExpr<Date>, enabling composition:

prisma.$from("Post").select(({ year, now }) => year(now()), "y");

prisma.$from("User")
      .select(({ upper }) => upper("User.name"), "uname");

String fns accept a SQLExpr<string> as input, enabling composition:

prisma.$from("User")
      .select(({ lower }) => lower("User.name"), "lname");

prisma.$from("User")
      .select(({ replace }) => replace("User.email", "@example.com", ""), "handle");

Math Functions (all dialects)

| Function | SQL | Returns | |---|---|---| | abs(col) | ABS(col) | number | | ceil(col) | CEIL(col) | number | | floor(col) | FLOOR(col) | number | | round(col, decimals?) | ROUND(col) / ROUND(col, n) | number | | power(base, exp) | POWER(base, exp) | number | | sqrt(col) | SQRT(col) | number | | mod(col, divisor) | MOD(col, divisor) | number | | sign(col) | SIGN(col) | number | | exp(col) | EXP(col) | number |

Math fns accept SQLExpr<number> or a column reference, enabling composition:

// Absolute value of a literal
prisma.$from("User")
      .select(({ abs, lit }) => abs(lit(-5)), "absVal");

// Round to 2 decimal places
prisma.$from("User")
      .select(({ round, lit }) => round(lit(4.567), 2), "val");

// Compose: sqrt(power(x, 2))
prisma.$from("User")
      .select(({ sqrt, power }) => sqrt(power("User.age", 2)), "val");

Control Flow Functions (all dialects)

| Function | SQL | Returns | |---|---|---| | cond(criteria) | (WhereCriteria → SQL condition string) | SQLExpr<boolean> | | coalesce(...args) | COALESCE(a, b, ...) | SQLExpr<T> | | nullif(expr1, expr2) | NULLIF(a, b) | SQLExpr<T \| null> | | caseWhen(cases, elseVal?) | CASE WHEN ... THEN ... END | SQLExpr<T \| null> |

cond() converts a WhereCriteria object into a SQLExpr<unknown> — useful when you need a condition expression outside of a dedicated function. Note: $if()/iif() and caseWhen() all accept WhereCriteria directly, so cond() is rarely needed.

prisma.$from("User")
      .select(({ coalesce, lit }) => coalesce("User.email", lit("unknown")), "contact")

prisma.$from("User")
      .select(({ nullif, lit }) => nullif(lit(0), lit(0)), "val")

prisma.$from("User")
      .select(({ caseWhen, lit }) => caseWhen([
        { when: { age: { op: ">=", value: 18 } }, then: lit("adult") },
      ], lit("minor")), "status")

prisma.$from("User")
      .select(({ cond }) => cond({ age: { op: ">", value: 0 } }), "flag")

Combining with `.groupBy()`

prisma.$from("User")
      .join("Post", "authorId", "User.id")
      .groupBy(["User.name"])
      .select("User.name")
      .select(({ countAll }) => countAll(), "postCount");

SQL

SELECT User.name, COUNT(*) AS `postCount`
FROM User
JOIN Post ON Post.authorId = User.id
GROUP BY User.name;

MySQL-specific

| Function | SQL | Returns | |---|---|---| | distinct(col) | DISTINCT col | ColType (use inside avg, sum, count, groupConcat) | | groupConcat(col, sep?) | GROUP_CONCAT(col SEPARATOR sep) | string | | bitAnd(col) | BIT_AND(col) | number | | bitOr(col) | BIT_OR(col) | number | | bitXor(col) | BIT_XOR(col) | number | | stddev(col) | STDDEV(col) | number | | stddevSamp(col) | STDDEV_SAMP(col) | number | | variance(col) | VARIANCE(col) | number | | varSamp(col) | VAR_SAMP(col) | number | | jsonArrayAgg(col) | JSON_ARRAYAGG(col) | JSONValue | | jsonObjectAgg(key, val) | JSON_OBJECTAGG(key, val) | JSONValue | | concat(...cols) | CONCAT(a, b, ...) | string | | substring(col, start, len?) | SUBSTRING(col, start, len) | string | | left(col, n) | LEFT(col, n) | string | | right(col, n) | RIGHT(col, n) | string | | repeat(col, n) | REPEAT(col, n) | string | | reverse(col) | REVERSE(col) | string | | lpad(col, len, pad) | LPAD(col, len, 'pad') | string | | rpad(col, len, pad) | RPAD(col, len, 'pad') | string | | locate(substr, col) | LOCATE('substr', col) | number | | space(n) | SPACE(n) | string | | $if(cond, trueVal, falseVal) | IF(cond, a, b) | T | | ifNull(col, fallback) | IFNULL(col, fallback) | NonNullable<T> | | greatest(...args) | GREATEST(a, b, ...) | T \| null | | least(...args) | LEAST(a, b, ...) | T \| null | | dateAdd(col, n, unit) | DATE_ADD(col, INTERVAL n unit) | Date | | dateSub(col, n, unit) | DATE_SUB(col, INTERVAL n unit) | Date | | dateFormat(col, fmt) | DATE_FORMAT(col, 'fmt') | string | | dateDiff(d1, d2) | DATEDIFF(d1, d2) | number | | quarter(col) | QUARTER(col) | number | | weekOfYear(col) | WEEKOFYEAR(col) | number | | dayName(col) | DAYNAME(col) | string | | lastDay(col) | LAST_DAY(col) | Date | | pi() | PI() | number | | ln(x) | LN(x) | number | | log(x) | LOG(x) | number | | log2(x) | LOG2(x) | number | | log10(x) | LOG10(x) | number | | truncate(x, n) | TRUNCATE(x, n) | number | | rand(seed?) | RAND() / RAND(seed) | number |

Note: MySQL LOG(x) is natural log (ln). rand() returns a float in [0, 1).

Note: jsonArrayAgg and jsonObjectAgg require MySQL 5.7.22+.

PostgreSQL-specific

| Function | SQL | Returns | |---|---|---| | greatest(...args) | GREATEST(a, b, ...) | T | | least(...args) | LEAST(a, b, ...) | T | | distinct(col) | DISTINCT col | ColType (use inside avg, sum, count, stringAgg, arrayAgg) | | stringAgg(col, sep) | STRING_AGG(col, sep) | string | | arrayAgg(col) | ARRAY_AGG(col) | unknown[] | | stddevPop(col) | STDDEV_POP(col) | number | | stddevSamp(col) | STDDEV_SAMP(col) | number | | varPop(col) | VAR_POP(col) | number | | varSamp(col) | VAR_SAMP(col) | number | | boolAnd(col) | BOOL_AND(col) | boolean | | boolOr(col) | BOOL_OR(col) | boolean | | jsonAgg(col) | JSON_AGG(col) | JSONValue[] | | bitAnd(col) | BIT_AND(col) | number | | bitOr(col) | BIT_OR(col) | number | | jsonObjectAgg(key, val) | JSON_OBJECT_AGG(key, val) | JSONValue | | concat(...cols) | CONCAT(a, b, ...) | string | | substring(col, start, len?) | SUBSTRING(col, start, len) | string | | left(col, n) | LEFT(col, n) | string | | right(col, n) | RIGHT(col, n) | string | | repeat(col, n) | REPEAT(col, n) | string | | reverse(col) | REVERSE(col) | string | | lpad(col, len, pad) | LPAD(col, len, 'pad') | string | | rpad(col, len, pad) | RPAD(col, len, 'pad') | string | | initcap(col) | INITCAP(col) | string | | strpos(col, substr) | STRPOS(col, 'substr') | number | | splitPart(col, delimiter, field) | SPLIT_PART(col, 'delimiter', field) | string | | btrim(col, chars?) | BTRIM(col) / BTRIM(col, 'chars') | string | | md5(col) | MD5(col) | string | | extract(field, col) | EXTRACT(field FROM col) | number | | dateTrunc(unit, col) | DATE_TRUNC('unit', col) | Date | | age(ts1, ts2?) | AGE(ts1) / AGE(ts1, ts2) | string (PG interval mapped to string) | | toDate(text, fmt) | TO_DATE(text, 'fmt') | Date | | pi() | PI() | number | | ln(x) | LN(x) | number | | log(x) | LOG(x) | number | | logBase(base, x) | LOG(base, x) | number | | trunc(x, n?) | TRUNC(x) / TRUNC(x, n) | number | | div(x, y) | DIV(x, y) | number | | random() | RANDOM() | number |

Note: PG LOG(x) is log base 10 (unlike MySQL where it is natural log). random() returns a float in [0, 1).

unit for dateTrunc is one of: `'microseconds' | 'milliseconds' | 'second' | 'minute'

Published

Vulnerabilities

Links

Maintainers

Keywords

Readme

prisma-ts-select

Test Matrix

Summary

Installation

Setup

Schema

Options

Client — Prisma v6

Client — Prisma v7

Supported DBs

Usage

Generator

API

.$from

Example

Example - With Table Alias

SQL

.$with

Example — CTE as joined table

SQL

Example — CTE as base table

SQL

Example — Multiple CTEs

SQL

Table Aliases

Table Alias Syntax Options

Table Aliases with Joins

Inline Alias Syntax

Object Syntax

SQL

Self-Joins with Aliases

SQL

Table.* with Aliases

SQL

SQL

Joins

Dialect Support

Nullability Semantics

.join

Example

SQL

Parameters

Join Type

Join-level WHERE

.joinUnsafeTypeEnforced

Example

SQL

Parameters

.joinUnsafeIgnoreType

Example

SQL

Parameters

.manyToManyJoin

Example

SQL

Parameters

With Alias

Named Junction (refName)

Explicit Source (source)

.innerJoin

Example

SQL

.innerJoinUnsafeTypeEnforced

.innerJoinUnsafeIgnoreType

.leftJoin

Example

SQL

.leftJoinUnsafeTypeEnforced

.leftJoinUnsafeIgnoreType

.crossJoin

Example

SQL

.crossJoinUnsafeTypeEnforced / .crossJoinUnsafeIgnoreType

.rightJoin

`.$from`

`.$with`

`.join`

`.joinUnsafeTypeEnforced`

`.joinUnsafeIgnoreType`

`.manyToManyJoin`

Named Junction (`refName`)

Explicit Source (`source`)

`.innerJoin`

`.innerJoinUnsafeTypeEnforced`

`.innerJoinUnsafeIgnoreType`

`.leftJoin`

`.leftJoinUnsafeTypeEnforced`

`.leftJoinUnsafeIgnoreType`

`.crossJoin`

`.crossJoinUnsafeTypeEnforced` / `.crossJoinUnsafeIgnoreType`

`.rightJoin`

`.rightJoinUnsafeTypeEnforced`

`.rightJoinUnsafeIgnoreType`

`.fullJoin`

`.fullJoinUnsafeTypeEnforced`

`.fullJoinUnsafeIgnoreType`

`.where`

`.whereNotNull`

`.whereIsNull`

`.where` — fn overload (SQL expressions)

`.whereRaw`

`.selectDistinct`

`.selectAll`