Releases & MaintenanceFeatures in Preview

Datamodel v1.1

Overview

Over the last months, we have worked with the community to define a new datamodel specification for Prisma. This new version is called datamodel v1.1 and is currently available in an early preview.

Please help us test the implementation of the new datamodel and share your feedback or report problems by opening an issue in the feedback repo.

This document explains how to get started:

Prerequises & Installation
Option 1: Get started from scratch
Option 2: Get started with an existing database
Option 3: Upgrading from datamodel v1 to datamodel v1.1

Motivation

The main motivations for the new datamodel are:

Enabling more control over how Prisma lays out the database schema
Consolidated migrations (unifying the "active" and "passive" database connectors)

More control over database layout

The current datamodel is opinionated about the database layout in many ways. Here are a few things that are now possible with datamodel v1.1:

Model/field names in the datamodel can differ from the names of the underlying tables/columns
Specify whether a relation should use a JOIN table or inline references
Use any field as id field
Use any field as createdAt or updatedAt fields

With the current datamodel, developers need to decide whether Prisma should perform database migrations for them, by setting the migrations flag in PRISMA_CONFIG when the Prisma server is deployed. In datamodel v1.1 the migrations flag is removed, meaning developers can at all times either migrate the database manually or use Prisma for the migration.

What's new in datamodel v1.1?

Directives

`@db(name: String!)`

The @db directive is used to control how the datamodel is mapped to the underlying database schema. Specifically it allows developers to:

Map the name of a Prisma model to a certain table in the underlying database
Map the name of a field on a Prisma model to a certain column in the underlying database

Usage

The @db directive can be applied to:

Types (i.e. models)
Fields

Usage of the @db directive is optional. If it's not applied, the table/column name in the underlying database will default to name that's used in the Prisma datamodel.

Examples

A model applying the directive on a type- and field-level. The underlying table will be called user and the column full_name

type User @db(name: "user") {
  id: ID! @id
  name: String! @db(name: "full_name")
}

A model omitting the directive. The underlying table will be called User

type User {
  id: ID! @id
  name: String!
}

`@id`

The @id is used to indicate that a (scalar) field on a model is used as the primary identifier for that model. It implies that this field is unique.

Usage

The @id directive can be applied to scalar fields of types:

ID
UUID
Int

It can be used at most once per model.

The @id directive takes one argument: @id(strategy: Strategy!) where Strategy is an enum with the following values:

AUTO (default): Based on the type of the field an id will be generated automatically.
- Int: An identity column is used to in the underlying database. For each database a matching implementation is selected, e.g. a column of type SERIAL for Postgres.
- UUID and ID: Prisma will generate a random value for the type when a record is created.
NONE: The id does not get auto-generated. The id field is then required whenever a record for that model is created.
SEQUENCE: A sequence in the underlying database will be created to create IDs for records of this models. When this strategy is used the @sequence directive must be specified. This value can only be used if the field on which the @id directive is applied is of type Int (it doesn't work with ID or UUID).

Examples

The _myID field is used as the primary identifier of the User model. IDs are auto-generated by Prisma (the strategy argument could also be omitted as it specifies the default value).

type User {
  myId: UUID! @id(strategy: AUTO)
  name: String!
}

The _myID field is used as the primary identifier of the User model. IDs must be provided whenever a new record is created.

type User {
  myId: ID! @id(strategy: NONE)
  name: String!
}

The _myID field is used as the primary identifier of the User model. IDs are generated based on the MY_SEQENCE sequence in the database.

type User {
  myID: Int!
    @id(strategy: SEQUENCE)
    @sequence(name: "MY_SEQUENCE", initialValue: 1, allocationSize: 100)
  name: String!
}

`@sequence`

The @sequence directive is used to specify how a primary identifier for a model is generated.

Usage

The @sequence directive can only be applied to fields of type Int that are annotated with the @id(strategy: SEQUENCE) directive. It takes three arguments:

name: String!: The name of the database sequence.
initialValue: Int!: The initial value for the database sequence.
allocationSize: Int!: The allocation size of the database sequence.

`@createdAt` & `@updatedAt`

The @createdAt and @updatedAt directives can be used to automatically track the times for when a record was created and last updated. The fields annotated with either of those directives become read-only.

Usage

The @createdAt and @updatedAt directives can be applied to any fields of type DateTime!.

Examples

The myCreatedAt and myUpdatedAt fields track the time when a User record was created and last updated.

type User {
  id: ID! @id
  myCreatedAt: DateTime! @createdAt
  myUpdatedAt: DateTime! @updatedAt
}

`@relation`

See the the Relations section for more info.

`@relationTable`

See the the Relations section for more info.

Relations

In Prisma, a relation connects two models. A direction can be uni- or bidirectional:

Unidirectional: Only one model has a relation field to another model
Bidirectional: Both models have relation fields to each other

A relation field is a field on a model that has another model as its type. For example, in the following datamodel author and posts are relation fields that mark a bidirectional relation (1:n) between the User and Post models:

type User {
  id: ID! @id
  posts: [Post!]!
}

type Post {
  id: ID! @id
  author: User!
}

You can control how a relation is represented in the underlying database via the @relation and @relationTable directives. A relation can be represented in either of two ways:

With a relation table: Prisma tracks the relation via a dedicated table that contains two columns which refer to the IDs of each model.
As an inline relation: Prisma tracks the relation via a foreign key in a column (not available for n:m relations)

1:1 relations

When defining a 1:1 relation between two models, you must add the @relation directive to one end of the relation. Otherwise Prisma doesn't know how it should lay out the relation in the underlying database.

Inline

type User {
  id: ID! @id
  profile: Profile! @relation(link: INLINE)
}

type Profile {
  id: ID! @id
  user: User!
}

This stores the primary key of Profile in the profile column on the User table:

CREATE TABLE "default$default"."User" (
    "id" varchar(25) NOT NULL,
    "profile" varchar(25),
    PRIMARY KEY ("id")
);

Relation table

Generatic relation table

type User {
  id: ID! @id
  profile: Profile! @relation(link: TABLE)
}

type Profile {
  id: ID! @id
  user: User!
}

This creates the following relation table:

CREATE TABLE "default$default"."_PostToUser" (
  "A" varchar(25) NOT NULL,
  "B" varchar(25) NOT NULL
);

Relation table with a custom name (prepended with an underscore)

type User {
  id: ID! @id
  profile: Profile! @relation(link: TABLE, name: "MyRelation")
}

type Profile {
  id: ID! @id
  user: User!
}

This creates the following relation table:

CREATE TABLE "default$default"."_MyRelation" (
  "A" varchar(25) NOT NULL,
  "B" varchar(25) NOT NULL
);

Customized relation table with `@relationTable`

type User {
  id: ID! @id
  profile: Profile! @relation(link: TABLE, name: "MyRelation")
}

type Profile {
  id: ID! @id
  user: User!
}

type MyRelation @relationTable {
  user: User!
  profile: Profile!
}

This creates the following relation table:

CREATE TABLE "default$default"."MyRelation" (
  "profile" varchar(25) NOT NULL,
  "user" varchar(25) NOT NULL
);

The @relationTable directive should be used when you want to:

remove the underscore in front of the relation name
call the foreign key columns in the relation table something else than A and B

So you need it in the active case when you want to control that layout. More prominently you need it in the passive case, when you want to connect to an existing database that does not match the prisma conventions.

1:n relations

When defining a 1:n relation between two models, the @relation directive is optional. Prisma defaults to an inline relation.

Inline

type User {
  id: ID! @id
  posts: [Post!]!
}

type Post {
  id: ID! @id
  author: User! @relation(link: INLINE)
}

This stores the primary key of User in the author column on the Post table:

CREATE TABLE "relations$dev1"."Post" (
    "id" varchar(25) NOT NULL,
    "author" varchar(25),
    PRIMARY KEY ("id")
);

Note that in this case the @relation directive could also be omitted because this is the default behaviour for 1:n relations.

Relation table

Generatic relation table

type User {
  id: ID! @id
  posts: [Post!]!
}

type Post {
  id: ID! @id
  author: User! @relation(link: TABLE)
}

This creates the following relation table:

CREATE TABLE "default$default"."_PostToUser" (
  "A" varchar(25) NOT NULL,
  "B" varchar(25) NOT NULL
);

Relation table with a custom name (prepended with an underscore)

type User {
  id: ID! @id
  posts: [Post!]!
}

type Post {
  id: ID! @id
  user: User! @relation(link: TABLE, name: "MyRelation")
}

This creates the following relation table:

CREATE TABLE "default$default"."_MyRelation" (
  "A" varchar(25) NOT NULL,
  "B" varchar(25) NOT NULL
);

Customized relation table with `@relationTable`

type User {
  id: ID! @id
  posts: [Post!]!
}

type Post {
  id: ID! @id
  user: User! @relation(link: TABLE, name: "MyRelation")
}

type MyRelation @relationTable {
  user: User!
  post: Post!
}

This creates the following relation table:

CREATE TABLE "default$default"."MyRelation" (
    "post" varchar(25) NOT NULL,
    "user" varchar(25) NOT NULL
);

n:m relations

Inline relation

Not applicable

Relation table

Generatic relation table

type Category {
  id: ID! @id
  posts: [Post!]!
}

type Post {
  id: ID! @id
  categories: [Category!]! @relation(link: TABLE)
}

This creates the following relation table:

CREATE TABLE "default$default"."_PostToCategory" (
  "A" varchar(25) NOT NULL,
  "B" varchar(25) NOT NULL
);

Relation table with a custom name (prepended with an underscore)

type Category {
  id: ID! @id
  posts: [Post!]!
}

type Post {
  id: ID! @id
  categories: [Category!]! @relation(link: TABLE, name: "MyRelation")
}

This creates the following relation table:

CREATE TABLE "default$default"."_MyRelation" (
  "post" varchar(25) NOT NULL,
  "category" varchar(25) NOT NULL
);

Customized relation table with `@relationTable`

type Category {
  id: ID! @id
  posts: [Post!]!
}

type Post {
  id: ID! @id
  categories: [Category!]! @relation(link: TABLE, name: "MyRelation")
}

type MyRelation @relationTable {
  user: User!
  post: Post!
}

This creates the following relation table:

CREATE TABLE "default$default"."MyRelation" (
    "post" varchar(25) NOT NULL,
    "cagtegory" varchar(25) NOT NULL
);

Migrations and introspection with the Prisma CLI

`prisma deploy`

The command prisma deploy got enhanced. From now on, there will be two modes that allow developers to choose whether the Prisma migration system should migrate their database:

prisma deploy: This command reads your datamodel and migrates the underlying database to match it.
prisma deploy --no-migrate: This command does not migrate the underlying database. Instead, it expects the database schema to be in the right state already (which means you need to manually migrate the database before running prisma deploy --no-migrate)! If the datamodel and database schema do not align, prisma deploy will throw an error.

`prisma introspect`

The command prisma introspect has been adapted to output the new datamodel format:

prisma introspect

The command has been improved so that it will pay respect to existing datamodel files. This way you can use it to translate your database schema into a datamodel while the introspection will preserve the ordering of types and fields of your existing datamodel file.

Common workflows

You can fluently switch between those two commands however you like. For instance you could use prisma deploy for most of your migration needs. When you hit an advanced use case where the Prisma migration system is not yet powerful enough, you can:

Migrate the database manually
Use prisma introspect to update your datamodel with those changes from the database
Use prisma deploy --no-migrate to let the Prisma server know about that

Prerequisites & Installation

The datamodel v1.1 is available in the latest Prisma version.

1. Install the latest Prisma CLI

npm install -g prisma

2. Use the latest Prisma Docker image

In the Docker Compose file for your Prisma server, make sure to use the latest Docker image:

prismagraphql/prisma:1.31

Get started from scratch

1. Create Docker Compose file

Create a new file callled docker-compose.yml and add the following code to it:

version: '3'
services:
  prisma:
    image: prismagraphql/prisma:1.31
    restart: always
    ports:
      - '4466:4466'
    environment:
      PRISMA_CONFIG: |
        port: 4466
        databases:
          default:
            connector: postgres
            host: postgres
            user: prisma
            password: prisma
            port: 5432
  postgres:
    image: postgres
    restart: always
    ports:
      - '5432:5432'
    environment:
      POSTGRES_USER: prisma
      POSTGRES_PASSWORD: prisma
    volumes:
      - postgres:/var/lib/postgresql/data
volumes:
  postgres: ~

Note that in this example, we're using a PostgreSQL database. We're also exposing the PostgreSQL port 5432 so that you can e.g. connect to it from a database GUI like Postico.

2. Deploy Prisma server

Run the following command to deploy your server:

docker-compose up -d

3. Setup Prisma project

A Prisma project always requires at least two files: prisma.yml and a datamodel file (e.g. called datamodel.prisma).

Create the following prisma.yml:

endpoint: http://localhost:4466
datamodel: datamodel.prisma

And this datamodel.prisma:

type User @db(name: "user") {
  id: ID! @id
  createdAt: DateTime! @createdAt
  email: String! @unique
  name: String
  role: Role @default(value: USER)
  posts: [Post!]!
  profile: Profile @relation(link: INLINE)
}

type Profile @db(name: "profile") {
  id: ID! @id
  user: User!
  bio: String!
}

type Post @db(name: "post") {
  id: ID! @id
  createdAt: DateTime! @createdAt
  updatedAt: DateTime! @updatedAt
  author: User!
  published: Boolean! @default(value: false)
  categories: [Category!]! @relation(link: TABLE, name: "PostToCategory")
}

type Category @db(name: "category") {
  id: ID! @id
  name: String!
  posts: [Post!]! @relation(name: "PostToCategory")
}

type PostToCategory @db(name: "post_to_category") @relationTable {
  post: Post
  category: Category
}

enum Role {
  USER
  ADMIN
}

Let's understand some important bits of the datamodel:

Each model is mapped a table that's named after the model but lowercased using the @db directive.
There are the following relations:
- 1:1 between User and Profile
- 1:n between User and Post
- n:m between Post and Category
The 1:1 relation between User and Profile is annotated with @relation(link: INLINE) on the User model. This means user records in the database have a reference to a profile record if the relation is present (because the profile field is not required, the relation might just be NULL). An alternative to INLINE is TABLE in which case Prisma would track the relation via a dedicated relation table.
The 1:n relation between User and Post is is tracked inline the relation via the author column of the post table, i.e. the @relation(link: INLINE) directive is inferred on the author field of the Post model.
The n:m relation between Post and Category is tracked via a dedicated relation table called PostToCategory. This relation table is part of the datamodel and annotated with the @relationTable directive.
Each model has an id field annotated with the @id directive.
For the User model, the database automatically tracks when a record is created via the field annotated with the @createdAt directive.
For the Post model, the database automatically tracks when a record is created and updated via the fields annotated with the @createdAt and @updatedAt directives.

4. Deploy datamodel (migrate database)

With prisma.yml and datamodel.prisma in place, you can deploy your datamodel:

prisma deploy

The underlying database that's created by Prisma is called after the service name and stage (separated by the $ character). Because the endpoint in prisma.yml doesn't use these properties, Prisma defaults to default for both. The database name consequently is default$default.

Here is an overview of the tables that are being created in default$default:

`catgegory`

CREATE TABLE "default$default"."category" (
    "id" varchar(25) NOT NULL,
    "name" text NOT NULL,
    PRIMARY KEY ("id")
);

index_name	index_algorithm	is_unique	column_name
`category_pkey`	`BTREE`	`TRUE`	`id`

`post`

CREATE TABLE "default$default"."post" (
    "id" varchar(25) NOT NULL,
    "author" varchar(25),
    "published" bool NOT NULL,
    "createdAt" timestamp(3) NOT NULL,
    "updatedAt" timestamp(3) NOT NULL,
    "title" text NOT NULL,
    PRIMARY KEY ("id")
);

index_name	index_algorithm	is_unique	column_name
`post_pkey`	`BTREE`	`TRUE`	`id`

`post_to_category`

CREATE TABLE "default$default"."post_to_category" (
    "category" varchar(25) NOT NULL,
    "post" varchar(25) NOT NULL
);

index_name	index_algorithm	is_unique	column_name
`post_to_category_AB_unique`	`BTREE`	`TRUE`	`category`,`post`
`post_to_category_B`	`BTREE`	`FALSE`	`post`

`profile`

CREATE TABLE "default$default"."profile" (
    "id" varchar(25) NOT NULL,
    "bio" text NOT NULL,
    PRIMARY KEY ("id")
);

index_name	index_algorithm	is_unique	column_name
`profile_pkey`	`BTREE`	`TRUE`	`id`

`user`

CREATE TABLE "default$default"."user" (
    "id" varchar(25) NOT NULL,
    "email" text NOT NULL,
    "name" text,
    "role" text NOT NULL,
    "createdAt" timestamp(3) NOT NULL,
    "profile" varchar(25),
    PRIMARY KEY ("id")
);

index_name	index_algorithm	is_unique	column_name
`user_pkey`	`BTREE`	`TRUE`	`id`
`default$default.user.email._UNIQUE`	`BTREE`	`TRUE`	`email`

5. View and edit your data

From here on, you can use the Prisma client if you want to access the data in your database programmatically. In the following, we'll highlight two visual ways to interact with the data.

Using Prisma Admin

To access your data in Prisma Admin, you need to navigate to the Admin endpoint of your Prisma project: http://localhost:4466/_admin

Using TablePlus

While Prisma Admin is focussed on convenient data management workflows, you can also connect to your database from other database GUIs. In contrast to Admin, these tools typically highlight the actual database structure (instead of the Prisma datamodel abstraction). In this example, we're using TablePlus.

To connect to your database from a database GUI, you need to map the port in the database configuration of your Docker Compose file. In our case, this is why we're adding the 5432:5432 line to it.

When opening TablePlus, you need to:

Create a new connection
Select PostgreSQL
Provide a the database connection details:
- Name: Can be anything, e.g. Local PostgreSQL
- Host/Socket: localhost
- Port: 5432
- User: prisma
- Password: prisma
- Database: prisma
Click Connect

After you connected to the database, you can explore the data and table structure in the TablePlus GUI:

Get started with an existing database

1. Introspect database & generate datamodel

If you have a database that already contains some data, you can get started by introspecting the database schema and let Prisma generate the right datamodel for you.

To get started, run:

prisma init

This launches an interactive wizard that guides you through the process of connecting to your existing database. The following interaction shows how to connect to a local PostgreSQL database:

$ prisma init myapp
? Set up a new Prisma server or deploy to an existing server? Use existing database
? What kind of database do you want to deploy to? PostgreSQL
? Does your database contain existing data? Yes
? Enter database host localhost
? Enter database port 5432
? Enter database user prisma
? Enter database password prisma
? Enter database name (the database includes the schema) myblog
? Use SSL? No
? Please select the schema you want to introspect public
Introspecting database public 113ms
Created datamodel definition based on 6 tables.
? Select the programming language for the generated Prisma client Don't generate

Created 3 new files:

  prisma.yml           Prisma service definition
  datamodel.prisma    GraphQL SDL-based datamodel (foundation for database)
  docker-compose.yml   Docker configuration file

Next steps:

  1. Start your Prisma server: docker-compose up -d
  2. Deploy your Prisma service: prisma deploy
  3. Read more about introspection:
     http://bit.ly/prisma-introspection

Once the process has terminated, the Prisma CLI will have generated the following files inside the new myapp directory for you:

docker-compose.yml: The Docker Compose file that specifies your Prisma server and its database connection.
prisma.yml: The root configuration file for your Prisma project.
datamodel.prisma: Your database schema represented in datamodel v1.1 syntax.

2. Deploy Prisma setup

Now you can deploy your Prisma setup:

cd myapp
docker-compose up -d
prisma deploy

3. View and edit your data

From here on, you can use the Prisma client if you want to access the data in your database programmatically.

To access your data in Prisma Admin, you need to navigate to the Admin endpoint of your Prisma project: http://localhost:4466/_admin

Upgrading to datamodel v1.1

1. Datamodel v1 setup

Assume you have a deployed Prisma project with the following datamodel:

type User {
  id: ID! @id
  createdAt: DateTime!
  email: String! @unique
  name: String
  role: Role @default(value: "USER")
  posts: [Post!]!
  profile: Profile
}

type Profile {
  id: ID! @id
  user: User!
  bio: String!
}

type Post {
  id: ID! @id
  createdAt: DateTime!
  updatedAt: DateTime!
  title: String!
  published: Boolean! @default(value: false)
  author: User!
  categories: [Category!]!
}

type Category {
  id: ID! @id
  name: String!
  posts: [Post!]!
}

enum Role {
  USER
  ADMIN
}

When using the datamodel v1, the following tables are created by Prisma in the underlying database:

User
Profile
Post
Category
_CategoryToPost
_PostToUser
_ProfileToUser
_RelayId

Each relation is represented via a relation table and the _RelayId table to be able to identity any record by its ID. With the datamodel v1, these are Prisma opinionations that can not be worked around.

2. Enable datamodel v1.1

To enable the datamodel v1.1, you need to upgrade to the latest Prisma version (Prisma server and CLI).

For example, if you have are connecting to a local PostgreSQL database, your updated docker-compose.yml might look as follows:

version: '3'
services:
  prisma:
    image: prismagraphql/prisma:1.31
    restart: always
    ports:
      - '4466:4466'
    environment:
      PRISMA_CONFIG: |
        port: 4466
        databases:
          default:
            connector: postgres
            host: localhost
            user: prisma
            password: prisma
            port: '5432'

Once you have specified the prismagraphql/prisma:1.31 image, you can redeploy your Prisma server:

docker-compose up -d

Now you can install the latest CLI version, e.g. with npm:

npm install -g prisma

At this point, you can already try to redeploy your Prisma datamodel. It won't work and the CLI will show some errors:

$ prisma deploy
Deploying service `default` to stage `default` to server `local` 585ms

Errors:

  User
    ✖ One field of the type `User` must be marked as the id field with the `@id` directive.
    ✖ The value "USER" is not a valid default for fields of type Enum.

  Profile
    ✖ One field of the type `Profile` must be marked as the id field with the `@id` directive.

  Post
    ✖ One field of the type `Post` must be marked as the id field with the `@id` directive.
    ✖ The value "false" is not a valid default for fields of type Boolean.

  Category
    ✖ One field of the type `Category` must be marked as the id field with the `@id` directive.

Deployment canceled. Please fix the above errors to continue deploying.
Read more about deployment errors here: https://bit.ly/prisma-force-flag

3. Migrating to datamodel v1.1 syntax

To update your datamodel and get rid of the previous errors, you can use the prisma introspect command. Inside your project directory, run:

prisma introspect

This introspects your database schema and generates a new datamodel file that uses v1.1 syntax. The new file is called datamodel-TIMESTAMP.prisma, e.g. datamodel-1554394432089.prisma. For the example from above, the following datamodel is generated:

type User {
  id: ID! @id
  createdAt: DateTime! @createdAt
  updatedAt: DateTime! @updatedAt
  name: String
  email: String! @unique
  role: Role @default(value: USER)
  posts: [Post]
  profile: Profile @relation(link: TABLE)
}

type Profile {
  id: ID! @id
  createdAt: DateTime! @createdAt
  updatedAt: DateTime! @updatedAt
  user: User!
  bio: String!
}

type Post {
  id: ID! @id
  createdAt: DateTime! @createdAt
  updatedAt: DateTime! @updatedAt
  title: String!
  published: Boolean! @default(value: false)
  author: User! @relation(link: TABLE)
  categories: [Category]
}

type Category {
  id: ID! @id
  createdAt: DateTime! @createdAt
  updatedAt: DateTime! @updatedAt
  name: String!
  posts: [Post]
}

enum Role {
  USER
  ADMIN
}

You can now upgrade the datamodel of your running Prisma service:

prisma deploy

Alternative: Fix errors manually instead of using introspection

To fix the errors that Prisma threw after prisma deploy, you need to:

Use the @id directive instead of @unique on the id fields of your models
Remove the quotes around the arguments of the @default directives
Specify a relation type (inline or relation table) on the 1:1 relation between User and Profile (i.e. add the @relation directive with the link argument to one end of the relation)

Here is the datamodel updated to the new syntax:

type User {
  id: ID! @id
  createdAt: DateTime! @createdAt
  email: String! @unique
  name: String
  role: Role @default(value: USER)
  posts: [Post!]!
  profile: Profile @relation(link: TABLE)
}

type Profile {
  id: ID! @id
  user: User!
  bio: String!
}

type Post {
  id: ID! @id
  createdAt: DateTime! @createdAt
  updatedAt: DateTime! @updatedAt
  title: String!
  published: Boolean! @default(value: false)
  author: User!
  categories: [Category!]!
}

type Category {
  id: ID! @id
  name: String!
  posts: [Post!]!
}

enum Role {
  USER
  ADMIN
}

Now you can deploy the datamodel with the new syntax:

prisma deploy

4. Optimizing the database schema

When updating the datamodel syntax to v1.1, the existing database schema will remain the same, therefore keeping the opinionations of the datamodel v1. For example, you can't turn a relation that was represented via a relation table in v1 into an inline relation using the Prisma migration system.

If you want to optimize your database schema and take advantage of the new features, there are two options:

Option 1: Export the data from the old project and import it into a new Prisma project where the optimisations are applied
Option 2: Manually migrate the database schema and subsequently adjust the datamodel to match it

4.1. Export data

To export the data, you can run the following command (inside the directory where your prisma.yml is located):

prisma export

This creates the following file: export-TIMESTAMP.zip, where TIMESTAMPT represents the time of the export, e.g. export-2019-03-21T09:08:48.816Z.zip.

4.2. Optimize the database schema

Next, you need to initialize a new Prisma project that has a similar configuration as your previous one, e.g. the following prisma.yml:

endpoint: http://localhost:4466
datamodel: datamodel.prisma

When creating the new datamodel file, you can copy over your current datamodel and apply the optimizations you want to introduce. In this case, we will:

Turn the 1:1 relation between User and Profile into an inline relation tracked via the User table
Turn the one-to-manby relation between User and Post into an inline relation tracked via the Post table

Here's the datamodel that incorporates these changes, put it into a file called datamodel.prisma:

type User {
  id: ID! @id
  createdAt: DateTime! @createdAt
  email: String! @unique
  name: String
  role: Role @default(value: USER)
  posts: [Post!]!
  profile: Profile @relation(link: INLINE)
}

type Profile {
  id: ID! @id
  user: User!
  bio: String!
}

type Post {
  id: ID! @id
  createdAt: DateTime! @createdAt
  updatedAt: DateTime! @updatedAt
  title: String!
  author: User!
  published: Boolean! @default(value: false)
  categories: [Category!]!
}

type Category {
  id: ID! @id
  name: String!
  posts: [Post!]!
}

enum Role {
  USER
  ADMIN
}

Note that @relation(link: INLINE) on author could also be omitted since INLINE is the default relation type for a 1:n relation.

With these changes in place, you can deploy the datamodel:

prisma deploy

The new database schema now has the following (empty) tables:

User
Profile
Post
Category
_CategoryToPost

4.3. Import the data

To import the data, you need to use the prisma import command and point it to the exported file from 4.1., e.g.:

prisma import -d ./export-2019-03-21T09:08:48.816Z.zip

Note that there currently still is a bug that prevents this from working smoothly. One workaround is to just add the createdAt: DateTime! @createdAt and updatedAt: DateTime! @updatedAt fields to thyour new datamodel.

Multi column indexes are not part of this release yet. We are currently working on implementing them.
Polymorphic relations are not part of this release yet. We will make a separate effort to implement them.

How can I migrate my Prisma project with existing data to a new optimised database schema (e.g. JOIN tables have been removed)?

We recommend the following:

Use prisma export to export the data from your existing Prisma project.
Copy your existing datamodel to a new Prisma project and apply your desired optimisations to your datamodel.
Use prisma import to import the data into your new Prisma project.

Edit this page on GithubLast updated 4 days ago

Datamodel v1.1

Usage

Examples

Usage

Examples

Usage

Usage

Examples

Inline

Relation table

Generatic relation table

Relation table with a custom name (prepended with an underscore)

Customized relation table with @relationTable

Inline

Relation table

Generatic relation table

Relation table with a custom name (prepended with an underscore)

Customized relation table with @relationTable

Inline relation

Relation table

Generatic relation table

Relation table with a custom name (prepended with an underscore)

Customized relation table with @relationTable

catgegory

post

post_to_category

profile

user

4.1. Export data

4.2. Optimize the database schema

4.3. Import the data

Customized relation table with `@relationTable`

Customized relation table with `@relationTable`

Customized relation table with `@relationTable`

`catgegory`

`post`

`post_to_category`

`profile`

`user`