How to upgrade schema db using Prisma

Objectives

Prisma is an open-source tool for Node.js that simplifies database interactions through an intuitive API, automatic migrations and a data modeling system. It support multiple database formats MongoDB, MySQL, MariaDB, PostgreSQL and we are going to use it in Kunai with SQLite.

The idea is to generate scripts used by Prisma in order to implement the structure and the content evolution of the database from a release to and other one.

Steps

Prerequisites

Have installed Prisma in your project
Have the schema database expected

Initialization

Initialize the Prisma environment.

npx prisma migrate init

Create a new database

Define schema into the prisma/schema.prisma file

In order to define the structure of the database you need to define all the tables and there attributes into a schema file.

vi prisma/schema.prisma

generator client {
provider = "prisma-client-js"
binaryTargets = ["native", "debian-openssl-3.0.x"]
}

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

model projects {
id    Int     @id @default(autoincrement())
name               String @unique
git_type           String
git_repository     String
git_token          String
git_certificat     String
cluster_url        String
cluster_token      String
cluster_certificat String
prune              Boolean
createdAt          DateTime @default(now())
updatedAt          DateTime @updatedAt
}

Generate the database creation script

npx prisma migrate dev

A following file will be generated :

prisma/migrations/XXXXXXXXXXXXXX_init/migration.sql

-- CreateTable
CREATE TABLE "projects" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "name" TEXT NOT NULL,
    "git_type" TEXT NOT NULL,
    "git_repository" TEXT NOT NULL,
    "git_token" TEXT NOT NULL,
    "git_certificat" TEXT NOT NULL,
    "cluster_url" TEXT NOT NULL,
    "cluster_token" TEXT NOT NULL,
    "cluster_certificat" TEXT NOT NULL,
    "prune" BOOLEAN NOT NULL DEFAULT false,
    "createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" DATETIME NOT NULL
);

-- CreateIndex
CREATE UNIQUE INDEX "projects_name_key" ON "projects"("name");

Generate the client connexion to the database
```
npx prisma generate
```
Create the database using the creation script

The Prisma migration script will create the database in the prisma folder on in the DATABASE_URL javascript variable (.env file).
```
npx prisma migrate deploy
```

Update database

In the following exemple we are going to change the table name from project to gitRepositories.

Update the schema

vi prisma/schema.prisma

generator client {
provider = "prisma-client-js"
binaryTargets = ["native", "debian-openssl-3.0.x"]
}

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

model gitReposirories {
id    Int     @id @default(autoincrement())
name               String @unique
git_type           String
git_repository     String
git_token          String
git_certificat     String
cluster_url        String
cluster_token      String
cluster_certificat String
prune              Boolean
createdAt          DateTime @default(now())
updatedAt          DateTime @updatedAt
}

Generate database update strips

npx prisma migrate dev

Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": SQLite database "dev.db" at "file:../data/dev.db"

✔ Enter a name for the new migration: … rename_project_table
Applying migration `20240927122450_rename_project_table`

The following migration(s) have been created and applied from new schema changes:

migrations/
└─ 20240927122450_rename_project_table/
    └─ migration.sql

Your database is now in sync with your schema.

✔ Generated Prisma Client (v5.19.1) to ./node_modules/@prisma/client in 236ms

The file prisma/migrations/20240927122450_rename_project_table/migration.sql has been generated, it will transform the database to rename project table to gitRepository.

Apply the update to the database
```
npx prisma migrate deploy
```