Skip to content

Latest commit

 

History

History
250 lines (184 loc) · 10.1 KB

getting-started.md

File metadata and controls

250 lines (184 loc) · 10.1 KB
Raw

Getting started

This page explains how to get started with Prisma in an existing project.

If you don't have a database to try Prisma or just want to explore everything from scratch, check out the ready-to-run example projects with use cases for REST, GraphQL, gRPC and more.

TLDR

Follow these steps to use Prisma with your existing database. Note that these steps assume that you have an existing Node.js or TypeScript project (in case you don't, follow the extended guide below):

  1. Install prisma2 as a development dependency: npm install prisma2 --save-dev
  2. Run npx prisma2 init to create an empty Prisma schema
  3. Set the url of the datasource block in the Prisma schema to your database connection URL
  4. Run prisma2 introspect to obtain your data model from the database schema
  5. Run npm install @prisma/client to add the Prisma Client npm package to your project
  6. Run prisma2 generate to generate Prisma Client
  7. Import Prisma Client into your code: import { PrismaClient } from '@prisma/client
  8. Instantiate Prisma Client: const prisma = new PrismaClient()
  9. Use Prisma Client in code (use your editor's auto-completion to explore its API)

Note: If Prisma's introspection failed for your database schema, please open an issue and tell us what went wrong. If you want to help us make Prisma more resilient, please share your database SQL schema with us so we can add it to our introspection testing suite.

Extended guide

In this guide, we'll walk you through the steps from above in more detail.

Prerequisites

This guide is based on Prisma's introspection feature which is constantly being improved. Right now, it still has the following limitations:

  • Every column needs to have a primary key constraint on a single column (multi-column primary keys are not yet supported). Introspection will fail if this is not the case. Note that this often makes it impossible to introspect a schema that uses relation tables (also sometimes called "join tables") as these typically don't have a single-column primary key.
  • ENUM types are not yet supported. Introspection will succeed and ignore the ENUM types in your database schema.
  • TIMESTAMP WITH TIMEZONE types are already supported via introspection (and mapped to Prisma's DateTime type) but currently can't be queried with Prisma Client.

1. Set up Prisma for your database

First, run the following command to create an empty Prisma schema file:

npx prisma2 init

This creates an empty Prisma schema looking similar to this:

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

// The `datasource` block is used to specify the connection to your DB.
// Set the `provider` field to match your DB type: "postgresql", "mysql" or "sqlite".
// The `url` field must contain the connection string to your DB.
// Learn more about connection strings for your DB: https://pris.ly/connection-strings
datasource db {
  provider = "postgresql" // other options are: "mysql" and "sqlite"
  url      = "postgresql://johndoe:johndoe@localhost:5432/mydb?schema=public"
}
// Other examples for connection strings are:
// SQLite: url = "sqlite:./dev.db"
// MySQL:  url = "mysql://johndoe:johndoe@localhost:3306/mydb"
// You can also use environment variables to specify the connection string: https://pris.ly/prisma-schema#using-environment-variables

// By adding the `generator` block, you specify that you want to generate Prisma's DB client.
// The client is generated by runnning the `prisma generate` command and will be located in `node_modules/@prisma` and can be imported in your code as:
// import { Prisma Client } from '@prisma/client'
generator client {
  provider = "prisma-client-js"
}

// Next steps:
// 1. Add your DB connection string as the `url` of the `datasource` block
// 2. Run `prisma2 introspect` to get your data model into the schema
// 3. Run `prisma2 generate` to generate Prisma Client JS
// 4. Start using Prisma Client JS in your application

This file contains a number of comments that tell you how to proceed.

First you need to provide your the connection URL of your database as the url of the datasource block. This is needed so that Prisma can introspect your database schema and generate Prisma Client.

The format of the connection URL for your database typically depends on the database you use (the parts spelled all-uppercased are placeholders for your specific connection details):

  • MySQL: mysql://USER:PASSWORD@HOST:PORT/DATABASE
  • PostgreSQL: postgresql://USER:PASSWORD@HOST:PORT/DATABASE?schema=SCHEMA
  • SQLite: sqlite:./FILE.db

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

datasource db {
  provider = "postgresql"
  url      = "postgresql://opnmyfngbknppm:[email protected]:5432/d50rgmkqi2ipus?schema=hello-prisma2"
}

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

datasource db {
  provider = "postgresql"
  url      = "postgresql://janedoe:janedoe@localhost:5432/janedoe?schema=hello-prisma2"
}

Note: 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.

Using the sample Heroku connection URL from above, the Prisma schema would now look as follows (comments removed for readability):

datasource db {
  provider = "postgresql"
  url      = "postgresql://spmhgyticvmvqh:[email protected]:5432/d37tkdumf8mre6?schema=public"
}

generator client {
  provider = "prisma-client-js"
}

2. Introspect your database to generate a data model

The next step is to run Prisma's introspection to obtain your data model:

npx prisma2 introspect

Note: If Prisma's introspection failed for your database schema, please open an issue and tell us what went wrong. If you want to help us make Prisma more resilient, please share your database SQL schema with us so we can add it to our introspection testing suite.

This command connects to your database and introspects its schema. Based on that schema, Prisma then adds a number of models to your Prisma schema file which represent the data model of your application. This data model will be the foundation for the generated data access API of Prisma Client.

For the purpose of this guide, we're using the following SQL schema:

CREATE TABLE users (
	user_id SERIAL PRIMARY KEY NOT NULL,
	name VARCHAR(256),
	email VARCHAR(256) UNIQUE NOT NULL
);

CREATE TABLE posts (
	post_id SERIAL PRIMARY KEY NOT NULL,
	created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
	title VARCHAR(256) NOT NULL,
	content TEXT,
	author_id INTEGER REFERENCES users(user_id) 
);

CREATE TABLE profiles (
	profile_id SERIAL PRIMARY KEY NOT NULL,
	bio TEXT,
	user_id INTEGER NOT NULL REFERENCES users(user_id) 
);

Prisma's introspection generates the following data model for the SQL schema above:

model posts {
  author_id  users?
  content    String?
  created_at DateTime?
  post_id    Int       @id
  title      String
}

model profiles {
  bio        String?
  profile_id Int     @id
  user_id    users
}

model users {
  email      String     @unique
  name       String?
  user_id    Int        @id
  postses    posts[]
  profileses profiles[]
}

Note: The wrong pluralization of posts and profiles to postses and profileses will be fixed soon.

3. Generate Prisma Client

Prisma Client is an auto-generated and type-safe database client that's tailored to your database schema. Note that you'll need a Node.js/TypeScript project in order to generate Prisma Client since it relies the @prisma/client dependency. You'll use TypeScript for the purpose of this guide.

If you don't have one already, run the following commands to create a simple TypeScript setup:

npm init -y
npm install typescript ts-node --save-dev
npm install @prisma/client
touch script.ts
touch tsconfig.json

Next, add the following contents to your tsconfig.json:

{
  "compilerOptions": {
    "outDir": "dist",
    "rootDir": "src",
    "lib": ["esnext"],
    "strict": true
  },
  "include": ["src/**/*"]
}

Now you can generate Prisma Client:

npx prisma2 generate

Your Prisma Client API is now ready to be used in node_modules/@prisma/client.

4. Use Prisma Client to read and write data in your database

With your TypeScript setup in place, add the following code to script.ts:

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

async function main() {

  const users = await prisma.users.findMany({
    include: {
      postses: true,
    }
  })
  console.log(JSON.stringify(users))
}

main()

This is a simple API call that fetches all the records from the users table. you can run the script with this command:

npx ts-node script.ts

If you've used your own database in this guide and are unsure what to query for, you can use your editor's auto-complection feature to help create a query by typing prisma. and then hit CTRL+SPACE to suggest any of your models as a starting point for the query. Once you selected a model and added another dot afterwards, you can again use the CTRL+SPACE to decide for an operation on the model (e.g. findMany, create, update, ...). After having selected the operation, you can once more invoke the auto-completion to explore the arguments to provide for the operation.