Getting Started with Prisma, SQLite, and Express

Prisma is a modern ORM that simplifies database management for Node.js and TypeScript projects. In this guide, we’ll explore how to set up Prisma with SQLite and integrate it into an Express application.

Why Prisma?

Prisma offers:

Type-safe queries: Automatically generated types for your database schema.
Ease of setup:Works seamlessly with many databases, including SQLite.
Developer-friendly tooling: Includes a powerful migration system and intuitive query APIs.

Prerequisites
Before diving in, ensure you have the following installed:

Node.js (LTS recommended)
npm or yarn

Initialize Your Project
Start by creating a new Node.js project:

mkdir prisma-express-app  
cd prisma-express-app  
npm init -y 

Install Dependencies
Install the necessary packages for Prisma, SQLite, and Express:

npm install express sqlite3  
npm install @prisma/client  
npm install -D prisma

Initialize Prisma
Run the following command to set up Prisma:

npx prisma init

This creates
A prisma/schema.prisma file to define your database schema.
A .env file for environment variables.

Configure SQLite
Update the .env file with the path to your SQLite database:

DATABASE_URL="file:./dev.db"

Update the prisma/schema.prisma file to use SQLite:

generator client {  
  provider = "prisma-client-js"  
}  

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

model User {  
  id    Int     @id @default(autoincrement())  
  name  String  
  email String  @unique  
}  

Here, we’ve defined a simple User model.

Run Migrations
Generate the SQLite database and apply the schema:

npx prisma migrate dev --name init  
This creates the dev.db SQLite file and applies your schema

.

Generate Prisma Client
Run the following command to generate the Prisma client:

npx prisma generate

Create an Express App
Set up an Express server and integrate Prisma:

const express = require('express');  
const { PrismaClient } = require('@prisma/client');  

const prisma = new PrismaClient();  
const app = express();  

app.use(express.json());  

// Create a user  
app.post('/users', async (req, res) => {  
  const { name, email } = req.body;  
  try {  
    const user = await prisma.user.create({ data: { name, email } });  
    res.status(201).json(user);  
  } catch (error) {  
    res.status(400).json({ error: error.message });  
  }  
});  

// Get all users  
app.get('/users', async (req, res) => {  
  const users = await prisma.user.findMany();  
  res.json(users);  
});  

const PORT = process.env.PORT || 3000;  
app.listen(PORT, () => console.log(`Server running on http://localhost:${PORT}`)); 

Test Your API
Start your server:

node index.js

Use tools like Postman or cURL to test the endpoints:

POST /users to create a new user.
GET /users to retrieve all users.

Explore Prisma Studio
Prisma Studio is a GUI for managing your database. Run:

npx prisma studio

Access it at http://localhost:5555.

Conclusion
You’ve now set up Prisma with SQLite and integrated it into an Express app! Prisma simplifies database management and gives you a clean, type-safe interface for working with your data.

If you want to dive deeper, check out the official Prisma documentation.

its just written by 0xapma