ORM libraries provide an object-oriented layer of abstraction on top of database (typically SQL) interaction. In this lab we're going to look at Prisma, the most popular ORM for Node.js projects. With 5.6M weekly npm downloads, it is the modern choice for new projects with excellent TypeScript support and developer experience.
Whether to use an ORM or not is a contentious topic, but the reality is that you're mostly going to use it or not based on whether the project you're on uses it or not. You will see it if you stick in web/mobile development. My advice is that you absolutely need to know SQL, then what you choose to use over that becomes preference not necessity.
Other popular Node.js ORMs and query builders:
Articles on choosing and using ORMs:
Grab the repo containing a simple database for this lab here:
https://classroom.github.com/a/QlD9XLtZ
Open the database in DBBrowser and look at the two tables we'll be using.
In the repo directory with the database file, create a new project and install Prisma.
npm init -y npm install prisma@6 @prisma/client@6
Note: We use Prisma 6.x which has built-in SQLite support. Prisma 7+ requires database adapters.
Initialize Prisma with SQLite as the database provider:
npx prisma init --datasource-provider sqlite
This creates three files:
prisma/schema.prisma: The schema file where models will be definedprisma.config.ts: Configuration file that loads environment variables.env: Environment file with the database connection stringThe .env file stores sensitive configuration data like database URLs, API keys, and passwords.
It should NEVER be committed to version control (add it to .gitignore).
This keeps sensitive data out of your repository and allows different environments
(development, testing, production) to use different configurations without changing code.
Install the dotenv library:
npm install dotenv
Add an import statement to the top of your prisma.config.ts. This automatically loads all the key value
pairs from the .env file into a dictionary called env:
import "dotenv/config";
Edit the .env file to point to your database:
DATABASE_URL="file:../addresses.sqlite"
Note: The path is relative to the prisma directory, thus the .. for addresses.sqlite
in the root of the project. You can also use an absolute path: file:/full/path/to/addresses.sqlite
ORMs use a class, called a model, to represent each table in the database. Instead of
writing SQL queries, your code will call methods on the ORM models. One of the features that makes ORM
popular is automatic synchronization between the database and the models. You can:
Since we have already been working with databases and SQL already, we'll take option one. Prisma
uses a command called db pull (also known as introspection) to generate models from an existing database.
Documentation is available here: https://www.prisma.io/docs/concepts/components/introspection.
Run this command to introspect your database and generate the schema:
npx prisma db pull
This reads your database structure and updates the prisma/schema.prisma file with model definitions.
Open prisma/schema.prisma and you'll see models for each table in the database:
// This is your Prisma schema file
generator client {
provider = "prisma-client"
output = "../generated/prisma"
}
datasource db {
provider = "sqlite"
url = env("DATABASE_URL")
}
model Person {
id Int @id @default(autoincrement())
first_name String?
last_name String?
PhoneNumber PhoneNumber[]
}
model PhoneNumber {
id Int @id @default(autoincrement())
person_id Int?
area_code String?
exchange String?
number String?
Person Person? @relation(fields: [person_id], references: [id], onDelete: NoAction, onUpdate: NoAction)
}
Prisma uses its own schema language. The models are defined in a declarative way that's easy to read and understand.
Because we are using regular JavaScript instead of TypeScript, you need to change the generator block
in your prisma/schema.prisma from the default to the below:
generator client {
provider = "prisma-client-js"
}
After introspection, you need to generate the Prisma Client - the typed query builder that you'll use in your code:
npx prisma generate
This creates the custom @prisma/client based on your schema. You'll need to run this
command whenever you change the schema.
Create a test.js file to test connecting to the database using the code below.
// import the Prisma Client
const { PrismaClient } = require('@prisma/client');
// instantiate the client
const prisma = new PrismaClient();
// need an async function to use await
const test = async () => {
// test the connection by querying the database
try {
const count = await prisma.person.count();
console.log(`Connected to database. Found ${count} people.`);
} catch(err) {
console.log("Failed to connect: " + err);
} finally {
// always disconnect when done
await prisma.$disconnect();
}
}
test();
Run the file at the command prompt with:
node test.js
Now that you have the Prisma Client generated, you can use it to create, update, or query the
database from JavaScript. The database models are accessible as properties of the prisma client instance:
prisma.person, prisma.phoneNumber, etc. Under the hood, SQL is being generated and sent to the
database.
(You probably will want to open the database in DB Browser to see what's happening. Make sure that you push the Write Changes button if you make any manual changes or it will lock the database and your code queries will time out.)
Add the following queries to test.js (in the try block) and dump each result to the console to inspect it. Use this code to dump it to the console in easily readable JSON:
console.log(JSON.stringify(result, null, 2));
findMany() - retrieves all records (equivalent to SELECT *)findUnique() - retrieves a single record by unique field (typically id)findFirst() - retrieves the first record matching a condition (e.g. id > 1)create()update()delete()ORMs can be designed using different patterns for how they interact with database objects. Understanding these patterns helps you work more effectively with any ORM you encounter.
Prisma uses the Data Mapper pattern, which separates data (plain objects) from database operations (client methods). When you query the database, you get back plain JavaScript objects with no methods attached. If you need to get updated data from the database or write back changes, you just make new queries.
In contrast, many ORMs use the Active Record pattern, which strongly follows Object-Oriented Programming. You create
classes that represent each database table. When you query the database, you get back instances representing specific rows. Operations
on those instances are propagated to the database. Below is an example in sequelize:
// person is an instance of the Person class const person = await Person.findByPk(1); // modify the instance and "save" to the db person.first_name = 'Janet'; await person.save(); // the save generates a SQL update, but that is abstracted away
Neither pattern is universally better - they have different trade-offs. Data Mapper is more prevalent now, as transparency is generally more highly valued than the convenience and elegance of clever abstractions.
In professional development, you'll likely encounter both patterns. Understanding the difference helps you adapt to whichever ORM your project uses.
In the schema.prisma file, the relation between Person and PhoneNumber is defined in both models:
model Person {
id Int @id @default(autoincrement())
first_name String?
last_name String?
PhoneNumber PhoneNumber[] // One person has many phone numbers
}
model PhoneNumber {
id Int @id @default(autoincrement())
number String?
person_id Int?
Person Person? @relation(fields: [person_id], references: [id]) // Each number belongs to a person
}
Relations allow you to use the models to query more complex data the way that you use joins in SQL. In this case, we might be interested in retrieving a person and all their phone numbers.
Lazy loading refers to waiting to retrieve data (the phone numbers) until you need it. In prisma, you do this by simply making another query.
For any one of the people you've already retrieved, use a findMany to retrieve their phone numbers by person_id and dump them to the console.
Eager loading refers to retrieving all the data (the person and their phone numbers) at once. To do this, you use
the include option in your query. This is the more common pattern in Prisma:
Show all the people in the database with their phone numbers included in the objects.
const peopleWithNumbers = await prisma.person.findMany({
include: { PhoneNumber: true }
});
Two things should strike you about the output from this call:
You can also select specific fields and nest multiple levels of relations using Prisma's SQL specification approach:
const peopleWithNumbers = await prisma.person.findMany({
select: {
first_name: true,
last_name: true,
PhoneNumber: {
select: {
number: true
}
}
}
});
Prisma includes several powerful tools beyond just querying:
npx prisma studio to open it in your browser.npx prisma migrate dev.Try running Prisma Studio to visually explore your database:
npx prisma studio