Create a database

The first thing you need is a database. SupaSaas uses a database ORM which makes it very easy to configure a database and interact with it.

It also means you can choose any database provider you prefer. The ORM that I use is the very popular Prisma.

Prisma has baked in support for the most popular types of database, including PostgreSQL, MySQL and MongoDB (view full list here)

I always go with Postgres databases since they're open source, very secure, stable and popular

Supabase signup

Sign up for an account with Supabase

In this example we'll be using Supabase as our database provider. You can sign up for a free account here. However, you can use any database provider you prefer.

Create a new project

Once you've signed up, you'll need to create a new project. You can do this by clicking the New Project button on the dashboard.

Create a new database

Once you've created a new project, you can go ahead and create a new database. You can do this by clicking the Create Database button on the dashboard.

Get your database connection URL

Once you've created a new database, we need to grab your connection URL, this will allow Prisma to connect to it.

You can do this by clicking the Project Settings button at the bottom of the dashboard sidebar and then clicking the Database tab under the Configuration menu.

You'll see a Connection String field, this is your database URL and it will look something like this.

postgresql://johndoe:johnspassword@host:5432/postgres

Database configuration

Add to .env

You'll need to add your new database connection URL to your .env file, this is so that Prisma will be able to read it whilst keeping the private information secure.

Copy the the connection URL and paste it into the DATABASE_URL variable. It should look like this:

.env
DATABASE_URL="postgresql://johndoe:johnspassword@host:5432/postgres"

Configure prisma

We need to make sure that we tell Prisma what kind of database it's working with.

The database schema is defined in the file prisma/schema.prisma. You can see that it's already set up to use a Postgres database however you can change this to any other database provider you prefer.

prisma/schema.prisma
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

Here's a list of the provider databases that Prisma supports:

Initiate database

Now that we've configured our database, we need to create the database tables.

You can do this by running the following command in a terminal window in the root of your project:

npx prisma generate
npx prisma db push

This will create the database tables and make it ready to use.

Example database operations

Here's an example of how you can interact with the database using Prisma.

example.ts
import { prismaClient } from "@/lib/prisma";

const user = await prismaClient.user.findUnique({
  where: {
    email: "john@doe.com",
  },
});

Database models

A model schema (complete with relationships) is provided for your SaaS to handle and manage payments and subscriptions in the @/prisma/schema.prisma file.

You can use this file to get started with a fully functioning database for your SaaS or you can customize to your own needs.

Account

This model represents the account that a user has with your SaaS. It includes the user, the plan, and the status of the account.

@/prisma/schema.prisma
model Account {
  id                String  @id @default(cuid())
  userId            String
  type              String
  provider          String
  providerAccountId String
  refresh_token     String? @db.Text
  access_token      String? @db.Text
  expires_at        Int?
  token_type        String?
  scope             String?
  id_token          String? @db.Text
  session_state     String?

  user User @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@unique([provider, providerAccountId])
}

Session

This model represents the session that a user has with your SaaS. It includes the user, the session token, and the expiration date of the session.

@/prisma/schema.prisma
model Session {
  id           String   @id @default(cuid())
  sessionToken String   @unique
  userId       String
  expires      DateTime
  user         User     @relation(fields: [userId], references: [id], onDelete: Cascade)
}

User

This model represents the user that has an account with your SaaS. It includes the user's email, name, image and their subscription plan if they have one.

@/prisma/schema.prisma
model User {
  id             String           @id @default(cuid())
  name           String?
  email          String?          @unique
  emailVerified  DateTime?
  image          String?
  accounts       Account[]
  sessions       Session[]
  UserPlan       UserPlan?
}

ProductPlan

This model represents the different plans that you offer to your users. It includes the name, price, and properties of the plan.

You will need to enter into your database the plans that you offer to your users.

The productId field is used to store the product ID from your payment provider.

@/prisma/schema.prisma
model ProductPlan {
  id         String                 @id @default(cuid())
  name       String
  productId  String
  price      String
  createdAt  DateTime               @default(now())
  updatedAt  DateTime               @updatedAt
  properties ProductPlanProperty[]
  users      UserPlan[]
}

ProductPlanProperty

This model represents the properties of a particular product plan. For example, a property could be MAX_IMAGES and the value could be 10.

@/prisma/schema.prisma
model ProductPlanProperty {
  id           String           @id @default(cuid())
  planId       String
  propertyName ProductPlanPropertyName
  value        String
  createdAt    DateTime         @default(now())
  updatedAt    DateTime         @updatedAt
  plan         ProductPlan      @relation(fields: [planId], references: [id])
}

ProductPlanPropertyName

This model represents the possible names of a ProductPlanProperty.

@/prisma/schema.prisma
enum ProductPlanPropertyName {
  MAX_IMAGES
}

UserPlan

This model represents the particular plan that a user has purchased or has access to. It includes the user, the plan, and the status of the plan (active/cancelled etc).

This table is what Prisma will mostly query when checking if a user has an active subscription, or if their plan allows access to a particular protected route.

@/prisma/schema.prisma
model UserPlan {
  id                  String           @id @default(cuid())
  userId              String           @unique
  planId              String
  orderId             String?
  productId           String
  variantId           String?
  planName            String?
  planPrice           String?
  subscriptionId      String?
  createdAt           DateTime         @default(now())
  updatedAt           DateTime         @updatedAt
  validUntil          DateTime?
  cancelUrl           String?
  updateUrl           String?
  status              String?
  user                User             @relation(fields: [userId], references: [id])
  plan                ProductPlan      @relation(fields: [planId], references: [id])
}