01 Setting Up Prisma Existing Database JAVASCRIPT A003 - Prisma1

import Warning from 'components/Markdown/Warning' import QueryChooser from 'components/Markdown/QueryChooser' import Code from 'components/Markdown/Code' import Collapse from 'components/Markdown/Collapse'

export const meta = { title: 'Set up Prisma', position: 1, gettingStartedOrder: 2, gettingStartedTitle: 'Existing database', nextText: 'Great work! 👏 Move on to learn how you can extend your datamodel and make changes to your Prisma API.', technology: 'node', technologyOrder: 1, articleGroup: 'Set up Prisma', }

Goals

On this page, you will learn how to:

Install the Prisma CLI
Set up Prisma using Docker
Introspect your existing database and derive a datamodel
Use the datamodel to configure your Prisma API
Generate a Prisma client
Read and write data using the Prisma client

Using your existing database with Prisma currently only works when using PostgreSQL or MongoDB databases. It is not yet supported for MySQL.

</Warning>

Prerequisites

Make sure to have connection details for your database at hand. This includes the following pieces of information:

Host: The host of your Postgres server, e.g. localhost.
Port: The port where your Postgres server listens, e.g. 5432.
User & Password: The credentials for your Postgres server.
Name of existing database: The name of the Postgres database.
Name of existing schema: The name of the Postgres schema, e.g. public.

You also need to know whether your database server uses SSL.

</Collapse> <Collapse title="Required connection details for MongoDB">

Your MongoDB connection string.

</Collapse>

Install the Prisma CLI

The Prisma CLI is used for various Prisma workflows. You can install it using Homebrew or NPM:

bash

brew tap prisma/prisma
brew install prisma

bash

npm install -g prisma

</Code> <Warning>

When using MongoDB, you need to install the beta version of the Prisma CLI:

console

npm install -g prisma@beta

</Warning>

Install Docker

To use Prisma locally, you need to have Docker installed on your machine. If you don't have Docker yet, you can download the Docker Community Edition for your operating system here.

Set up Prisma server

Create new directory

bash

mkdir hello-world
cd hello-world

Create Docker Compose file

To launch Prisma on your machine, you need a Docker Compose file that configures Prisma and specifies to which database it can connect:

bash

touch docker-compose.yml

Add Prisma Docker image

Paste the following contents into the Docker Compose file you just created:

yml

version: '3'
services:
  prisma:
    image: prismagraphql/prisma:1.22
    restart: always
    ports:
    - "4466:4466"
    environment:
      PRISMA_CONFIG: |
        port: 4466
        databases:
          default:
            connector: postgres
            host: __YOUR_POSTGRES_HOST__
            port: __YOUR_POSTGRES_PORT__
            database: __YOUR_POSTGRES_DB__
            schema: __YOUR_POSTGRES_SCHEMA__
            user: __YOUR_POSTGRES_USER__
            password: __YOUR_POSTGRES_PASSWORD__
            migrations: false
            ssl: __SSL_CONNECTION__

yml

# Coming soon. See https://github.com/prisma/prisma/issues/2506

yml

version: '3'
services:
  prisma:
    image: prismagraphql/prisma:1.22
    restart: always
    ports:
    - "4466:4466"
    environment:
      PRISMA_CONFIG: |
        port: 4466
        databases:
          default:
            connector: mongo
            uri: __YOUR_CONNECTION_STRING__

</Code>

Specify database connection

To specify the database to which Prisma should connect, replace the placeholders that are spelled all-uppercased in the Docker Compose file with the corresponding values of your database:

__YOUR_POSTGRES_HOST__: The host of your Postgres server, e.g. localhost. (When connecting to a local database, you might need to use host.docker.internal.)
__YOUR_POSTGRES_PORT__: The port where your Postgres server listens, e.g. 5432.
__YOUR_POSTGRES_DB__: The name of your Postgres database.
__YOUR_POSTGRES_SCHEMA__: The name of your Postgres schema, e.g. public.
__YOUR_POSTGRES_USER__: The database user.
__YOUR_POSTGRES_PASSWORD__: The password for the database user.
__SSL_CONNECTION__: Whether your database server uses SSL, possible values are true and false.

</Collapse> <Collapse title="Placeholder overview for MongoDB">

__YOUR_CONNECTION_STRING__: Your MongoDB connection string (learn more).

</Collapse>

Launch Prisma

To start Prisma and connect it to your database, run the following command:

bash

docker-compose up -d

Prisma is now connected to your database and runs on http://localhost:4466.

Derive Prisma datamodel from database schema

Create prisma.yml

Next, you need to create a prisma.yml:

bash

touch prisma.yml

Now add the following contents to it:

yml

endpoint: http://localhost:4466

The endpoint needs to match the URL of a running Prisma server.

Introspect database

You now need to introspect your database schema to generate the datamodel which is the foundation for the API of your Prisma client:

bash

prisma introspect

The CLI generates the datamodel-[TIMESTAMP].prisma (e.g. datamodel-1533886167692.prisma) file containing the SDL version of your database schema. On the first run, it also writes the datamodel property into the prisma.yml.

Finally, you need to rename the file to datamodel.prisma because that's the file name you specifed in prisma.yml.

Deploy the Prisma API

You now have the minimal setup ready to deploy your Prisma API. Run the following command (this does not change anything in your database):

bash

prisma deploy

Launching the Prisma server may take a few minutes. In case the prisma deploy command fails, wait a few minutes and try again. Also run docker ps to ensure the Docker container is actually running.

</Warning> ## Generate your Prisma client

The Prisma client is a custom, auto-generated library that connects to your Prisma API. Append the following lines to the end of your prisma.yml:

yml

generate:
  - generator: javascript-client
    output: ./generated/prisma-client/

Now generate the client with this command:

copy

prisma generate

The CLI now stored your Prisma client inside the ./generated/prisma-client/ directory as specified in prisma.yml.

Prepare Node application

Run the following command to create an empty Node script:

bash

touch index.js

Next, initialize an empty NPM project in the current directory and install the required dependencies:

bash

npm init -y
npm install --save prisma-client-lib

Read and write data using the Prisma client

The API operations of the Prisma client depend on the datamodel that was generated from the database introspection. The following sample queries assume there is a User type in the datamodel defined as follows:

graphql

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

If you don't have such a User type, you need to adjust the following code snippets with a type that matches your datamodel.

Add the following code in index.js:

const { prisma } = require('./generated/prisma-client')

// A `main` function so that we can use async/await
async function main() {

  // Create a new user called `Alice`
  const newUser = await prisma.createUser({ name: 'Alice' })
  console.log(`Created new user: ${newUser.name} (ID: ${newUser.id})`)

  // Read all users from the database and print them to the console
  const allUsers = await prisma.users()
  console.log(allUsers)
}

main().catch(e => console.error(e))

Execute the script with the following command:

bash

node index.js

Whenever you run this script with that command, a new user record is created in the database (because of the call to createUser).

Feel free to play around with the Prisma client API and try out some of the following operations by adding the following code snippets to the file (at the end of the main function) and re-executing the script:

const user = await prisma
  .user({ id: '__USER_ID__' })

const usersCalledAlice = await prisma
  .users({
    where: {
      name: 'Alice'
    }
  })

 const updatedUser = await prisma
  .updateUser({
    where: { id: '__USER_ID__' },
    data: { name: 'Bob' }
  })

 const deletedUser = await prisma
  .deleteUser({ id: '__USER_ID__' })

</QueryChooser>

In some snippets, you need to replace the __USER__ID__ placeholder with the ID of an actual user.