Overview

This document relates to the files in the prisma directory, as well as functions within src/pages/api

Prisma is an Object Relational Mapper (ORM). It is capable of automatically reading the SQL database schema and converting it into a TypeScript schema. It also provides an interface to make type-safe database queries using TypeScript.

Generating a Schema

The first step to using Prisma is to create a schema file. The file for this application is located here: prisma/schema.prisma.

In this file we provide the database connection string by reading from an environment variable:

datasource db {
  provider = "sqlserver"
  url      = env("DATABASE_URL")
}

We then need to set the environment variable DATABASE_URL to the database connection string.

In the Azure Portal you can find the connection string under the SQL database -> connection strings -> JDBC -> JDBC (SQL authentication). Just remove the "jdbc:" prefix and insert the database password.

When working locally, we can add a file named .env to the root directory to simulate environment variables. Environment variables declared in this file are automatically made available to Prisma. Declare an environment variable using a key-value pair separated by =.

Example .env file:

DATABASE_URL="sqlserver://life-server.database.windows.net:1433;database=life-database;user=chami033@life-server;password={your_password_here};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;"

With the environment variable set, Prisma will be able to perform queries on the database.

Updating the Schema

To read the database schema run

npx prisma db pull

The schema will be generated or updated within prisma/schema.prisma

Then run

npx prisma generate

to generate a Prisma Client module (it is generated as a node module within /node_modules/.prisma/client). This will generate TypeScript types from the schema.

Example of the account type generated from the database schema:

export type account = {
  id: number;
  login_email: string;
  microsoft_id: string | null;
  first_name: string;
  last_name: string;
  is_admin: boolean;
  last_login: Date | null;
};

Making Queries

See docs on Prisma's Query Language

Prisma provides an interface for making type-safe database queries from our backend Node.js server. We just need to instantiate and export the client:

prisma/prisma-client.ts

const db = new PrismaClient();
export default db;

Example of registering an account using Prisma:

src/pages/api/register-account.ts

type RegisterAccountParams = {
  login_email: string;
  first_name: string;
  last_name: string;
  is_admin?: boolean;
  is_member?: boolean;
};

function registerAccount(params: RegisterAccountParams) {
  return db.account.create({
    data: {
      login_email: params.login_email.toLocaleLowerCase(),
      is_admin: params.is_admin,
      first_name: params.first_name,
      last_name: params.last_name,
      member: params.is_member
        ? { create: { date_joined: new Date() } }
        : undefined,
    },
  });
}

This function creates an entry in the account table and returns the new entry.

Notice how we can also create an entry in the member table. Prisma knows member has a foreign key pointing to an account id so it allows this operation, populating the foreign key with the new id that was just created.

Typing the Return Value

We can create complex return types by picking what properties to return using the select and include properties. We can even include properties from connected tables. Prisma will automatically generate nested JavaScript objects to represent the relationships.

For example:

src/pages/api/account/[id].ts

function getAccountById(id: number) {
  return db.account.findUnique({
    where: { id },
    include: includeAllAccountInfo,
  });
}

includeAllAccountInfo is an object listing all the properties and relationships we want to include. See prisma/helpers.ts for its definition. These helper objects are explained later in this document.

Here is what the returned object looks like:

{
    id: 131,
    login_email: '[email protected]',
    microsoft_id: '9e481b6a940c09c4',
    first_name: 'Christopher',
    last_name: 'Hamilton',
    is_admin: true,
    last_login: 2022-12-22T00:00:00.000Z,
    member: {
      id: 415,
      account_id: 131,
      faculty_id: 3,
      type_id: 7,
      work_email: '',
      work_phone: '',
      about_me_fr: '',
      about_me_en: '',
      website_link: '',
      twitter_link: '',
      facebook_link: '',
      linkedin_link: '',
      tiktok_link: '',
      cv_link: '',
      address: null,
      city: null,
      province: null,
      country: null,
      postal_code: null,
      mobile_phone: null,
      date_joined: 2022-12-22T00:00:00.000Z,
      is_active: true,
      last_active: null,
      faculty: { id: 3, name_en: 'Fac. Engineering', name_fr: 'Fac. Génie' },
      member_type: { id: 7, name_en: 'Undergrad', name_fr: 'Undergrad' },
      partnership_member_org: [],
      current_promotion_strategy: [],
      desired_partnership: null,
      desired_promotion_strategy: [],
      has_keyword: [
        {
          member_id: 415,
          keyword_id: 45,
          keyword: { id: 45, name_en: '3D modeling', name_fr: null }
        },
        {
          member_id: 415,
          keyword_id: 738,
          keyword: {
            id: 738,
            name_en: 'web development',
            name_fr: 'développement web'
          }
        }
      ],
      insight: null,
      problem: [
        {
          id: 365,
          member_id: 415,
          name_en: 'Problem 1 EN',
          name_fr: 'Problem 1 FR'
        }
      ]
    }
  }

But now the issue is: we will be using this object in the frontend, we want the object's type defined somewhere.

Prisma & Typescript are able to calculate the return type based on the database schema and the input parameters. You can see this via Intellisense by hovering over the function in an IDE like VS Code.

We see something like:

(account & {
    member: (member & {
        partnership_member_org: (partnership_member_org & {
            organization: organization;
        })[];
        current_promotion_strategy: (current_promotion_strategy & {
            promotion_strategy: promotion_strategy;
        })[];
        desired_partnership: desired_partnership | null;
        desired_promotion_strategy: (desired_promotion_strategy & {
            promotion_strategy: promotion_strategy;
        })[];
        faculty: faculty | null;
        has_keyword: (has_keyword & {
            keyword: keyword;
        })[];
        insight: insight | null;
        member_type: member_type | null;
        problem: problem[];
    }) | null;
}) | null

That's the type we want, but we definitely don't want to write the whole thing out and have to refactor it when something changes. We can use Typescript Utility Types to extract the return type from the function.

src/pages/api/account/[id].ts

export type AccountDBRes = Awaited<ReturnType<typeof getAccountById>>;

We export the type so the frontend can import it.

Date Type Caveat

There is one caveat: the Date type is converted to a string when sent from backend to frontend. This is because Prisma implicitly converts the Date SQL type to a JavaScript Date object 👎. Then the data is stringified to be sent to the frontend via an HTTP response, which converts the Date object to an ISO string like "2022-12-22T00:00:00.000Z".

So we have a mismatch between the type we get from Prisma and the type the frontend gets. There are 2 options to handle this:

1. Convert every date string to a Date object when received in the frontend.
2. Modify the return type to change all Date types to string.

Option 2 was used to solve this, since most dates will just be output to HTML as strings anyway. The property types can be overwritten by using the Omit utility type and an intersection &. NonNullable is also necessary as this does not apply when the return value is null.

src/pages/api/account/[id].ts

export type AccountRes = Omit<
  NonNullable<AccountDBRes>,
  "member" | "last_login"
> & {
  member: PrivateMemberRes | null; // Here we're reusing the return type of another endpoint where we performed the same steps
  last_login: string | null;
};

Now the frontend can safely use this object type. Intellisense will provide auto-completion and typechecking in realtime.

Helper Objects

This section describes the purpose of prisma/helpers.ts

There are many cases where we want Prisma to include many properties and relationships. If we write all of these out in every API endpoint, then refactoring in the event of a database schema change will take a long time. This is solved by creating helper objects which list common properties and relationships we want to select or include.

For example, to select only public data for a member, the Prisma query looks like:

function getPublicMemberInfo(id: number) {
  return db.member.findUnique({
    where: { id },
    select: {
      id: true,
      account: { select: { first_name: true, last_name: true } },
      is_active: true,
      about_me_en: true,
      about_me_fr: true,
      work_email: true,
      work_phone: true,
      website_link: true,
      twitter_link: true,
      linkedin_link: true,
      cv_link: true,
      facebook_link: true,
      tiktok_link: true,
      faculty: true,
      problem: true,
      member_type: true,
      has_keyword: { select: { keyword: true } },
    },
  });
}

We can extract the select object to cut down on refactoring work:

export const selectPublicMemberInfo = {
  id: true,
  account: { select: { first_name: true, last_name: true } },
  is_active: true,
  about_me_en: true,
  about_me_fr: true,
  work_email: true,
  work_phone: true,
  website_link: true,
  twitter_link: true,
  linkedin_link: true,
  cv_link: true,
  facebook_link: true,
  tiktok_link: true,
  faculty: true,
  problem: true,
  member_type: true,
  has_keyword: { select: { keyword: true } },
} as const;

function getPublicMemberInfo(id: number) {
  return db.member.findUnique({
    where: { id },
    select: selectPublicMemberInfo,
  });
}

But we lose something important by doing this: Excess Property Checking

Before extracting the object, adding an excess property to the select object would cause TypeScript to raise an error:

function getPublicMemberInfo(id: number) {
  return db.member.findUnique({
    where: { id },
    select: {
      id: true,
      foo: "bar", // Error:
    },
  });
}

// Error Type '{ id: true; foo: string; }' is not assignable to type 'memberSelect'.
//   Object literal may only specify known properties, and 'foo' does not exist in type 'memberSelect'.

This is an important catch since excess properties result in Prisma runtime errors. It's common that a property name will be deleted or have its name change, we want to be able to locate all areas that need to be refactored at compile time.

But excess property checking is only done when the object is first instantiated.

We can achieve the type check by typing the object on instantiation:

const selectPublicMemberInfo: Prisma.memberSelect = {
  id: true,
  foo: "bar", // Compile error 😃
} as const;

But this actually breaks the return type of the query:

const selectPublicMemberInfo: Prisma.memberSelect = {
  id: true,
  account: { select: { first_name: true, last_name: true } },
} as const;

// Return type is {} | null 😡
function getPublicMemberInfo(id: number) {
  return db.member.findUnique({
    where: { id },
    select: selectPublicMemberInfo,
  });
}

This is because Prisma relies on the exact values of the key-value pairs within the object to calculate the return type. So we need to let Typescript infer the type when we create the object. But excess properties in this object will not be caught and will cause a runtime error.

export const selectPublicMemberInfo = {
  id: true,
  foo: "bar", // 😡 No compile error
} as const;

function getPublicMemberInfo(id: number) {
  return db.member.findUnique({
    where: { id },
    select: selectPublicMemberInfo, // 💥 Runtime error
  });
}

To avoid this, we create an extra check for TypeScript to perform. This utility type was modified from this stackoverflow answer

type CheckKeysAreValid<T, ValidProps> = Exclude<
  keyof T,
  keyof ValidProps
> extends never
  ? T
  : "Invalid keys" | Exclude<keyof T, keyof ValidProps>;

What this type does is check if T has any excess properties compared to ValidProps.

If there are no excess properties, resolve to T.

Else resolve to the string "Invalid Keys" Union with { All Invalid Keys }. This is so the error message makes some sense.

The trick is to make a second variable, which we type as CheckKeysAreValid<typeof helperObject, ValidProps>.

Then we attempt to assign helperObject to it.

If there are no excess properties, the two variables will have the same type, so no error. That type will also be the inferred type of the object, so our return types will still resolve properly.

If there are excess properties, we will be attempting to assign an object to a string union, so we get an error, as shown below.

const _selectPublicMemberInfo = {
  id: true,
  foo: "bar",
} as const;

export const selectPublicMemberInfo: CheckKeysAreValid<
  // const selectPublicMemberInfo: "foo" | "Invalid keys"
  typeof _selectPublicMemberInfo,
  Prisma.memberSelect
> = _selectPublicMemberInfo;

// Error: Type '{ readonly id: true; readonly foo: "bar"; }' is not assignable to type '"foo" | "Invalid keys"'.

In this way we get excess property checking and still maintain the return type of our query 🎉 🎉