Database

Prisma

This project uses Prisma as the database client. Prisma is an agnostic object-relational mapper (ORM) that can be used with various database engines, including Postgres, MySQL, and even NoSQL engines like MongoDB.

While Postgres is recommended, the choice of database is ultimately up to you.

Postgres

To get started, you need to install Postgres (PostgreSQL). If you already have it installed or have chosen another database connector, you can skip this section.

Windows

1. Visit the PostgreSQL Download page.
2. Download and install Postgres.

Alternatively, use Chocolatey which is like Homebrew for Windows.

MacOS

If you are using a Mac, the recommended installation method is Homebrew.

brew install postgresql

Initial User

On Windows, an initial user is usually created automatically. However, on MacOS and Linux, you need to create it manually. First, log into Postgres as a superuser:

sudo -u postgres psql

Then, create the initial user:

CREATE USER postgres WITH PASSWORD 'password';ALTER USER postgres WITH SUPERUSER;

Exit the Postgres prompt with:

\q

Connection String

Replace the connection string in the .env file with your Postgres credentials. Default credentials (username/password) are postgres and password.

DATABASE_URL=postgresql://postgres:password@localhost:5432/database?schema=public

Migrations

First make sure that the dependencies are installed:

npm install

To create the database and apply the migration files, run the following command:

npx prisma migrate dev

Prisma Studio

Prisma Studio is a useful tool for viewing and managing data in your local database. It serves as a lightweight alternative to tools like pgAdmin or DBeaver.

Visit Prisma Studio for more information.

Core Models

The most important models are:

• Account
• Contact
• Invitation
• Organization
• Session
• User

Model Relationships

• Organization -> Users
• Organization -> Contacts
• Organization -> Invitations
• User -> Sessions
• User -> Accounts

The Contact model is an example of an aggregate with many dependent models, illustrating a real-world scenario. In an e-commerce context, this could be renamed to Customer or something similar.

Multitenancy

Multitenancy is implemented by separating data in the same database using an organizationId, also commonly known as the tenantId.

The organizationId is on the session object, i.e.

const session = await auth();session.user.organizationId;

How to use Prisma

This guide covers basic database client operations, including querying, creating, updating, and deleting records. For more details about the Prisma client, refer to the official Prisma documentation.

Querying Records

To retrieve records from the database, use the findMany method on the Prisma client. This method accepts an object with optional where, orderBy, skip, and take fields, enabling you to filter, order, and paginate results.

Here's an example of querying all contacts of the current organization from the database:

import { prisma } from '@/lib/db/prisma';const contacts = await prisma.contact.findMany({  where: { organizationId: session.user.organizationId },  orderBy: { createdAt: 'desc' }});

Creating Records

To create a new record in the database, use the create method on the Prisma client.

import { prisma } from '@/lib/db/prisma';const contact = await prisma.contact.create({  data: {    organizationId: session.user.organizationId,    name: 'John Doe',    email: 'john.doe@gmail.com'  }});

Updating Records

To update an existing record in the database, use the update method on the Prisma client.

import { prisma } from '@/lib/db/prisma';const updatedContact = await prisma.contact.update({  where: {    organizationId: session.user.organizationId,    id: parsedInput.contactId  },  data: {    name: 'John Travolta'  }});

Deleting Records

To delete a record from the database, use the delete method on the Prisma client.

import { prisma } from '@/lib/db/prisma';const deletedContact = await prisma.contact.delete({  where: {    organizationId: session.user.organizationId,    id: parsedInput.contactId  }});