# New Datamodel Syntax: More Schema Control & Simpler Migration

URL: https://www.prisma.io/blog/datamodel-v11-lrzqy1f56c90
Published: 2019-04-17
Authors: Nikolas Burk
Description: Prisma's new datamodel syntax gave developers more control over database schemas while simplifying migrations and reducing opinionated defaults.

Prisma's latest release features an improved datamodel syntax. It removes many of the opinionated decisions Prisma used to make about the database layout and enables more control for developers.

> ⚠️ **This article is outdated** as it relates to [Prisma 1](https://github.com/prisma/prisma1) which is now [deprecated](https://github.com/prisma/prisma1/issues/5208). To learn more about the most recent version of Prisma, read the [documentation](https://www.prisma.io/docs). ⚠️

Over the last months, we have worked with the community to define an improved datamodel [specification](https://github.com/prisma/orm/issues/3408) for Prisma. This new version is called datamodel v1.1 and is available in today's stable release. Check out the docs [here](https://v1.prisma.io/docs/1.31/releases-and-maintenance/features-in-preview/datamodel-v11-b6a7/).

> As of today, Prisma's public [Demo servers](https://v1.prisma.io/docs/1.31/prisma-server/demo-servers-prisma-cloud-jfr3) will be using the new datamodel syntax. Check out the [**docs**](https://v1.prisma.io/docs/1.31/releases-and-maintenance/features-in-preview/datamodel-v11-b6a7/#upgrading-to-datamodel-v11) or this [**tutorial video**](https://www.youtube.com/watch?v=48m1Gnmu19Q) to learn how to upgrade your existing projects.

***

## A more flexible approach to data modelling [#a-more-flexible-approach-to-data-modelling]

A [datamodel](https://v1.prisma.io/docs/1.31/datamodel-and-migrations/datamodel-MYSQL-knul) is the foundation for every Prisma project. It serves as the foundation for the schema of the underlying database.

The current datamodel is opinionated about the database layout, e.g. for relations, naming of tables/columns or system fields. The new datamodel syntax lifts many limitations so that developers have more control over their schema.

### More control over your database layout [#more-control-over-your-database-layout]

Here are a few things enabled by the new datamodel syntax:

* Specify whether a relation should use a relation table or foreign keys
* Model/field names can differ from the names of the underlying tables/columns
* Use any field as `id` field and "bring your own ID"
* Use any field as `createdAt` or `updatedAt` fields

### Simpler migrations & improved introspection [#simpler-migrations--improved-introspection]

In previous Prisma versions, developers had to decide whether Prisma should perform database migrations for them, by setting the `migrations` flag in [`PRISMA_CONFIG`](https://v1.prisma.io/docs/1.31/prisma-server/deployment-environments/docker-rty1#prisma_config-reference).

The `migrations` flag has been removed in the latest Prisma version, meaning developers can now at all times *either* migrate the database manually *or* use Prisma for the migration.

We have also invested a lot into the introspection of existing databases, enabling smooth workflows for developers that are using Prisma with a legacy database or need to perform manual migrations at some point.

***

## What's new in the improved datamodel syntax? [#whats-new-in-the-improved-datamodel-syntax]

### Map model and field names to the underlying tables and columns [#map-model-and-field-names-to-the-underlying-tables-and-columns]

With the old datamodel syntax, tables and columns are always named *exactly* after the models and fields in your datamodel. Using the new `@db` directive, you can control what tables and columns should be called in the underlying database:

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

```ts
CREATE TABLE "default$default"."user" (
    "id" varchar(25) NOT NULL,
    "full_name" text NOT NULL,
    PRIMARY KEY ("id")
);
```

In this case, the underlying table will be called `user` and the column `full_name`.

### Decide how a relation is represented in the database schema [#decide-how-a-relation-is-represented-in-the-database-schema]

The old datamodel is opinionated about relations in the database schema: they're *always* represented as relation tables.

On the one hand, this makes it possible to easily migrate any existing relation to a *many-to-many*-relation without extra work. However, there might be a performance penalty to pay for this flexibility because relation tables are often more expensive to query.

> While 1:1 and 1:n relations can now be represented via foreign keys, m:n relations will keep being represented as relation tables.

With the new datamodel, developers can take full control over expressing a relation in the underlying database. There are two options:

* Represent a relation via inline references (i.e. *foreign keys*)
* Represent a relation via a relation table

Here is an example with two relations (one is *inline*, the other uses a *relation table*):

```graphql
type User {
  id: ID! @id
  profile: Profile! @relation(link: INLINE)
  posts: [Post!]! @relation(link: TABLE)
}

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

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

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

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

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

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

In the case of the inline relation, the placement of the `@relation(link: INLINE)` directive determines on which end of the relation the foreign key is being stored, in this example it's stored in the `User` table.

### Use any field as `id`, `createdAt` or `updatedAt` [#use-any-field-as-id-createdat-or-updatedat]

With the old datamodel, developers were required to use reserved fields if they wanted to automatically generate unique IDs or track when a record was created/last updated.

With the new `@id`, `@createdAt` and `@updatedAt` directives, it is now possible to add this functionality to any field of a model:

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

```ts
CREATE TABLE "test$devasdas"."User" (
    "myID" varchar(25) NOT NULL,
    "myCreatedAt" timestamp(3) NOT NULL,
    "myUpdatedAt" timestamp(3) NOT NULL,
    PRIMARY KEY ("myID")
);
```

### More flexible IDs [#more-flexible-ids]

The current datamodel *always* uses [CUIDs](https://github.com/ericelliott/cuid) to generate and store globally unique IDs for database records. The datamodel v1.1 now makes it possible to maintain custom IDs as well as to use other ID types (e.g. integers, sequences, or UUIDs).

***

## Getting started with the new datamodel syntax [#getting-started-with-the-new-datamodel-syntax]

We prepared two short tutorials for you to explore the new datamodel:

* Option A: [**Upgrade an old Prisma project**](#option-a-upgrade-from-an-older-prisma-version) to the new datamodel syntax
* Option B: [**Get started from scratch**](#option-b-starting-from-scratch) with the new datamodel syntax

For more extensive tutorials and instructions for getting started with an existing database, visit the [docs](https://v1.prisma.io/docs/1.31/releases-and-maintenance/features-in-preview/datamodel-v11-b6a7/).

### Prerequisite: Install the latest Prisma CLI [#prerequisite-install-the-latest-prisma-cli]

To install the latest version of the Prisma CLI, run:

```shell
npm install -g prisma
```

> When running Prisma with Docker, you need to upgrade its Docker image to `1.31`.

### Option A: Upgrade from an older Prisma version [#option-a-upgrade-from-an-older-prisma-version]

When upgrading your existing Prisma projects, you can use simply run `prisma introspect` to generate the datamodel with the new syntax. The exact process is described with an example in the following sections and this video:

[Watch the video on YouTube](https://www.youtube.com/watch?v=48m1Gnmu19Q)

#### 1. Old datamodel setup [#1-old-datamodel-setup]

Assume you already have a running Prisma project that uses an (old) datamodel.

### See sample datamodel

```graphql
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 old datamodel, 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. The `_RelayId` table is used to identify any record by its ID. With the old datamodel syntax, these are decisions made by Prisma that can not be worked around.

#### 2. Upgrade your Prisma server [#2-upgrade-your-prisma-server]

In the Docker Compose file used to deploy your Prisma server, make sure to use the latest `1.31` Prisma version for the `prismagraphql/prisma` image. For example:

```yml
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'
```

Now upgrade the running Prisma server:

```shell
docker-compose up -d
```

#### 3. Generate new datamodel via introspection [#3-generate-new-datamodel-via-introspection]

If you're now running `prisma deploy`, your Prisma CLI will throw an error because you're trying to deploy a datamodel in the old syntax to an updated Prisma server.

The easiest way to fix these errors is by generating a datamodel written in the new syntax via introspection. Run the following command inside the directory where your `prisma.yml` is located:

```shell
prisma introspect
```

This introspects your database and generates another datamodel with the new syntax, called `datamodel-TIMESTAMP.prisma` (e.g. `datamodel-1554394432089.prisma`). For the example from above, the following datamodel is generated:

```graphql
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
}
```

#### 4. Deploy new datamodel [#4-deploy-new-datamodel]

The final step is to delete the old `datamodel.prisma` file and rename your generated datamodel to `datamodel.prisma` (so that the `datamodel` property in your `prisma.yml` points to the generated file that's using the new syntax).

Once that's done, you can run:

```shell
prisma deploy
```

#### 5. Optimize your database schema [#5-optimize-your-database-schema]

Because the introspection didn't change anything about your database layout, all relations are still represented as relation tables. If you want to learn how you can migrate the old 1:1 and 1:n relations to use *foreign keys*, check out the docs [here](https://v1.prisma.io/docs/1.31/releases-and-maintenance/features-in-preview/datamodel-v11-b6a7/#4.-optimizing-the-database-schema).

### Option B: Starting from scratch [#option-b-starting-from-scratch]

After having learned how to upgrade existing Prisma projects, we'll now walk you through a simple setup where we're starting out from scratch.

#### 1. Create a new Prisma project [#1-create-a-new-prisma-project]

Let's start by setting up a new Prisma project:

```shell
prisma init hello-datamodel
```

In the interactive wizard, select the following:

1. Select **Create new database**
2. Select **PostgreSQL** (or MySQL, if you prefer)
3. Select a client in your preferred language (*optional as we won't use the client*)

Before launching the Prisma server and the database via Docker, enable port mapping for your database. This will later allow you to connect to the database using a local DB client (such as [Postico](https://eggerapps.at/postico/) or [TablePlus](https://tableplus.io/)).

In the generated `docker-compose.yml`, uncomment the following lines in the Docker image configuration of the database:

```yml
ports:
  - '5432:5432'
```

```yml
ports:
  - '3306:3306'
```

#### 2. Define datamodel [#2-define-datamodel]

Let's define a datamodel that takes advantage of the new Prisma features. Open `datamodel.prisma` and replace the contents with the following:

```graphql
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
}
```

Here are some important bits about this datamodel definition:

* 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.

#### 3. Deploy the datamodel [#3-deploy-the-datamodel]

In the next step, Prisma will map this datamodel to the underlying database:

```shell
prisma deploy
```

### See Prisma's generated SQL schema

##### `Category` [#category]

Table:

```ts
CREATE TABLE "hello-datamodel$dev"."category" (
    "id" varchar(25) NOT NULL,
    "name" text NOT NULL,
    PRIMARY KEY ("id")
);
```

Index:

| index\_name     | index\_algorithm | is\_unique | column\_name |
| --------------- | ---------------- | ---------- | ------------ |
| `category_pkey` | `BTREE`          | `TRUE`     | `id`         |

##### `Post` [#post]

Table:

```ts
CREATE TABLE "hello-datamodel$dev"."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:

| index\_name | index\_algorithm | is\_unique | column\_name |
| ----------- | ---------------- | ---------- | ------------ |
| `post_pkey` | `BTREE`          | `TRUE`     | `id`         |

##### `PostToCategory` [#posttocategory]

Table:

```ts
CREATE TABLE "hello-datamodel$dev"."post_to_category" (
    "category" varchar(25) NOT NULL,
    "post" varchar(25) NOT NULL
);
```

Index:

| 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` [#profile]

Table:

```ts
CREATE TABLE "hello-datamodel$dev"."profile" (
    "id" varchar(25) NOT NULL,
    "bio" text NOT NULL,
    PRIMARY KEY ("id")
);
```

Index:

| index\_name    | index\_algorithm | is\_unique | column\_name |
| -------------- | ---------------- | ---------- | ------------ |
| `profile_pkey` | `BTREE`          | `TRUE`     | `id`         |

##### `User` [#user]

Table:

```ts
CREATE TABLE "hello-datamodel$dev"."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:

| index\_name                              | index\_algorithm | is\_unique | column\_name |
| ---------------------------------------- | ---------------- | ---------- | ------------ |
| `user_pkey`                              | `BTREE`          | `TRUE`     | `id`         |
| `hello-datamodel$dev.user.email._UNIQUE` | `BTREE`          | `TRUE`     | `email`      |

#### 4. View and edit the data in Prisma Admin [#4-view-and-edit-the-data-in-prisma-admin]

From here on, you can use the [Prisma client](https://v1.prisma.io/docs/1.31/prisma-client) if you want to access the data in your database programmatically. In the following, we'll highlight how to use Prisma Admin to interact with the data.

> Visit the [docs](https://v1.prisma.io/docs/1.31/releases-and-maintenance/features-in-preview/datamodel-v11-b6a7/#using-tableplus) to learn how you can connect to the database using [TablePlus](https://tableplus.io) and explore the underlying database schema.

To access your data in [Prisma Admin](https://v1.prisma.io/docs/1.31/prisma-admin), you need to navigate to the Admin endpoint of your Prisma project: `http://localhost:4466/_admin`

![Access your data in Prisma Admin](https://www.prisma.io/blog/datamodel-v11-lrzqy1f56c90/imgs/wJCyxVy.png)

***

## Share you feedback and ideas [#share-you-feedback-and-ideas]

While the new datamodel syntax already incorporates many features requested by our community, we still see opportunities to improve it even further. For example, the datamodel doesn't yet provide [multi-column indices](https://github.com/prisma/orm/issues/3405) and [polymorphic relations](https://github.com/prisma/orm/issues/3407).

We are currently working on a new [data modeling language](https://github.com/prisma/specs/tree/master/schema) that will be a variation of the currently used [SDL](https://www.prisma.io/blog/graphql-sdl-schema-definition-language-6755bcb9ce51).

We'd love hear what you think of the new datamodel. Please share your feedback by [opening an issue in the feedback repo](https://github.com/prisma/datamodel-v1.1-feedback/issues/new) or join the conversation on [Spectrum](https://spectrum.chat/prisma/general/releasing-prisma-v1-31~0d4dcb59-a58f-4ecf-84e6-b8509bad4abf).