A Simple REST API with Prisma, Part 1: The Setup

This two-part series will cover building a simple REST API using the Prisma ORM and Postgresql. Our API will use the USDA plants data to create a Postgres database and make CRUD operations using Prisma. Prisma is a new type of ORM that attempts to avoid the problems we typically encounter with traditional ORMs when making changes to our tables. Part one of this two-part series will cover how to get a Prisma schema from your Postgresql database.

Some things you’ll need for this tutorial:

Creating Your .env File

This file will contain the configuration variables that pertain to our simple Postgres database and Prisma configuration. In our case, our database will be called plants-db.

DATABASE_URL="postgresql://postgres:password@localhost:5432/plants-db?schema=public"
DATABASE_PASSWORD="password"
DATABASE_USER="postgres"
DATABASE_NAME="plants-db"

Setting Up Your Database with Postgres.

1. Get your simple Postgres database setup. We’ll use Docker . Let’s write a docker-compose.yml file.

version: '3.1'
services:
  db:
    image: postgres:latest
    ports: ['5432:5432']
    restart: always
    environment:
      POSTGRES_PASSWORD: $$DATABASE_PASSWORD
      POSTGRES_USER: $$DATABASE_USER
      POSTGRES_DB: $$DATABASE_NAME
    volumes:
      - plants-db:/var/lib/postgresql/data
volumes:
  plants-db:

Make sure to download the official postgres image for Docker for this to work.

2. Then, start your database connection by running the following:

docker compose up

Importing CSV Data into our DB

Now you have your database connection up and running. Let’s use TablePlus to import some CSV data to your database.

1. First, let’s create a table in the SQL section and run the following:

CREATE TABLE plants(
id serial PRIMARY KEY,
Symbol varchar,
Accepted_Symbol_x varchar,
Synonym_Symbol_x varchar,
Scientific_Name_x varchar,
Hybrid_Genus_Indicator varchar,
Hybrid_Species_Indicator varchar,
Species varchar,
Subspecies_Prefix varchar,
Hybrid_Subspecies_Indicator varchar,
Subspecies varchar,
Variety_Prefix varchar,
Hybrid_Variety_Indicator varchar,
Variety varchar,
Subvariety_Prefix varchar,
Subvariety varchar,
Forma_Prefix varchar,
Forma varchar,
Genera_Binomial_Author varchar,
Trinomial_Author varchar,
Quadranomial_Author varchar,
Questionable_Taxon_Indicator varchar,
Parents varchar,
Common_Name varchar,
State varchar,
Category varchar,
Family varchar,
Family_Symbol varchar,
Family_Common_Name varchar,
xOrder varchar,
SubClass varchar,
Class varchar,
SubDivision varchar,
Division varchar,
SuperDivision varchar,
SubKingdom varchar,
Kingdom varchar,
Duration varchar,
Growth_Habit varchar,
Native_Status varchar,
Federal_Noxious_Status varchar,
State_Noxious_Status varchar,
State_Noxious_Common_Name varchar,
Invasive varchar,
Federal_T_E_Status varchar,
State_T_E_Status varchar,
State_T_E_Common_Name varchar,
Accepted_Symbol_y varchar,
Synonym_Symbol_y varchar,
Scientific_Name_y varchar,
Active_Growth_Period varchar,
After_Harvest_Regrowth_Rate varchar,
Bloat varchar,
C_N_Ratio varchar,
Coppice_Potential varchar,
Fall_Conspicuous varchar,
Fire_Resistance varchar,
Flower_Color varchar,
Flower_Conspicuous varchar,
Foliage_Color varchar,
Foliage_Porosity_Summer varchar,
Foliage_Porosity_Winter varchar,
Foliage_Texture varchar,
Fruit_Color varchar,
Fruit_Conspicuous varchar,
Growth_Form varchar,
Growth_Rate varchar,
Height_at_Base_Age_Maximum_feet varchar,
Height_Mature_feet varchar,
Known_Allelopath varchar,
Leaf_Retention varchar,
Lifespan varchar,
Low_Growing_Grass varchar,
Nitrogen_Fixation varchar,
Resprout_Ability varchar,
Shape_and_Orientation varchar,
Toxicity varchar,
Adapted_to_Coarse_Textured_Soils varchar,
Adapted_to_Medium_Textured_Soils varchar,
Adapted_to_Fine_Textured_Soils varchar,
Anaerobic_Tolerance varchar,
CaCO_3_Tolerance varchar,
Cold_Stratification_Required varchar,
Drought_Tolerance varchar,
Fertility_Requirement varchar,
Fire_Tolerance varchar,
Frost_Free_Days_Minimum varchar,
Hedge_Tolerance varchar,
Moisture_Use varchar,
pH_Minimum varchar,
pH_Maximum varchar,
Planting_Density_per_Acre_Minimum varchar,
Planting_Density_per_Acre_Maximum varchar,
Precipitation_Minimum varchar,
Precipitation_Maximum varchar,
Root_Depth_Minimum_inches varchar,
Salinity_Tolerance varchar,
Shade_Tolerance varchar,
Temperature_Minimum_F varchar,
Bloom_Period varchar,
Commercial_Availability varchar,
Fruit_Seed_Abundance varchar,
Fruit_Seed_Period_Begin varchar,
Fruit_Seed_Period_End varchar,
Fruit_Seed_Persistence varchar,
Propogated_by_Bare_Root varchar,
Propogated_by_Bulbs varchar,
Propogated_by_Container varchar,
Propogated_by_Corms varchar,
Propogated_by_Cuttings varchar,
Propogated_by_Seed varchar,
Propogated_by_Sod varchar,
Propogated_by_Sprigs varchar,
Propogated_by_Tubers varchar,
Seeds_per_Pound varchar,
Seed_Spread_Rate varchar,
Seedling_Vigor varchar,
Small_Grain varchar,
Vegetative_Spread_Rate varchar,
Berry_Nut_Seed_Product varchar,
Christmas_Tree_Product varchar,
Fodder_Product varchar,
Fuelwood_Product varchar,
Lumber_Product varchar,
Naval_Store_Product varchar,
Nursery_Stock_Product varchar,
Palatable_Browse_Animal varchar,
Palatable_Graze_Animal varchar,
Palatable_Human varchar,
Post_Product varchar,
Protein_Potential varchar,
Pulpwood_Product varchar,
Veneer_Product varchar,
Genus varchar
);

2. Download the USDA Plants data.
Then, go to File > Import > From CSV. You should see the following wizard:

3. Load the file and select the plants table. Note: Make sure you also select Match Columns by Name – Case Insensitive to autodetect the column types. Some columns may be mismatched but don’t worry. Just make sure native status, common name, and state are all correctly mapped.

4. Select Import.

Setting Up Prisma

1. Let’s install Prisma and the PrismaClient:

npm install prisma@latest --save-dev

and then:

npm install @prisma/client@latest

2. Let’s create a Prisma project via:

npx prisma init

3. Navigate to your newly-generated schema file. The important part is the datasource db, which will point to the URL we’ve already setup in our.envfile.

datasource db {
  provider = “postgresql”
  url = env(“DATABASE_URL”)
}

4. Let’s run this to convert our database schema into a Prisma schema:

npx prisma db pull

You should see a message that says it introspected 1 model and wrote it into our schema file.
Note: Make sure to run this command every time you make manual changes to your database with SQL or other tools.
5. Let’s generate our client for importing

npx prisma generate

You should get the following message:

Now the Prisma client is ready for importing!


In the second half of this two-part series, I will cover how to use this Prisma client in our Express server.