Engineering Databases

In the ADGSTUDIOS Software Engineering company we use prisma to engineer the schema and databases.

The benefits of this approach are:

• The app schema can be scaled, datatypes can be changed and the database can be migrated without having to change the code.
• The database can be migrated to any database engine supported by prisma.
• It generates libraries for the backend to access database programmatically.
• When we do any CRUD operations, we can use the generated libraries to access the database. Everything is parameterized and safe from SQL injection attacks.

To get started with prisma, we need to install the prisma cli globally.

npm install -g prisma

Create a folder called prisma in the root of the project. This folder will contain the schema and the migrations.

mkdir prisma

Create a file called schema.prisma in the prisma folder. This file will contain the schema of the database.

touch prisma/schema.prisma

The schema is written in the prisma schema language. The schema language is a declarative language that describes the data model of your application. It is used to define the data model in your Prisma schema file (schema.prisma) and to generate Prisma Client.

In the schema.prisma file, we will define the schema of the database. The schema is written in the prisma schema language. The schema language is a declarative language that describes the data model of your application. It is used to define the data model in your Prisma schema file (schema.prisma) and to generate Prisma Client.

Creating a schema for the database

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}
 
generator client {
  provider = "prisma-client-js"
}
 
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  password  String
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  deletedAt DateTime?
  role      Role     @default(USER)
  projects  Project[]
}
 
model Project {
  id        Int      @id @default(autoincrement())
  name      String
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  deletedAt DateTime?
  users     User[]
}
 
enum Role {
  USER
  ADMIN
}

The schema defines the data model of the application. It defines the tables, columns, relationships and constraints of the database.

A better guide to the schema language can be found here (opens in a new tab).

If you want to quickly add or remove columns, tables or relationships, you can use the prisma studio. Prisma studio is a visual editor for the database. It is a web app that runs on your local machine. It is a great tool for quickly iterating on your data model.

To start the prisma studio, run the following command:

npx prisma studio

Quite cool right? You can now add or remove columns, tables or relationships and see the changes in the database.

Migrations

Migrations are a way to make database changes like creating a table, adding a column, or updating a column type. Migrations are done using the prisma cli.

To create a migration, run the following command:

npx prisma migrate dev --name init

This will create a migration file in the prisma/migrations folder. The migration file contains the SQL statements to be executed to make the changes to the database.

To apply the migration, run the following command:

npx prisma migrate dev

To revert the migration, run the following command:

npx prisma migrate reset

DevOps Usage

In terms of DevOps we can use this to create a database for each environment. We can also use this to create a database for each developer. This way, each developer can have their own database to work with. This will prevent developers from stepping on each other's toes.

Ready to use

Once everything is done you can proceed to use the database in your code. To do this, you need to generate the prisma client. The prisma client is a type-safe database client auto-generated based on the data model in your Prisma schema. It provides you with a ready-to-use and fully type-safe API to perform CRUD operations against your database otherwise you can proceed to use the SQL Server Module to connect to the database and process more complex queries.