Database Setup

What You'll Accomplish

By the end of this lesson, you’ll have:

• A working database with tables to store job applications, companies, and contacts
• Understanding of how different pieces of data connect to each other (relationships)
• The ability to view and manage your database using Prisma Studio
• Sample data in your database so you can start building features
• Knowledge of how to make changes to your database structure safely using migrations

Why Do We Need a Database?

Right now our app can’t remember anything. When you refresh the page, everything is gone. A database lets our app store and remember information permanently, like your job applications.

What is SQLite?

For our job application tracker, we’re using SQLite as our database. SQLite is a lightweight, file-based database that’s perfect for getting started with database development.

Unlike larger database systems like PostgreSQL or MySQL that run as separate servers, SQLite stores all your data in a single file on your computer. This makes it incredibly easy to set up - there’s no complicated server configuration or installation process. You just create a file, and you have a working database.

SQLite is production-ready and powers many applications you probably use every day. Plus, it works seamlessly with Cloudflare’s infrastructure.

Setting up our Database Tables or Models

For this project, we’ll use Prisma as our ORM (Object Relational Mapper). An ORM is a tool that acts as a translator between your JavaScript code and your database - instead of writing raw SQL commands like SELECT * FROM users, you can write JavaScript like db.user.findMany(). This makes it much easier to work with databases because you can use familiar JavaScript syntax, and Prisma automatically generates the complex SQL for you. It also helps prevent common database errors and makes it simple to define your database structure and create migrations.

What's a migration?

Think of a migration like a recipe for changing your database. When you need to add a new table, remove a column, or change how data is stored, you create a migration that contains step-by-step instructions for making those changes.

Why are migrations important? Imagine you’re working with a team - when someone adds a new feature that needs a database change, they create a migration file. Now everyone on the team can run that same migration to update their local database to match. Without migrations, you’d have to manually tell everyone “hey, go add this new table with these exact columns” - and that gets messy fast.

Prisma makes this process much easier. Instead of writing complex SQL commands by hand, you just update your schema file (which looks like regular code), and Prisma automatically generates the migration files with all the correct SQL. It’s like having a translator that converts your simple schema changes into the technical database commands needed to make them happen.

I like to start by going through the application design files and making a list of all the tables (models) and fields we need.

Each model represents the type of data that will be stored in the database.

We need models for:

1. Users
2. Job Applications

The “trick” with database design is to prevent duplication. For example, we want to store the company information for each job application. There’s a chance we’ll have multiple job applications for the same company. If we store the company information directly in the Application model, we might have duplicate entries. This could create all kinds of problems. If something changes, every instance of the company information would need to be updated. Plus, there are also formatting issues to consider: is it RedwoodSDK, redwoodsdk, redwood-sdk, or RedwoodSdk?

Instead, we can store the company information in a separate model, with a single entry for each company, and “link” it to the relevant job application.

Uniquely Identifying Entries

Notice that every model has an id field. This is used to uniquely identify each record or row in the database.

The same applies to Contacts. We can create a separate model and create a relationship between the contact and the related company.

3. Companies
4. Contacts

Application Statuses are a little different, but the same principle applies. To avoid duplication, we will give statuses their own model. We will have a set of fixed status options (New, Applied, Interviewing, Offer, and Rejected).

5. Application Statuses

Columns

Now, that we know what our models are, we need to create the columns. What are the pieces of data we need to store for each model?

We’ve already alluded to the data that we’ll need within the relationship diagrams, but let’s take a closer look.

Most tables I create include an id, createdAt, and updatedAt field.

• id - We need a way to uniquely identify each record or row in the database.
• createdAt - We need to know when the record was created.
• updatedAt - We need to know when the record was last updated.

Soft Deleting

In the future, if you’re going to “soft delete” a record, you’ll probably want a deletedAt column for tracking when the record was deleted.

“Soft deleting” a record means that instead of removing the record from the database, we’ll set a column (i.e. deleted) to true to indicate that the record has been deleted. The entry won’t be shown to the user, but it gives you a way to recover the data. This is also useful for auditing purposes.

Data Types

As you’re making a list of all the data we need to store, you’ll also need to think about the data type of each column.

In Prisma, there are 9 different data types:

Prisma Type	Description
String	A UTF-8 encoded string
Boolean	A true or false value
Int	A 32-bit signed integer
BigInt	A 64-bit signed integer (for large numbers)
Float	A floating-point number
Decimal	A high-precision decimal number
DateTime	An ISO 8601 timestamp
Json	Arbitrary JSON data
Bytes	Binary data (Base64-encoded)

IDs

When it comes to ids, there are 2 different types: String and Int.

• String - A UUID (Universally Unique Identifier) is a unique string of characters. For example: 2b22ee49-d788-4bf0-bacd-380b351ca1e0
• Int - An auto-incrementing integer.

When you use an integer, the first entry is usually 1. The next entry is 2, then 3, etc. This makes it easy to tell how many entries are in the database. It’s also easy to sort the entries by the order they were created. However, this can also create a security risk. It’s easy to guess the next entry in the sequence.

For example, if the URL for my job application is https://applywize.app/applications/1, I could assume the URL for the next application is: https://applywize.app/applications/2.

This is where a UUID comes in handy. A UUID is a unique, random string of characters. It’s almost impossible to guess an entry’s ID. The URL https://applywize.app/applications/2b22ee49-d788-4bf0-bacd-380b351ca1e0 isn’t quite as user friendly, but it’s security by obscurity.

Which is better? It depends. My general rule of thumb is if the user is going to see the ID (like our example, in the URL), use a UUID (string). If the ID is an internal identifier, use an integer.

Application Model

Let’s start with the Application model.

I need the following data:

Field	Type	Notes
id	String	A UUID
salaryMin	String	Optional. The minimum salary for the job.
salaryMax	String	Optional. The maximum salary for the job.
dateApplied	DateTime	Optional. The date the application was submitted.
jobTitle	String	Optional. The job title
jobDescription	String	Optional. The job description.
postingUrl	String	Optional. The URL to the job posting.
archived	Boolean	False by default. Whether the application has been archived
createdAt	DateTime	The date the application was created.
updatedAt	DateTime	The date the application was last updated.

Working with Money

You may have noticed that I’m using a String for the salaryMin and salaryMax fields. Alternatively, we could make this a number, so we can standardize the display or sort the applications by salary.

However, there’s a few reasons why I’m using a String:

• Working with money is complicated. It’s not just a number. There’s currency symbols, commas, and decimal points.
• Prisma doesn’t have a native Currency data type for SQLite
• If you are going to use a number, it’s best to use a integer to avoid potential floating-point accuracy problems and store the values as cents.

For now, I’m going to hit the easy button, and use a String.

Now, we need to translate all this information into a Prisma model. Prisma has it’s own syntax for defining models. You’ll notice that it looks similar to an object definition:

prisma/schema.prisma

model Application {
  id             String            @id @default(uuid())
  salaryMin      String?
  salaryMax      String?
  dateApplied    DateTime?
  jobTitle       String?
  jobDescription String?
  postingUrl     String?
  archived       Boolean           @default(false)
  createdAt      DateTime          @default(now())
  updatedAt      DateTime?         @updatedAt
}

A few things worth noting:

• The ? means that that field is optional.
• Some fields are followed by a Prisma directive. For example, @id means that the field is the primary key.
• We can specify a default value with the @default() directive.
  • The @default(uuid()) means that the field will be assigned a UUID
  • The @default(false) means that the field will be false by default
  • The @default(now()) means that the field will be the current date and time
• The @updatedAt directive is a special directive that tells Prisma to update the field with the current date and time whenever the record is updated.

We can do the same for the remaining models (I didn’t make any changes to the provided User and Credential models, but included them here for reference).

prisma/schema.prisma

...

model ApplicationStatus {
  id           Int           @id @default(autoincrement())
  status       String
}

model Company {
  id           String        @id @default(uuid())
  name         String
  createdAt    DateTime      @default(now())
  updatedAt    DateTime?     @updatedAt
}

model Contact {
  id        String    @id @default(uuid())
  firstName String
  lastName  String
  email     String?
  role      String?
  createdAt DateTime  @default(now())
  updatedAt DateTime? @updatedAt
}

model Credential {
  id           String   @id @default(uuid()) // Internal DB ID
  userId       String   @unique // Each user has one discoverable credential
  user         User     @relation(fields: [userId], references: [id])
  createdAt    DateTime @default(now())
  credentialId String   @unique // WebAuthn credential identifier
  publicKey    Bytes
  counter      Int      @default(0)

  @@index([credentialId])
  @@index([userId])
}

model User {
  id           String        @id @default(uuid()) // User ID (UUID-based)
  username     String        @unique
  createdAt    DateTime      @default(now())
  updatedAt    DateTime?     @updatedAt

  credentials Credential[] // Relationship: One user can have many credentials
}

@@index

You may have noticed that the provided Credential model includes the @@index directive. This is used to create an index on the field. This means that Prisma will optimize queries so that I can look up a record by the @@index. This can improve performance.

Prisma already recognizes the id field as an index.

Naming Conventions

When working with Prisma, there a couple of naming conventions you should follow:

• Use singular names for your models. For example, Application not Applications.
• The model name should start with an uppercase letter, in PascalCase.
  • For example: ✅ ApplicationStatus not ❌ applicationStatus or ❌ Application_Status.
• The field names should start with a lowercase letter, in camelCase.
  • For example, ✅ jobTitle not ❌ JobTitle or ❌ job_title.
• Avoid underscores (_) in names.

This looks good, but we haven’t defined any of the relationships between the models (Other than the preexisting relationship between User and Credential models, defined in the starter template).

What are Database Relationships?

A relationship in database terms describes how data in one table connects to data in another table. Think of it like connections between people in real life - you might have relationships with your coworkers, family members, or friends. In our job application tracker, we have similar connections between our data.

For example, every job application belongs to a specific company, and every company can have multiple job applications associated with it. This creates a relationship between our Application table and Company table. Without relationships, we’d have duplicate information — we’d have to store all the company information (name, address, website) separately in every single application record.

Relationships solve this problem by letting us store company information once in a Company table, and then simply reference which company each application belongs to. This keeps our data organized, prevents duplication, and makes it much easier to maintain and update information over time.

There are different types of relationships depending on how the data connects. For example, one company can have many job applications (like if you apply to RedwoodSDK multiple times), but each individual application belongs to only one company. Understanding these connection patterns helps us design a database that’s efficient, organized, and easy to work with.

One-to-one

A one-to-one relationship is a relationship where a record in one table is associated with exactly one record in another table.

One-to-many

A one-to-many relationship is a relationship where a record in one table is associated with one or more records in another table.

Many-to-many

A many-to-many relationship is a relationship where multiple records in one table are associated with multiple records in another table.

In order to establish this relationship, we need a junction table. This table will store the relationship between the two tables.

Naming Conventions

When creating junction tables, I name them based on the two tables they connect. For example, if I have a User and Application model, I’ll name the junction table ApplicationUser. Notice the models are listed in alphabetical order.

IDs in Junction Tables

Technically, you don’t need an id field in the junction table, because the combination of the two IDs (contactId and tagId) together will be unique. However, I still like to include an id field for good measure.

All the Tables for our Database

Here’s a diagram of all the models and their relationships for this project.

Coincidentally, all the relationships in our project are one-to-one or one-to-many.

Crendential Table

Most of the tables in our database are self explanatory. However, the Credential table might not be as obvious. This model is included within the standard starter kit and will hold all the details for our passkeys.

Database Diagram

I always find it helpful to have a visual representation of the database.

I created these diagrams with tldraw. But, Whimsical and Figma are also great tools.

prisma-dbml-generator is an interesting project. It’s built on top of dbdiagram.io and can take a Prisma Schema and generate a diagram.

Relationships within Prisma

When establishing a relationship within your schema, there are 3 parts.

1. Foreign Key Column This stores the ID for the related record.
2. Relation Field. This defines the relationship between the two models. It allows you to access the related records and uses a @relation directive to specify the connection details.
3. Implicit Relationship Field Allows you to access related records from the other side of the relationship

What's a foreign key?

A foreign key is a column (or set of columns) in a table that refers to the primary key of another table, establishing a link between two tables in a relational database.

It’s called a “foreign key” because it references the primary key of the “foreign” table.

What can you see within the database?

Within the database, the only column that you’ll be able to see if the foreign key column that stores the ID of the related record.

The relationship field and implicit relationship field are only used within the context of Prisma.

Let’s look at this in practice. On the Company and Contact models:

prisma/schema.prisma

model Company {
  id           String        @id @default(uuid())
  name         String

  contacts     Contact[]
  createdAt    DateTime      @default(now())
  updatedAt    DateTime?     @updatedAt
}

model Contact {
  id        String    @id @default(uuid())
  firstName String
  lastName  String
  email     String?
  role      String?

  companyId String

  company   Company   @relation(fields: [companyId], references: [id])
  createdAt DateTime  @default(now())
  updatedAt DateTime? @updatedAt
}

1. On the Contact model, we added a companyId field. This stores the ID of the related Company record. Because the id field on the Company model is a string, our companyId field is also a string.
2. On the Contact model, we also added a company field. This describes the Contact↔Company relationship for Prisma.
   • You can read this as: “A contact belongs to a company.”
   • It is connected to the Company model. You can also think of this as having a type of Company.
   • It uses the companyId field on the Contact model to connect the two models.
   • It references the id field on the Company model.
3. On the Company model, we added a contacts field. This describes the Company↔Contact relationship to Prisma.
   • You can read this as: “A company has many contacts.”
   • The type of content will be an array [] of Contact records.

Relationships in Prisma

When you look a the columns in your database that Prisma generates, you won’t find a relation or implicit relationship field, only the foreign key. However, these properties are necessary for Prisma to understand the relationship between the two models.

In some cases, you might need to give the relationship a name. This is particularly useful when you have multiple relationships between the same models.

As an example, I’ve named the relationship CompanyContacts. Notice this is defined on both the relation field and the implicit relationship field.

prisma/schema.prisma

model Company {
  id           String        @id @default(uuid())
  contacts     Contact[]     @relation("CompanyContacts")
  ...
}

model Contact {
  id        String    @id @default(uuid())
  companyId String
  company   Company   @relation("CompanyContacts", fields: [companyId], references: [id])
}

You can find more information about naming relationships in the Prisma documentation.

Now, we need to create the remaining relationships. Here’s my final schema.prisma file.

prisma/schema.prisma

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

// Looking for ways to speed up your queries, or scale easily with your serverless or edge functions?
// Try Prisma Accelerate: https://pris.ly/cli/accelerate-init

generator client {
  provider = "prisma-client"

  runtime                = "workerd"
  moduleFormat           = "esm"
  generatedFileExtension = "ts"
  importFileExtension    = "ts"

  output          = "../generated/prisma"
  previewFeatures = ["queryCompiler", "driverAdapters"]
}

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

model Application {
  id             String            @id @default(uuid())
  userId         String
  user           User              @relation(fields: [userId], references: [id])
  status         ApplicationStatus @relation(fields: [statusId], references: [id])
  statusId       Int               @default(1)
  salaryMin      String?
  salaryMax      String?
  dateApplied    DateTime?
  jobTitle       String?
  jobDescription String?
  postingUrl     String?
  createdAt      DateTime          @default(now())
  updatedAt      DateTime?         @updatedAt
  archived       Boolean           @default(false)
  companyId      String
  company        Company           @relation(fields: [companyId], references: [id])
}

model ApplicationStatus {
  id           Int           @id @default(autoincrement())
  status       String
  applications Application[]
}

model Company {
  id           String        @id @default(uuid())
  name         String
  applications Application[]
  contacts     Contact[]
  createdAt    DateTime      @default(now())
  updatedAt    DateTime?     @updatedAt
}

model Contact {
  id        String    @id @default(uuid())
  firstName String
  lastName  String
  email     String?
  role      String?
  companyId String?
  company   Company?  @relation(fields: [companyId], references: [id])
  createdAt DateTime  @default(now())
  updatedAt DateTime? @updatedAt
}

model Credential {
  id            String   @id @default(uuid()) // Internal DB ID
  userId        String   @unique // Each user has one discoverable credential
  user          User     @relation(fields: [userId], references: [id])
  createdAt     DateTime @default(now())
  credentialId  String   @unique // WebAuthn credential identifier
  publicKey     Bytes
  counter       Int      @default(0)

  @@index([credentialId])
  @@index([userId])
}

model User {
  id                 String        @id @default(uuid())
  username           String        @unique
  createdAt          DateTime      @default(now())
  updatedAt          DateTime?     @updatedAt
  applications       Application[]
  credentials        Credential[]
}

Models in Alphabetical Order

This is personal preference, but I like to put my models in alphabetical order. It makes it easier to find what you’re looking for, especially on larger projects.

Running Migrations

A migration is a change to the database schema. It’s a way to keep track of the changes we make to the database.

When we create the migration, Prisma will look at our schema.prisma file and determine what has changed since our last migration. Then, it will generate a SQL file that will update our database with the changes.

Let’s create the migration by running migrate:new and give our migration a name. Since this is our first migration, we’ll say: setup all database models.

npm

npm run migrate:new "setup all database models"

pnpm

pnpm run migrate:new "setup all database models"

yarn

yarn run migrate:new "setup all database models"

New Migration

When you run pnpm migrate:new, it will create a new migration file and apply it. There’s no need to run pnpm migrate:dev (unless of course the migration failed).

Troubleshooting a Migration

Occasionally, you may run into an error where there are multiple sqlite files in the .wrangler > state > v3 > d1 > miniflare-D1DatabaseObject folder.

If this happens, you can delete the .wrangler folder and rerun dev to regenerate it.

Then, try running migrate:dev to apply the migration.

Our generated sql file will be in the migrations folder.

• 0001_init.sql was generated the first time we ran pnpm dev.
• 0002_setup_all_database_models.sql was generated when we ran pnpm migrate:new "setup all database models".

The Number of Migrations

Worried about the number of migrations in your project? Don’t. It really doesn’t have any effect on your project.

Working with other people

An added benefit of migrations is that it makes it easier to work with other people.

Migration files get committed to the repository and placed in version control.

Database changes can be tied to a branch and a specific feature or bug fix.

Keeping another team member’s database in sync is as simple as running the migration: npm migrate:dev

Troubleshooting: If the Migration Didn't Run

If the migration didn’t run, there are a few things you can check:

• How many SQLlite files are in the .wrangler > state > v3 > d1 > miniflare-D1DatabaseObject folder? There should be one. You can always delete the .wrangler folder and rerun pnpm dev to regenerate it.
• Try running pnpm migrate:dev. This will apply any new migrations.
• Try running pnpm dev. This will also apply any new migrations.

Migration Commands

There are 3 different migration commands:

1. migrate:new: creates a new migration
2. migrate:dev: runs the migration on your local database
3. migrate:prd: runs the migration on your production database

Previewing the Migration

If you want to preview the database, there are a couple of different options.

Prisma Studio (Recommended)

Prisma Studio ships with Prisma, so there’s nothing extra to install.

Let’s start by opening the schema.prisma file.

prisma/schema.prisma

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

You’ll notice the url is referencing the DATABASE_URL environment variable.

We need this to set this up in the .env file so Prisma Studio can connect to the database.

First, let’s go to our d1 database. This is inside the .wrangler/state/v3/d1/miniflare-D1DatabaseObject directory.

Right click on the sqlite file and click Copy Relative Path.

Inside your .env file, create a DATABASE_URL variable. We need to specify that this is a file:

DATABASE_URL="file:../"

Then, paste in the database path:

Now, within the Terminal, run:

npx prisma studio

This will open a new tab in your default browser at http://localhost:5555.

• Within Prisma Studio, you can see a list of all your tables/models on the left.
• If you click on one, it will load the table/model on the right.

Right now, we don’t have any data in our table, but at least we can tell that our migration ran successfully.

Deleting the .wrangler folder

If you ever delete the .wrangler folder, it will automatically generate a new one the next time you run pnpm dev. However, you’ll need to update the DATABASE_URL path within your .env file, as the hash will change.

SQLite Viewer

The VS Code extension, SQLite Viewer is perfect for previewing your SQLite database directly within VS Code.

Within the file explorer, navigate to your D1 database. You can find it in the .wrangler > state > v3 > d1 > miniflare-D1DatabaseObject folder.

• Directory.wrangler

  • Directorystate

    • Directoryv3

      • Directoryd1

        • Directoryminiflare-D1DatabaseObject/

          • 005cb3d3133217f352562272facf516778925ea1151d9c310bcb4e13614995c1.sqlite

The file name of your sqlite database will vary.

Click on the sqlite file and a preview should open within VSCode.

The viewer will only let you preview the database. If you want to create, update, or delete, you’ll need a pro (premium) account.

Seed the database

We can manually add data to the database through Prisma Studio.

Instead of manually adding data to the database, we can create a seed file to programmatically add data for us. This is particularly helpful if you need to reset the database during development and don’t want to manually enter test data (again and again).

Within the src > scripts directory, you’ll find a seed.ts

• Directorysrc/

  • Directoryscripts/

    • seed.ts

This contains the basic structure for our seed file.

src/scripts/seed.ts

import { defineScript } from "rwsdk/worker";
import { db, setupDb } from "@/db";

export default defineScript(async ({ env }) => {
  setupDb(env);

  await db.$executeRawUnsafe(`\
    DELETE FROM User;
    DELETE FROM sqlite_sequence;
  `);

  await db.user.create({
    data: {
      id: "1",
      username: "testuser",
    },
  });

  console.log("🌱 Finished seeding");
});

If you want to start fresh with your own seed file, you could delete lines 7-17, but we'll use this as a starting point.

Seed File Structure

In order for our seed file to run properly, we have to run our script inside a Cloudflare worker.

Why? Since we’re using a Cloudflare D1 database, we need a worker to access it.

You can copy and paste the following code to your project:

src/scripts/seed.ts

import { defineScript } from "rwsdk/worker";
import { db, setupDb } from "../db";

export default defineScript(async ({ env }) => {
  setupDb(env);

  await db.$executeRawUnsafe(`\
    DELETE FROM Application;
    DELETE FROM ApplicationStatus;
    DELETE FROM Contact;
    DELETE FROM Company;
    DELETE FROM Credential;
    DELETE FROM User;
    DELETE FROM sqlite_sequence;
  `);

  await db.applicationStatus.createMany({
    data: [
      { id: 1, status: "New" },
      { id: 2, status: "Applied" },
      { id: 3, status: "Interview" },
      { id: 4, status: "Rejected" },
      { id: 5, status: "Offer" },
    ],
  });

  console.log("🌱 Finished seeding");
});

Let’s make sure we understand what’s happening:

• On lines 1 and 2 are importing our dependencies. We need our Cloudflare worker and database connection.
• On line 4, we’re setting up our Cloudflare worker environment.
• On line 5, we’re setting up our database connection.
• On line 7-15, we’re using raw SQL to delete all the existing data from the database. This will allow us to start fresh when seeding the database.
• On line 17-25, we’re adding all the job application statuses to the database. We’re using a standard Prisma createMany function. This takes a data array of objects. Each object contains the data for a single record.
• On line 27, we’re logging a message to the console saying the seeding is complete.

Troubleshooting

Are you getting an error that says: "Property 'applicationStatus' does not exist on type 'PrismaClient<PrismaClientOptions, never, DefaultArgs>'"

This means that the Prisma types haven’t been generated for your model. Within the Terminal, run:

pnpm prisma generate

That should resolve your problem. Otherwise, ensure the ApplicationStatus table exists in your database.

To seed the database, run the following command:

npm

npm run seed

pnpm

pnpm run seed

yarn

yarn run seed

Within the Terminal, you should see something like this:

Now, when you preview the database and look at the ApplicationStatus table, you should see:

Multiple Seed Files

Sometimes, I’ll create multiple seed files for different purposes.

The seed file we just created is perfect for a brand new database, whether we’re in development or in production. We’ve provided all the application statuses that we need. These won’t change.

Typically, I’ll also create seed files to test the application with various sets of data. This is much easier (and faster) than manually adding data again and again.

You can also leverage libraries, like Faker and Snaplet, to make it easier to create realistic data sets.

Code on GitHub

You can find the final code for this step on GitHub.

Further reading

• Prisma Documentation
• VS Code Prisma Extension