prisma-ts-select

• Summary
• Installation
• Supported DBs
• Usage
  • Generator
• API
  • .$from
    • Example
    • Example - With Table Alias
      • SQL
    • Example - Inline Alias Syntax
      • SQL
  • Table Aliases
    • Table Alias Syntax Options
    • Basic Table Alias
      • SQL
    • Table Aliases with Joins
      • Inline Alias Syntax
      • Object Syntax
      • SQL
    • Self-Joins with Aliases
      • SQL
    • Table.* with Aliases
      • SQL
      • SQL
  • 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 - Table.* (Single Table)
      • SQL
    • Example - Table.* (With Join)
      • SQL
    • Example - Chained
      • SQL
    • Example - Join + Chained
      • SQL
    • Example - Column Aliases
      • SQL
    • Example - Aliases with Joins
      • 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.

Example

prisma.$from("User")

Example - With Table Alias

prisma.$from("User", "u")

SQL

FROM User AS u

Note: Table aliases are particularly useful for self-joins where you need to join a table to itself with different aliases.

Example - Inline Alias Syntax

prisma.$from("User u")

SQL

FROM User AS u

Note: Alias can be inline (space-separated) or as second parameter.

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")``
• Inline in .join(): .join("Post p", "authorId", "User.id")
• Object syntax: .join({table: "Post", src: "authorId", on: "User.id", alias: "p"})

Basic Table Alias

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

SQL

SELECT name, email FROM User AS u;

Table Aliases with Joins

Inline Alias Syntax

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

Object Syntax

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

SQL

SELECT name, title
FROM User AS u
JOIN Post AS p ON 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", "id", "u1.id", "u2")
  .select("u1.name", "user1Name")
  .select("u2.name", "user2Name")
  .run();

SQL

SELECT u1.name AS `user1Name`, u2.name AS `user2Name`
FROM User AS u1
JOIN User AS u2 ON User.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.*")
  .run();

SQL

SELECT u.id, u.email, u.name FROM User AS u;

With joins:

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

SQL

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

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

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

Alternative Syntaxes:

// Inline alias
.join("Post p", "authorId", "User.id")
    
// Object syntax
.join({
  table: "Post",
  src: "authorId",
  on: "User.id",
  alias: "p"  // optional
})

.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

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

.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

| 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

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

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; *, 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 User.id, User.email, User.name
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`,
       Post.id AS `Post.id`,
       Post.title AS `Post.title`,
       Post.content AS `Post.content`,
       Post.published AS `Post.published`
FROM User
JOIN Post ON 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, Post.title
FROM User
JOIN Post ON authorId = User.id;

Example - Column Aliases

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

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

// Mixing aliased and non-aliased columns
prisma.$from("User")
      .select("User.id")
      .select("User.name", "username")
      .select("User.email");

SQL

The resulting SQL will look like:

-- Basic alias
SELECT User.name AS `username` FROM User;

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

-- Mixed
SELECT User.id, User.name AS `username`, User.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 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 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