prisma-ts-select v0.0.33

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

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

SQL

The resulting SQL will look like:

FROM User
JOIN Post ON authorId = User.id;

Parameters

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

SQL

The resulting SQL will look like:

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

Parameters

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

SQL

The resulting SQL will look like:

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

Parameters

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

Examples

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

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

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

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

• Support specifying JOIN type issue#2
• Support Select Functions
  • Aggregation #4
  • String #5
  • Date & Time #6
  • Math #7
  • Control Flow #8
  • JSON #9
• Support a Many-To-Many join #19
• Select column alias #27
• Table name alias #28
• whereRaw supporting Prisma.sql

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