Mythica: Database setup with Prisma and Planetscale

This series of articles focuses on building a full-stack app with the following technology stack: PlanetScale - Prisma - tRPC - React. The project name is mythica and it will allow user to collect random mythical creatures. Code for this series can be found here.

Table of contents

Project Structure

In a new folder, start by creating package.json file by running npm init -y. Customize your package.json to look like this.

{
  "name": "mythica",
  "version": "1.0.0",
  "description": "Mythical creatures card collection showcasing PlanetScale - Prisma - tRPC - React",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1",
    "dev": "npm run dev --workspace=client & npm run dev --workspace=server"
  },
  "workspaces": ["./packages/client", "./packages/server"],
  "keywords": [],
  "author": "shadyaziza.com",
  "license": "Apache-2.0"
}

Notice how we define workspaces one for the client/React the other is for the server/tRPC. We reference these two directories in our dev command to start the client and the server. This monorepo approach is the recommended one when dealing with tRPC later. Now let’s actually create these workspaces such that our project structures will look like the following:

|____package.json
|____packages
| |____server
| |____client

Installing Dependencies

We will be building our server using Express and we have Prisma ORM to handle our database hosted on PlanetScale. Now, let’s go inside our server directory and install our packages.

cd packages/server && npm init -y && npm i express @prisma/client

We are going to use Typescript in this project, so let’s add the following as our developer dependencies. We also need to add prisma to configure our prisma schema and migrations.

npm i -D typescript @types/node @types/express prisma

We have not forgot about adding trpc as a dependency. Let’s do this now in a different way to leverage our workspaces definition. So go back to the root in the application and run.

npm i @trpc/server --workspace=server

To finalize this setup, we will need to add two more dependencies zod for schema validation, and cors to allow access to our server from our react app.

npm i cors zod --workspace=server

And we have to add @types/cors as dev dependency

npm i -D @types/cors --workspace=server

Database Setup

• We need initialize our Prisma configuration by running the following command from inside the server directory

npx prisma init --datasource-provider=mysql

✔ Your Prisma schema was created at prisma/schema.prisma
  You can now open it in your favorite editor.

Next steps:
1. Set the DATABASE_URL in the .env file to point to your existing database. If your database has no tables yet, read https://pris.ly/d/getting-started
2. Run prisma db pull to turn your database schema into a Prisma schema.
3. Run prisma generate to generate the Prisma Client. You can then start querying your database.

More information in our documentation:
https://pris.ly/d/getting-started

You will noticed schema.prisma file created inside prisma folder. In addition to .env file. Inside the .env file, we will add our DATABASE_URL which we will obtain from the next step from PlanetScale dashboard.

We will be using PlanetScale’s mysql database. You can however use any other serverless database or run your own locally. If you are following along, head to PlanetScale, create an account and create your database. This process should be fairly straight forward. Finally, you can create a password from the prompt and when you reach Connection strings dialog choose connect with Prisma and copy & paste the connection string into your environment variables’ file.

Data Models Definition

When you open schema.prisma file that was generated in the previous step. You will find that it contains the configuration needed to connect to our database with reference to our environment variable of DATABASE_URL we obtained earlier. Do not forget to change your url and/or provider if you are using a different database (e.g: postgres). In addition to this configuration, we also need to define our data models inside this file. We need to have a Creature model that will contain information about mythical creatures like so:

So let’s edit our schema.prisma file to add our Creature model:

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

generator client {
  provider = "prisma-client-js"
}

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

model Creature{
  id Int @id @default(autoincrement())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  name String
  photo String
  country_code String
  country_name String
  books String
  description String
  characteristics String
}

Now we need to persist this schema to our own database on PlanetScale. Since we url = env("DATABASE_URL") points to our database instance, we can now run npx prisma db push and if everything went okay you should find your result similar to the following:

npx prisma db push
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": MySQL database "mythica" at "aws.connect.psdb.cloud"

🚀  Your database is now in sync with your Prisma schema. Done in 2.08s

We can also verify this by going to our PlanetScale dashboard and from the console tab we can run show tables; command to view all the tables available in our database.

show tables;
1 row in (5.50 ms)
Tables_in_mythica
Creature

That’s it for this tutorial, we have prepared the ground work to be able to easily change our database schema. We will revisit working with Prisma when we add the trpc layer but before that we will be adding the Express scaffolding in order to be able to run our server code.

You can find the final code for this article here