docs/1.30/releases-and-maintenance/features-in-preview/datamodel-v11-b6a7.mdx
import Collapse from 'components/Markdown/Collapse'
export const meta = { title: 'Datamodel v1.1', position: 160 }
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:
The main motivations for the new datamodel are:
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:
id fieldcreatedAt or updatedAt fieldsWith 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.
@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:
The @db directive can be applied to:
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.
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!
}
@idThe @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.
The @id directive can be applied to scalar fields of types:
IDUUIDIntIt 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).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!
}
@sequenceThe @sequence directive is used to specify how a primary identifier for a model is generated.
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 & @updatedAtThe @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.
The @createdAt and @updatedAt directives can be applied to any fields of type DateTime!.
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
}
@relationSee the the Relations section for more info.
@relationTableSee the the Relations section for more info.
In Prisma, a relation connects two models. A direction can be uni- or bidirectional:
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:
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.
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")
);
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
);
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
);
@relationTabletype 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:
A and BSo 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.
When defining a 1:n relation between two models, the @relation directive is optional. Prisma defaults to an inline relation.
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.
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
);
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
);
@relationTabletype 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
);
Not applicable
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
);
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
);
@relationTabletype 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
);
prisma deployThe 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 introspectThe command prisma introspect has been adapted to output the new datamodel format when using it with the --prototype flag:
prisma introspect --prototype
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.
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:
prisma introspect --prototype to update your datamodel with those changes from the databaseprisma deploy --no-migrate to let the Prisma server know about thatThe datamodel v1.1 is available in the latest beta version of Prisma.
npm install -g prisma@beta
In the Docker Compose file for your Prisma server, make sure to use the latest beta Docker image:
prismagraphql/prisma:1.31-beta
prototype flag to true in PRISMA_CONFIGEnable the datamodel v1.1 by adding the prototype flag in your PRISMA_CONFIG:
port: 4466
prototype: true
databases:
default:
connector: postgres # or mysql
host: 127.0.0.1
port: 5432
user: prisma
password: prisma
Note that you might need to adjust the database connection details to match your setup. Check out the
examplefolder for a ready-to-use setup.
Create a new file callled docker-compose.yml and add the following code to it:
version: '3'
services:
prisma:
image: prismagraphql/prisma:1.31-beta
restart: always
ports:
- "4466:4466"
environment:
PRISMA_CONFIG: |
port: 4466
prototype: true
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.
Run the following command to deploy your server:
docker-compose up -d
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:
@db directive.User and ProfileUser and PostPost and CategoryUser 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.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.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.id field annotated with the @id directive.User model, the database automatically tracks when a record is created via the field annotated with the @createdAt directive.Post model, the database automatically tracks when a record is created and updated via the fields annotated with the @createdAt and @updatedAt directives.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:
catgegoryCREATE 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 |
postCREATE 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_categoryCREATE 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 |
profileCREATE 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 |
userCREATE 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 |
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.
To access your data in Prisma Admin, you need to navigate to the Admin endpoint of your Prisma project: http://localhost:4466/_admin
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:5432line to it.
When opening TablePlus, you need to:
Local PostgreSQLlocalhost5432prismaprismaprismaAfter you connected to the database, you can explore the data and table structure in the TablePlus GUI:
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 --prototype
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 --prototype
? 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.IMPORTANT: You need to manually add the prototype: true flag to docker-compose.yml:
port: 4466
prototype: true
databases:
default:
connector: postgres # or mysql
host: 127.0.0.1
port: 5432
user: prisma
password: prisma
Now you can deploy your Prisma setup:
cd myapp
docker-compose up -d
prisma deploy
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
Assume you have a deployed Prisma project with the following datamodel:
type User {
id: ID! @unique
createdAt: DateTime!
email: String! @unique
name: String
role: Role @default(value: "USER")
posts: [Post!]!
profile: Profile
}
type Profile {
id: ID! @unique
user: User!
bio: String!
}
type Post {
id: ID! @unique
createdAt: DateTime!
updatedAt: DateTime!
title: String!
published: Boolean! @default(value: "false")
author: User!
categories: [Category!]!
}
type Category {
id: ID! @unique
name: String!
posts: [Post!]!
}
enum Role {
USER
ADMIN
}
When using the datamodel v1, the following tables are created by Prisma in the underlying database:
UserProfilePostCategory_CategoryToPost_PostToUser_ProfileToUser_RelayIdEach 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.
To enable the datamodel v1.1, you need to:
prototype: true flag to your Docker Compose fileFor 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-beta
restart: always
prototype: true
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-beta image and introduced the prototype: true flag, you can redeploy your Prisma server:
docker-compose up -d
Now you can install the latest CLI beta, e.g. with npm:
npm install -g prisma@beta
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
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 --prototype
This introspects your database schema and generates a new datamodel file that uses v1.1 syntax. The new file is called datamodel-prototype-TIMESTAMP.prisma, e.g. datamodel-prototype-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
To fix the errors that Prisma threw after prisma deploy, you need to:
@id directive instead of @unique on the id fields of your models@default directivesUser 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
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:
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.
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:
User and Profile into an inline relation tracked via the User tableUser and Post into an inline relation tracked via the Post tableHere'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)onauthorcould also be omitted sinceINLINEis 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:
UserProfilePostCategory_CategoryToPostTo 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.
Please report bugs directly here in this repo!
We recommend the following:
prisma export to export the data from your existing Prisma project.prisma import to import the data into your new Prisma project.