Working with Prisma: A Unified Database ORM for PostgreSQL, SQLite and Beyond

Prisma is a powerful, type-safe ORM that supports multiple databases like SQLite, PostgreSQL, MySQL, and more. One of Prisma’s key strengths is that you can write the same database queries regardless of the underlying database you’re using. This means that whether you're using SQLite for local development or PostgreSQL in production, your Prisma queries remain the same.

In this article, we'll explore setting up Prisma with a database (SQLite for this example, but the process is similar for PostgreSQL or others), and demonstrate various CRUD operations using Prisma's intuitive query syntax.

Why Prisma?

Cross-Database Compatibility: Prisma abstracts database differences, allowing you to switch databases without rewriting your queries.
Type Safety: Prisma integrates well with TypeScript, offering compile-time query validation.
Data Modeling: Easily define relationships and models in a unified schema.
Query Optimization: Prisma’s generated queries are optimized for performance.

Step 1: Setting Up Prisma

Regardless of the database (SQLite, PostgreSQL, MySQL), the setup is straightforward.

Initialize your project:
```
 npm init -y
```
Install Prisma and the required database client: For SQLite:
```
 npm install prisma --save-dev
```
For PostgreSQL:
```
 npm install prisma --save-dev
```
Initialize Prisma:
```
 npx prisma init
```
This will create a .env file, and a prisma/schema.prisma file. In the .env file, set your database URL according to the database you are using.

For SQLite:
```
 npx codeDATABASE_URL="file:./dev.db"
```
For PostgreSQL:
```
 npx codeDATABASE_URL="postgresql://username:password@localhost:5432/mydb"
```

Step 2: Defining Your Models

In prisma/schema.prisma, define your models. These models are the same whether you're using SQLite, PostgreSQL, or any other database.

prismaCopy codegenerator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "sqlite" // Change this to "postgresql" or "mysql" if needed
  url      = env("DATABASE_URL")
}

model User {
  id       Int     @id @default(autoincrement())
  email    String  @unique
  name     String?
  articles Article[]
}

model Article {
  id       Int    @id @default(autoincrement())
  title    String
  body     String?
  author   User    @relation(fields: [authorId], references: [id])
  authorId Int
}

Step 3: Running Migrations

Once you've defined your models, run the following command to create the necessary tables in your database:

npx prisma migrate dev --name init

This will create the initial tables for User and Article in your database.

Step 4: Performing CRUD Operations

Prisma queries are consistent across all databases. You don’t need to modify them if you switch from SQLite to PostgreSQL.

Setting Up Prisma Client

First, you need to import and initialize the Prisma Client. This allows you to interact with your database.

tsCopy codeimport { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

async function main() {
  // Your Prisma queries will go here
}

Creating a New User

Here’s how you create a new user:

tsCopy codeconst user = await prisma.user.create({
  data: {
    name: 'John Doe',
    email: 'john@gmail.com'
  }
});
console.log('Created User:', user);

Fetching All Users

You can retrieve all users with this simple query:

tsCopy codeconst users = await prisma.user.findMany();
console.log('All Users:', users);

Creating an Article and Associating It with a User

You can also create an article and link it to an existing user:

tsCopy codeconst article = await prisma.article.create({
  data: {
    title: 'First Article',
    body: 'This is the first article',
    author: {
      connect: { id: user.id }  // Connect to an existing user
    }
  }
});
console.log('Created Article:', article);

Fetching All Articles

To get all the articles, use:

tsCopy codeconst articles = await prisma.article.findMany();
console.log('All Articles:', articles);

Updating a User

To update a user's information:

tsCopy codeconst updatedUser = await prisma.user.update({
  where: { id: user.id },
  data: { name: 'John Doe Jr.' },
});
console.log('Updated User:', updatedUser);

Deleting an Article

You can delete an article by its ID:

tsCopy codeconst deletedArticle = await prisma.article.delete({
  where: { id: article.id },
});
console.log('Deleted Article:', deletedArticle);

Step 5: Running the Project

You can run your project using:

npx ts-node index.ts

Additionally, you can use Prisma Studio to view and manage your database entries in a web-based interface:

npx prisma studio

Conclusion

Prisma is a powerful and flexible ORM that works across different databases like SQLite, PostgreSQL, and MySQL. Regardless of the database, Prisma ensures that your queries remain consistent and type-safe. Whether you're developing locally with SQLite or deploying to production with PostgreSQL, Prisma simplifies database interactions, so you can focus on building features.

The power of Prisma lies in its ability to allow you to switch databases seamlessly, making it a great choice for projects that might scale from a small level.