❤
npm
Sign UpSign In

prisma-ts-select
TypeScript icon, indicating that this package has built-in type declarations

0.0.33 • Public • Published

prisma-ts-select

npm version license

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.

  1. Sources
    1. from
    2. join(s)
  2. where
  3. groupBy
  4. select
  5. having
  6. orderBy
  7. limit
  8. 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

Readme

Keywords

Package Sidebar

Install

npm i prisma-ts-select

Repository

github.com/adrianbrowning/prisma-ts-select

Homepage

Link to homepage or github readme here

Weekly Downloads

1

Version

0.0.33

License

MIT

Unpacked Size

1.59 MB

Total Files

27

Last publish

Collaborators

  • gingacodemonkey
Try on RunKit
Report malware