Prisma-ts-select NPM

prisma-ts-select

npm version license

prisma-ts-select

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

This has been built mostly around MySQL. Most methods should work across the board. Known exceptions include:
HAVING
SQLite
Requires you to have either an aggregate function in the SELECT or make use of GROUP BY
Can only use columns that are specified in SELECT or GROUP BY

Installation

Install via:

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

Supported DBs

I have tested this currently on the following databases.

SQLite
MySQL

Most items should also work for

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.

Joins

`.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 authorId = User.id;

Parameters

column	Description
`table`	The table to join on. 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.

`.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. 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.

`.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. 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

`.where`

The where syntax takes inspiration from how mongoDB does queries.

TypeSyntax

type WhereClause = {
  "Table.Column": <value> | { "op": "<condition>", "value": <value> }
  "$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

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")))`

Columns

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

$AND

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

$OR

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

$NOT

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

$NOR

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

`.whereNotNull`

This will remove the null type from the union of types of the current table column. To use .whereNotNull, you need to add it before a .where.

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 authorId = User.id
WHERE User.name IS NOT NULL;

`.whereIsNull`

This will remove the NonNull type from the union of types of the current table column. To use .whereIsNull, you need to add it before a .where.

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 authorId = User.id
WHERE Post.content IS NULL;

`.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 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 authorId = User.id
GROUP BY name, Post.content;

Selecting

`.selectDistinct`

Will add the keyword DISTINCT after the select.

Example

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

SQL

The resulting SQL will look like:

SELECT DISTINCT
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
FROM User

Example - Join table

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

SQL

The resulting SQL will look like:

SELECT User.id, User. email, User.name, Post.id, Post.title, Post.content, Post.published, Post.author, Post.authorId, Post.LastModifiedBy, Post.lastModifiedById
FROM User 
JOIN Post ON authorId = User.id

`.select`

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

Example - `*`

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

SQL

The resulting SQL will look like:

SELECT *
FROM User;

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");

!NOTE

Support for Table.* isn't complete yet. This will be tracked here.

SQL

The resulting SQL will look like:

SELECT name, email
FROM User;

Having

.having uses the same syntax as .where. Please see the previous section for details.

Example

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

prisma.$from("User")
       .join("Post", "authorId", "User.id")
       .having({
           "User.name": {
               "op": "LIKE",
               "value": "stuart%"
           }
       });

SQL

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

FROM User
JOIN Post ON authorId = User.id
HAVING (User.name LIKE 'stuart%');

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 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 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 authorId = User.id
LIMIT 1
OFFSET 1

Future updates

Changelog / Versioning

Changelog is available here. We use semantic versioning for versioning.

License

This project is licensed under the MIT License. See the LICENSE file for details.

Things of note!!!!

remove typeof from
- type _db = DeepWriteable<typeof DB>;
- }[keyof typeof DB];
Merge Items missing //@ts-expect-error - might not be needed
groupBy -> having,
- missing @deprecated
- ts-exptect-error - might not be needed
GetColsFromTableType missing ts-expect-error - might not be needed
DB needs to be in the same file.

prisma-ts-select

Install

npm i prisma-ts-select
pnpm add prisma-ts-select

Setup

Extract

Usage

prisma generator SQL type-safe typescript

@prisma/generator-helper @prisma/internals ts-pattern

0.0.33

1 month ago

prisma-ts-select

Summary

Installation

Supported DBs

Usage

Generator

API

.$from

Joins

.join

Example

SQL

Parameters

.joinUnsafeTypeEnforced

Example

SQL

Parameters

.joinUnsafeIgnoreType

Example

SQL

Parameters

Where

.where

TypeSyntax

Operation types

Examples

Columns

$AND

$OR

$NOT

$NOR

.whereNotNull

Example

SQL

.whereIsNull

Example

SQL

.whereRaw

Example

SQL

Group By

Example

SQL

Selecting

.selectDistinct

Example

SQL

.selectAll

Example - Single Table

SQL

Example - Join table

SQL

.select

Example - *

SQL

Example - Chained

SQL

Example - Join + Chained

SQL

Having

Example

SQL

Order By

Example

SQL

Limit

Example

SQL

Offset

Example

SQL

Future updates

Changelog / Versioning

License

prisma-ts-select

Install

Setup

Extract

Usage

`.$from`

`.join`

`.joinUnsafeTypeEnforced`

`.joinUnsafeIgnoreType`

`.where`

`.whereNotNull`

`.whereIsNull`

`.whereRaw`

`.selectDistinct`

`.selectAll`

`.select`

Example - `*`