What is data modeling?

The term data modeling refers to the process of defining the shape and structure of the objects in an application, these objects are often called "application models". In relational databases (like PostgreSQL), they are stored in tables . When using document databases (like MongoDB), they are stored in collections.

Note: The content on this page is focused on relational databases.

Depending on the domain of your application, the models will be different. For example, if you're writing a blogging application, you might have models such as blog, author, article. When writing a carsharing app, you probably have models like driver, car, route. Application models enable you to represent these different entities in your code by creating respective data structures.

When modeling data, you typically ask questions like:

  • What are the main entities/concepts in my application?
  • How do they relate to each other?
  • What are their main characteristics/properties?
  • How can they be represented with my technology stack?

Data modeling without Prisma

Data modeling typically needs to happen on (at least) two levels:

  • On the database level
  • On the application level (i.e., in your programming language)

The way how the application models are represented on both levels might differ due to a few reasons:

  • Databases and programming languages use different data types
  • Relations are represented differently in a database than in a programming language
  • Databases typically have more powerful data modeling capabilities, like indexes, cascading deletes, or a variety of additional constraints (e.g. unique, not null, ...)
  • Databases and programming languages have different technical constraints

Data modeling on the database level

On the database level, models are represented via tables. For example, you might define a users table to store information about the users of your application. Using PostgreSQL, you'd define it as follows:

CREATE TABLE users (
user_id SERIAL PRIMARY KEY NOT NULL,
name VARCHAR(255),
email VARCHAR(255) UNIQUE NOT NULL,
isAdmin BOOLEAN NOT NULL DEFAULT false
);

A visual representation of the users table with some random data might look as follows:

user_idnameemailisAdmin
1Alicealice@prisma.iofalse
2Bobbob@prisma.iofalse
3Sarahsarah@prisma.iotrue

It has the following columns:

  • user_id: An integer that increments with every new record in the users table. It also represents the primary key for each record.
  • name: A string with at most 255 characters.
  • email: A string with at most 255 characters. Additionally, the added constraints express that no two records can have duplicate values for the email column, and that every record needs to have a value for it.
  • isAdmin: A boolean that indicates whether the user has admin rights (default value: false)

Data modeling on the application level

In addition to creating the tables that represent the entities from your application domain, you also need to create application models in your programming language. In object-oriented languages, this is often done by creating classes to represent your models. Depending on the programming language, this might also be done with interfaces or structs.

There often is a strong correlation between the tables in your database and the models you define in your code. For example, to represent records from the aforementioned users table in your application, you might define a JavaScript (ES6) class looking similar to this:

class User {
constructor(user_id, name, email, isAdmin) {
this.user_id = user_id
this.name = name
this.email = email
this.isAdmin = isAdmin
}
}

When using TypeScript, you might define an interface instead:

interface User {
user_id: Int
name: String
email: String
isAdmin: Boolean
}

Notice how the User model in both cases has the same properties as the users table in the previous example. While it's often the case that there's a 1:1 mapping between database tables and application models, it can also happen that models are represented completely differently in the database and your application.

With this setup, you can retrieve records from the users table and store them instances of your User type. The following example code snippet uses pg as the driver for PostgreSQL and creates a User instance based on the above defined JavaScript class:

const resultRows = await client.query('SELECT * FROM users WHERE user_id = 1')
const userData = resultRows[0]
const user = new User(userData.user_id, userData.name, userData.email, userData.isAdmin)
// user = {
// user_id: 1,
// name: "Alice",
// email: "alice@prisma.io",
// isAdmin: false
// }

Notice that in these examples, the application models are "dumb", meaning they don't implement any logic but their sole purpose is to carry data as plain old JavaScript objects.

Data modeling with ORMs

ORMs are commonly used in object-oriented languages to make it easier for developers to work with a database. The key characteristic of an ORM is that it lets you model your application data in terms of classes which are mapped to tables in the underlying database.

The main difference compared to the approaches explained above is these classes not only carry data but also implement a substantial amount of logic. Mostly for storage, retrieval, serialization, and deserialization, but sometimes they also implement business logic that's specific to your application.

This means, you don't write SQL statements to read and write data in the database, but instead the instances of your model classes provide an API to store and retrieve data.

Sequelize is a popular ORM in the Node.js ecosystem, this is how you'd define the same User model from the sections before using Sequelize's modeling approach:

class User extends Model {}
User.init(
{
user_id: {
type: Sequelize.INTEGER,
primaryKey: true,
autoIncrement: true,
},
name: Sequelize.STRING(255),
email: {
type: Sequelize.STRING(255),
unique: true,
},
isAdmin: Sequelize.BOOLEAN,
},
{ sequelize, modelName: 'user' }
)

To get an example with this User class to work, you still need to create the corresponding table in the database. With Sequelize, you have two ways of doing this:

  • Run User.sync() (typically not recommended for production)
  • Use Sequelize migrations to change your database schema

Note that you'll never instantiate the User class manually (using new User(...)) as was shown in the previous section, but rather call static methods on the User class which then return the User model instances:

const user = await User.findByPk(42)

The call to findByPk creates a SQL statement to retrieve the User record that's identified by the ID value 42.

The resulting user object is an instance of Sequelize's Model class (because User inherits from Model). It's not a POJO, but an object that implements additional behaviour from Sequelize.

Data modeling with Prisma

Depending on which parts of Prisma you want to use in your application, the data modeling flow looks slightly different. The following two sections explain the workflows for using only Prisma Client and using Prisma Client and Prisma Migrate (Experimental).

No matter which approach though, with Prisma you never create application models in your programming language by manually defining classes, interfaces, or structs. Instead, the application models are defined in your Prisma schema:

  • Only Prisma Client: Application models in the Prisma schema are generated based on the introspection of your database schema. Data modeling happens primarily on the database-level.
  • Prisma Client and Prisma Migrate (Experimental): Data modeling happens in the Prisma schema by manually adding application models to it. Prisma Migrate maps these application models to tables in the underlying database.

As an example, the User model from the previous example would be represented as follows in the Prisma schema:

model User {
user_id Int @id @default(autoincrement())
name String?
email String @unique
isAdmin Boolean @default(false)
}

Once the application models are in your Prisma schema (whether they were added through introspection or manually by you), the next step typically is to generate Prisma Client which provides a programmatic and type-safe API to read and write data in the shape of your application models.

Prisma Client uses TypeScript type aliases to represent your application models in your code. For example, the User model would be represented as follows in the generated Prisma Client library:

export declare type User = {
id: number
name: string | null
email: string
isAdmin: boolean
}

In addition to the generated types, Prisma Client also provides a data access API that you can use once you've installed the @prisma/client package:

import { PrismaClient } from '@prisma/client'
// or
// const { PrismaClient } = require('@prisma/client')
const prisma = new PrismaClient()
// use inside an `async` function to `await` the result
await prisma.user.findUnique(...)
await prisma.user.findMany(...)
await prisma.user.create(...)
await prisma.user.update(...)
await prisma.user.delete(...)
await prisma.user.upsert(...)

Using only Prisma Client

When using only Prisma Client and not using Prisma Migrate in your application, data modeling needs to happen on the database level via SQL. Once your SQL schema is ready, you use Prisma's introspection feature to add the application models to your Prisma schema. Finally, you generate Prisma Client which creates the types as well as the programmatic API for you to read and write data in your database.

Here is an overview of the main workflow:

  1. Change your database schema using SQL (e.g. CREATE TABLE, ALTER TABLE, ...)
  2. Run prisma introspect to introspect the database and add application models to the Prisma schema
  3. Run prisma generate to update your Prisma Client API

Using Prisma Client and Prisma Migrate (Experimental)

Warning: Prisma Migrate is currently in an Experimental state. It is ready to be tested and can be used in non-critical projects. However, it is not yet considered ready for production usage.

When using Prisma Migrate, you're not using SQL for any operations that change the database schema and you typically don't use Prisma's introspection feature either. Instead, you define your application models manually in the Prisma schema and use the prisma migrate subcommand to change the schema of your database.

Here is an overview of the main workflow:

  1. Manually change your application models in the Prisma schema (e.g. add a new model, remove an existing one, ...)
  2. Run prisma migrate save --experimental to create a migration on the file system
  3. Run prisma migrate up --experimental to apply the migration against your database
  4. Run prisma generate to update your Prisma Client API
Edit this page on GitHub