Javascript with Prisma

Connecting with Javascript using Prisma

Prisma is an ORM for Javascript that seeks to wrap the entire experience and improve it. So, when looking at Prisma, we get everything from migrations to object relational mapping.

This tutorial is a pretty generic, but the implementation of Prisma in your application will follow a similar pattern. So, just by following this tutorial line-for-line, you should have a bit more of understanding.

Steps

  1. Create your Crunchy Bridge Postgres Cluster
  2. Project Preparation
  3. Grant create database to your application user
  4. Initialize Prisma with Postgres
  5. Populate the database with a seed file
  6. Connect Prisma to Application

Create your Crunchy Bridge Postgres cluster

Creating a Crunchy Bridge cluster is pretty simple. Log into your Crunchy Bridge account, and click “Create Cluster”, then follow the cluster create form.

From here, we’ll want to get a “connection string” that we can use later. It looks like this:

postgres://user:password@host:port/database?setting=value

Once your Postgres cluster has a status of “ready” (a few minutes), click on the “Connections” tab for your connection string.

We will set this value as a DATABASE_URL value later when connecting to the database.

Project preparation

If you are following this tutorial strictly, then you'll need to create a new empty directoy to hold your project.

Then, add the following to a package.json file:

{
  "type": "module"
}

The rest of this walkthrough will run from this directory.

Grant create database to your application user

Because of the way that Prisma does database schema creation and reconcilitation, the database user that Prisma uses will need the createdb private.

From your connection string interface shown in the first step, choose the "postgres" user and the "psql" format. Copy that command and run it from your command line to connect as the the superuser. Then, run the following to grant createdb permissions to the application user:

postgres=> ALTER USER application CREATEDB;

If you are using a different user, just change the "application" value to your correct user name.

Initialize Prisma with Postgres

From the project directory, run the following to install the Prisma client:

bash> npm install @prisma/client

Then, initialize Prisma for Postgres:

bash> npx prisma init --datasource-provider postgresql

Then, the following to configure Prisma in that same directory:

bash> npx prisma generate

Now, we'll provide the connections string for Prisma to connect to Postgres. Prisma by default uses the .env file, which sets environmental variables.

Open the .env file in your project's directory, then insert the connection string from the Crunchy Bridge UI for the DATABASE_URL value. We do encourage appending schema=public explicitly:

DATABASE_URL="<your connection string goes here>?schema=public"

Create a library that you can use for connecting to your database. For this tutorial, create a db.server.js file like:

import { PrismaClient } from '@prisma/client'

let db = PrismaClient

if (process.env.NODE_ENV === 'production') {
  db = new PrismaClient()
} else {
  db = new PrismaClient({
    log: ['query', 'info', 'warn', 'error'],
  })

  db.$on('query', e => {
    console.log('--------------------')
    console.log('Query: ' + e.query)
    console.log('Params: ' + e.params)
    console.log('Duration: ' + e.duration + 'ms')
    console.log('--------------------')
  })
}

export default db

Notice that we've enhanced the log and the $on. These configurations will increase the logging seen when your application is running, thus making the ORM act less like a black-box, and more like a part of your application.

Configure your database structure

Prisma has the ability to managed database structure, which it calls a "schema." It does this by creating a shadow database from the file at prisma/schema.prisma. Then, it compares the application database against the shadow database. For any differences, Prisma will migrate the application database to the new configuration.

These calls can be destructive to data. For production databases, always run database backups before running migration commands.

If you are using an existing dataset, see our Migration Guide (or reach out to us, we are happy to help). Dump your data from your existing database, then load it into your Crunchy Bridge database.

If you are starting fresh, then create your schema file at prisma/schema.prisma.

If you need an sample code, below, we have some. It will create two tables: games and teams. Notice some of the syntax we supply here, it is more verbose than the typical Prisma schema file. We use this verbosity to create syntactically preferred names within the database:

// 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 = "postgresql"
  url      = env("DATABASE_URL")
}

model Team {
  @@map("teams")

  id Int @id @default(autoincrement())
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")
  name String

  homeGames Game[] @relation("homeTeam")
  awayGames Game[] @relation("awayTeam")
}

model Game {
  @@map("games")

  id Int @id @default(autoincrement())
  createdAt DateTime @default(now()) @map("updated_at")
  updatedAt DateTime @updatedAt @map("created_at")

  homeTeam Team @relation("homeTeam", fields: [homeTeamId], references: [id])
  homeTeamId Int @map("home_team_id")

  awayTeam Team @relation("awayTeam", fields: [awayTeamId], references: [id])
  awayTeamId Int @map("away_team_id")

  gameTimeAt DateTime @map("game_time_at")
}

For more details on Prisma schema files, check out: https://pris.ly/d/prisma-schema

Then, run the following to migrate your database:

bash&gt; npx prisma migrate dev --name init

For future migrations, you'll need to run Prisma's migration functionality. To check that out, see Prisma Migrate guide.

Populate the database with a seed file

Now, let's create a seed file to populate your database. Add the following to prisma/seed.js in your directory:

import { PrismaClient } from '@prisma/client'
const db = new PrismaClient()

async function seed() {
  var team_one = await db.team.create({ data: { name: 'Los Angeles Lakers' } })
  var team_two = await db.team.create({ data: { name: 'Sacramento Kings' } })

  await db.game.create({
    data: {
      awayTeamId: team_one.id,
      homeTeamId: team_two.id,
      gameTimeAt: new Date(),
    },
  })
}

seed()

Run your seed file now:

node prisma/seed.ts

Connect Prisma to application

Below, we create a very simple Express app that only responds on the root path. Create an app.js to run the query:

import db from './db.server.js'
import express from 'express'

const app = express()
const port = 3000

async function rootPath(req, res) {
  // ... you will write your Prisma Client queries here
  const games = await db.Game.findMany({
    include: {
      homeTeam: true,
      awayTeam: true,
    },
  })
  res.send(games)
}

app.get('/', rootPath)

app.listen(port, () => {
  console.log(`Example app listening on port ${port}`)
})

Now, run your server:

bash> node app.js

Then, open your browser, and go to http://localhost:3000, and you'll see the games that were created previously.

We have listed a number of files in this walkthrough. You should have the following in your project's directory:

  • app.js
  • db.server.js
  • package.json
  • prisma/schema.prisma
  • prisma/seed.js

If your code is erroring, check that you are not missing a file. If you are missing that file, find it in the walkthrough above, and create it.