Connect your database

Connect your database

To connect your database, you need to set the url field of the datasource block in your Prisma schema to your database connection URL:

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

In this case, the url is set via an environment variable which is defined in .env:

DATABASE_URL="postgresql://johndoe:randompassword@localhost:5432/mydb?schema=public"

info

We recommend adding .env to your .gitignore file to prevent committing your environment variables.

You now need to adjust the connection URL to point to your own database.

The format of the connection URL for your database depends on the database you use. For PostgreSQL, it looks as follows (the parts spelled all-uppercased are placeholders for your specific connection details):

postgresql://USER:PASSWORD@HOST:PORT/DATABASE?schema=SCHEMA

Here's a short explanation of each component:

• USER: The name of your database user
• PASSWORD: The password for your database user
• HOST: The name of your host name (for the local environment, it is localhost)
• PORT: The port where your database server is running (typically 5432 for PostgreSQL)
• DATABASE: The name of the database
• SCHEMA: The name of the schema inside the database

If you're unsure what to provide for the schema parameter for a PostgreSQL connection URL, you can probably omit it. In that case, the default schema name public will be used.

As an example, for a PostgreSQL database hosted on Heroku, the connection URL might look similar to this:

DATABASE_URL="postgresql://opnmyfngbknppm:XXX@ec2-46-137-91-216.eu-west-1.compute.amazonaws.com:5432/d50rgmkqi2ipus?schema=hello-prisma"

When running PostgreSQL locally on macOS, your user and password as well as the database name typically correspond to the current user of your OS, e.g. assuming the user is called janedoe:

DATABASE_URL="postgresql://janedoe:janedoe@localhost:5432/janedoe?schema=hello-prisma"

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

Note that the default schema created by prisma init uses PostgreSQL, so you first need to switch the provider to mysql:

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

In this case, the url is set via an environment variable which is defined in .env:

DATABASE_URL="mysql://johndoe:randompassword@localhost:3306/mydb"

info

We recommend adding .env to your .gitignore file to prevent committing your environment variables.

You now need to adjust the connection URL to point to your own database.

The format of the connection URL for your database typically depends on the database you use. For MySQL, it looks as follows (the parts spelled all-uppercased are placeholders for your specific connection details):

mysql://USER:PASSWORD@HOST:PORT/DATABASE

Here's a short explanation of each component:

• USER: The name of your database user
• PASSWORD: The password for your database user
• PORT: The port where your database server is running (typically 3306 for MySQL)
• DATABASE: The name of the database

As an example, for a MySQL database hosted on AWS RDS, the connection URL might look similar to this:

DATABASE_URL="mysql://johndoe:XXX@mysql–instance1.123456789012.us-east-1.rds.amazonaws.com:3306/mydb"

When running MySQL locally, your connection URL typically looks similar to this:

DATABASE_URL="mysql://root:randompassword@localhost:3306/mydb"

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

Note that the default schema created by prisma init uses PostgreSQL as the provider. For PlanetScale, you need to edit the datasource block to use the mysql provider instead:

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

You will also need to set the relation mode type to prisma in the datasource block:

datasource db {
  provider     = "mysql"
  url          = env("DATABASE_URL")
  relationMode = "prisma"
}

The url is set via an environment variable which is defined in .env:

DATABASE_URL="mysql://janedoe:mypassword@server.us-east-2.psdb.cloud/mydb?sslaccept=strict"

You now need to adjust the connection URL to point to your own database.

The format of the connection URL for your database typically depends on the database you use. PlanetScale uses the MySQL connection URL format, which has the following structure (the parts spelled all-uppercased are placeholders for your specific connection details):

mysql://USER:PASSWORD@HOST:PORT/DATABASE

Here's a short explanation of each component:

• USER: The name of your database user
• PASSWORD: The password for your database user
• PORT: The port where your database server is running (typically 3306 for MySQL)
• DATABASE: The name of the database

For a database hosted with PlanetScale, the connection URL looks similar to this:

DATABASE_URL="mysql://myusername:mypassword@server.us-east-2.psdb.cloud/mydb?sslaccept=strict"

The connection URL for a given database branch can be found from your PlanetScale account by going to the overview page for the branch and selecting the 'Connect' dropdown. In the 'Passwords' section, generate a new password and select 'Prisma' to get the Prisma format for the connection URL.

Alternative method: connecting using the PlanetScale CLI

Alternatively, you can connect to your PlanetScale database server using the PlanetScale CLI, and use a local connection URL. In this case the connection URL will look like this:

DATABASE_URL="mysql://root@localhost:PORT/mydb"

info

We recommend adding .env to your .gitignore file to prevent committing your environment variables.

To connect to your branch, use the following command:

pscale connect prisma-test branchname --port PORT

The --port flag can be omitted if you are using the default port 3306.

datasource db {
  provider = "sqlserver"
  url      = env("DATABASE_URL")
}

In this case, the url is set via an environment variable which is defined in .env:

The following example connection URL uses SQL authentication, but there are other ways to format your connection URL

  DATABASE_URL="sqlserver://localhost:1433;database=mydb;user=sa;password=r@ndomP@$$w0rd;trustServerCertificate=true"

info

We recommend adding .env to your .gitignore file to prevent committing your environment variables.

Adjust the connection URL to match your setup - see Microsoft SQL Server connection URL for more information.

Make sure TCP/IP connections are enabled via SQL Server Configuration Manager to avoid No connection could be made because the target machine actively refused it. (os error 10061)

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

Note that the default schema created by prisma init uses PostgreSQL as the provider. For CockroachDB, you need to edit the datasource block to use the cockroachdb provider instead:

datasource db {
  provider = "cockroachdb"
  url      = env("DATABASE_URL")
}

The url is set via an environment variable which is defined in .env. You now need to adjust the connection URL to point to your own database.

The format of the connection URL for your database depends on the database you use. CockroachDB uses the PostgreSQL connection URL format, which has the following structure (the parts spelled all-uppercased are placeholders for your specific connection details):

postgresql://USER:PASSWORD@HOST:PORT/DATABASE?PARAMETERS

Here's a short explanation of each component:

• USER: The name of your database user
• PASSWORD: The password for your database user
• PORT: The port where your database server is running. The default for CockroachDB is 26257.
• DATABASE: The name of the database
• PARAMETERS: Any additional connection parameters. See the CockroachDB documentation here.

For a CockroachDB Serverless or Cockroach Dedicated database hosted on CockroachDB Cloud, the connection URL looks similar to this:

DATABASE_URL="postgresql://<myusername>:<mypassword>@<short-id>.<region>.cockroachlabs.cloud:26257/defaultdb?sslmode=verify-full&sslrootcert=$HOME/.postgresql/root.crt&options=--<mycluster>"

To find your connection string on CockroachDB Cloud, click the 'Connect' button on the overview page for your database cluster, and select the 'Connection string' tab.

For a CockroachDB database hosted locally, the connection URL looks similar to this:

DATABASE_URL="postgresql://root@localhost:26257?sslmode=disable"

Your connection string is displayed as part of the welcome text when starting CockroachDB from the command line.

Installation

Using Prisma Migrate

Installation

Using Prisma Migrate

Installation

Creating the database schema

Installation

Using Prisma Migrate

Installation

Using Prisma Migrate

Installation

Using Prisma Migrate

Installation

Using Prisma Migrate

Installation

Creating the database schema

Installation

Using Prisma Migrate

Installation

Using Prisma Migrate