👨🏽‍💻 Using Prisma and Directus

Using Prisma ORM and Directus CMS together on an existing database

The use case for this blog is using the ORM tool Prisma and the CMS Directus installed in the same database, managing the database schema without deleting local data when external changes are made in a local environment.

See the project files on Github.

Directus is a modern CMS which can be used in a variety of ways

Setting up Prisma and the database

To initialise Prisma, run npx prisma init.

We’ll use Postgres inside of docker. Create a docker-compose.yml file with the following code:

YAML
version: '3.8'
services:
  db:
    image: postgres:14.1-alpine
    restart: always
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=docker
      - POSTGRES_DB=prismadirectusdb
    ports:
      - '5432:5432'
    volumes:
      - ./docker/db:/var/lib/postgresql/data
volumes:
  db:
    driver: local

Add a docker folder for your root directory which Docker will use for persistant storage.

Next, set the DATABASE_URL in the .env file to your database connection – which in this example would be:

PowerShell
DATABASE_URL="postgresql://postgres:docker@localhost:5432/prismadirectusdb?schema=public"

Running docker compose up boots up the database.

API setup

To have something to test with, let’s create a basic express server so we can interact with our database.

Install it with npm install express.

Create a main.js file in the root directory and add the following code for a simple server:

JavaScript
// main.js

import express from 'express'
const app = express()

app.get('/', function (req, res) {
  res.send('Hello World')
})

app.listen(3000)

Note: Since we use ‘import’ instead of ‘require’, open the package.json file and add the line "type": "module",.

To start the app, in your terminal run node main.js and open localhost:3000 in your web browser. You should see ‘Hello World’.

Instead of having to run this command, open the package.json file and add a script to run it as start:

JavaScript
{ 
  "scripts": {
    "start": "node main.js"
  },
  "type": "module",
  "dependencies": {
    "@prisma/client": "^4.3.1",
    "express": "^4.18.1"
  },
  "devDependencies": {
    "prisma": "^4.3.1"
  }
}

Let’s add some data into our database so we can display it.

Open your schema file at /prisma/schema.prisma. By default it looks like this:

JavaScript
// 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")
}

Add in a new table with some columns, e.g, ‘users’:

JavaScript
// 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 users {
  id             String    @id @default(uuid())
  name           String
  location       String
  dateRegistered DateTime? @default(now())
}

To apply our new table, make sure Docker is running the databse and run npx prisma migrate dev. Give the migration a name like ‘Added user table’.

Once that’s completed we can open Prisma’s database viewer and see our new table with npx prisma studio. It will open the viewer at localhost:5555 where you can play around with adding rows.

Seeding the database

To generate some sample data, let’s create a new file db-seed.js to seed the database.

Install the popular library faker to generate fake data with npm install @faker-js/faker --save-dev.

Create a db-seed.js with the following to generate 10 records:

JavaScript
// db-seed.js

import { PrismaClient } from '@prisma/client'
import { faker} from '@faker-js/faker';

const main = async () => {
    // Generate 100 user records for seeding
    let userData = []
    for (let i=0; i<10; i++) {
        userData.push({
            "name": faker.name.fullName(),
            "location": faker.address.cityName() 
        })
    }

    // Add records into DB
    const prisma = new PrismaClient()
    const createMany = await prisma.users.createMany({
        data: userData
    })
}
main()

Run the file with node db-seed.js.

In Prisma Studio you can see the new records:

Prisma Studio showing new seeded records

To get this data into our API, update your main.js file:

JavaScript
// main.js

import { PrismaClient } from '@prisma/client'
import express from 'express'
const app = express();

app.get("/", async function (req, res) {
    
    // Get list of clients
    const prisma = new PrismaClient()

    let userTableData = "<h1>Our amazing app users</h1>"

    const users = await prisma.users.findMany()
    users.forEach(item => {
        userTableData += `
            <tr>
                <td>${item.name}</td>
                <td>${item.location}</td>
            </tr>
        `
    });

    let tableHTML = `
    <table>
        <thead>
            <tr>
                <td>Name</td>
                <td>Location</td>
            </tr>
        </thead>
        <tbody>
            ${userTableData}
        </tbody>
    </table>
    `

    res.send(tableHTML);
});

app.listen(3000);

Install Directus

Directus is a content management system that can be installed over top an existing database. The Directus docs take you through the self-hosted installation.

To install it run npm init directus-project directus-app, add the Postgres database credentials and create an admin user.

Once completed, open the new directus-app folder and run npx directus start to start the project.

Use the Data Model page to create a new table the same as our Prisma one, e.g, ‘new_users’ with the same fields and add a couple of entries.

Now the Postgres DB has changed and if we wanted to update our Prisma Schema and run a new migration we will get the error Drift detected: Your database schema is not in sync with your migration history. and it will want to delete the data in our database.

One solution is to add the new Directus tables to the Prisma schema, delete the current migrations thus resetting the migration history and allowing us to create a new initial migration with the latest schema and keep existing data.

Note: This defeats the purpose of using migrations for version control.

Resetting the migration history

To get the new tables into Prisma, run npx prisma db pull. You’ll notice the schema.prisma file has imported all the directus tables.

Delete the migration folder in /prisma and delete the entries in the _prisma_migrations table in your database.

Note: A tool like Beekeeper Studio provides a handy UI to interact with your database.

Next, run these three commands to manually create a migration and mark it as applied:

PowerShell
rm -r prisma/migrations
mkdir -p prisma/migrations/init01
npx prisma migrate diff --from-empty --to-schema-datamodel prisma/schema.prisma --script > prisma/migrations/init01/migration.sql
npx prisma migrate resolve --applied init01

Now we can run npx prisma migrate dev without any issues!

Automating this step

Create a new script in package.json called pullSchemaChanges like below:

JavaScript
"scripts":{
    "start":"node main.js",
    "pullSchemaChanges": "sh ./pullSchemaChanges.sh"
  },

Create this new bash file pullSchemaChanges.sh and insert the following:

PowerShell
# Connect to database and clear migration history
psql -U postgres -h localhost -p 5432 -d prismadirectusdb -c 'truncate table _prisma_migrations;'

# Get latest external changes
npx prisma db pull
npx prisma generate

# Clear old migration folders and set up new one
rm -r prisma/migrations
mkdir -p prisma/migrations/init

# Manually apply migration
npx prisma migrate diff --from-empty --to-schema-datamodel prisma/schema.prisma --script > prisma/migrations/init/migration.sql
npx prisma migrate resolve --applied init

Now if you create a new table with Directus and want to update the prisma schema, just run npm run pullSchemaChanges.

Updating the API

To make sure Prisma is working, update line 14 of the main.js file to read from our Directus-created table ‘new_users’:

JavaScript
const users = await prisma.new_users.findMany()

Reloading the app should show the new data.

That’s how to manage external data with Prisma!


Further reading

3 Comments

  1. Ricky Sullivan Himself

    December 13, 2022 at 7:17 am

    Yo. Stumbled across this post looking for ways that I can use Directus + Prisma.

    I’m kind of doing thesame thing, except I’m updating my Prisma schema, then pushing those changes to the DB.

    I was wanting to keep any Directus tables out of the Prisma schema so that it remained semi independant. Each time I run a migration, it was trashing Directus tables and I was having to `npx directus bootstrap` again.

    Perhaps I need to flip this around as Directus needs to do an introspection each time you add a new field.

    • Yeah would it be possible for you to start using Directus to add tables in your database? It’s what I ended up doing + you get a nice UI for it.

  2. Thank you a lot for this post!

Leave a Reply

Your email address will not be published. Required fields are marked *