JavaScript & TypeScript ORM Prisma

Our open-source project Starchart uses Prisma which is ORM for any JavaScript and TypeScript projects. As it states on the doc, it's the next generation ORM that provides a lot of convenient features. I'm really amazed how it makes things easier our project. Let me show how to use Prisma briefly in this blog.

Getting started

Once you have your project ready, you can install Prisma with below command.

npm install prisma --save-dev

And initiate Prisma in your project. You can choose your data source like SQLite, PostgreSQL, MySQL, MariaDB, MongoDB, MS SQL server, etc. In this case, I choose MySQL.

npx prisma init --datasource-provider mysql

Writing schema and init PrismaClient

Now it's ready to use Prisma. Let's set up some schemas.

model User {
  id         Int       @id @default(autoincrement())
  email      String    @unique
  createdAt  DateTime  @default(now())
  updatedAt  DateTime  @updatedAt
  name       String?
  public     Boolean   @default(false)
  record     Record[]
}

model Record {
  id           Int       @id @default(autoincrement()) 
  username     String   
  description  String    @db.VarChar(255)
  user         User      @relation(fields: [username], references: [username])
}

This is an example to show how to write Prisma schema. You can see how to set ID column, make a property unique in the table, set initial date property when created, as well as default value and length limitation. And the table relationship between User and Record. You can find more details on Prisma docs Defining attributes.

CRUD operation

Next step is to create a new SQL migration file and run the SQL migration file against database. Below command creates dev.db inside the prisma directory.

npx prisma migrate dev --name init

Now you need the PrismaClient initialization script.

import { PrismaClient } from '@prisma/client'

async function init() {
  const databaseUrl = new URL(process.env.DATABASE_URL);

  const prisma = new PrismClient({
    datasources: {
      db: {
        url: databaseUrl.toString(),
      }
    }
  });

  prisma.$connect();
}

You can write queries. Here's CRUD examples.

const user = await prisma.user.create({
  data: {
    name: 'John',
    email: '[e-mail]',
    records: {
        ....
    },
  },
});

await prisma.record.findMany({
  where: { username }
});

await prisma.record.update({
  where: { id },
  data: { description: 'Updated record' },
});

await prisma.record.delete({
  where: { id },
});

You can also use methods like findUnique, findFirst, updateMany, upsert, deleteMany, count, etc. If you have a bit of SQL experiences, you would understand what each of these command does. Let me leave a link to take a look at further details Prisma CRUD operations.

Seeding and Prisma Studio

What's great about Prisma is the various tools that makes your development easier. You can set up a seed script to create initial development data. In order to do so, you need to add a prisma script as below.

//In package.json for Javascript project
{
  "name": "starchart",
  "version": "0.0.1",
  "prisma": {
    "seed": "node prisma/seed.js"
  }
}

For Typescript, you need to install @types/node and add Typescript version script in package.jon.

{
  "name": "starchart",
  "version": "0.0.1",
  "prisma": {
    "seed": "ts-node prisma/seed.ts"
  },
  "devDependencies": {
    "@types/node": "^14.14.21",
    ....
  }
}

Then you can run npx prisma db seed to seed your database.

Prisma also provides the visual editor for easier data view/edit.

You can run npx prisma studio to see the visualized tables.

Conclusion

Developers need to work with database not just to write CRUD functions against database, but also database connection based on environment, create/change schema, migrate database, seed development database. Prisma can help you do these jobs easy in a sophisticated way. The fastest way to get familiar with Prisma is to go through the tutorial on the official website. I hope my article is helpful to know Prisma.