streets-prisma-postgresql-seed

🚀 Javascript full-stack 🚀

https://github.com/coding-to-music/streets-prisma-postgresql-seed

By Jordan McRae https://github.com/jordan-mcrae

https://stackfive.io/blog/seeding-relational-data-with-prisma

https://github.com/jordan-mcrae/prisma-relational-seeding-example

Data is used by https://github.com/coding-to-music/grafana-cloud-docker-postgres-integration

Environment Values

DATABASE_URL="postgres://username:password@localhost:5432/prisma-relational-example"

GitHub

git init
git add .
git remote remove origin
git commit -m "first commit"
git branch -M main
git remote add origin git@github.com:coding-to-music/streets-prisma-postgresql-seed.git
git push -u origin main

◭ Prisma is a modern DB toolkit to query, migrate and model your database (https://prisma.io)

npx prisma

Output

◭  Prisma is a modern DB toolkit to query, migrate and model your database (https://prisma.io)

Usage

  $ prisma [command]

Commands

            init   Set up Prisma for your app
        generate   Generate artifacts (e.g. Prisma Client)
              db   Manage your database schema and lifecycle
         migrate   Migrate your database
          studio   Browse your data with Prisma Studio
          format   Format your schema

Flags

     --preview-feature   Run Preview Prisma commands

Examples

  Set up a new Prisma project
  $ prisma init

  Generate artifacts (e.g. Prisma Client)
  $ prisma generate

  Browse your data
  $ prisma studio

  Create migrations from your Prisma schema, apply them to the database, generate artifacts (e.g. Prisma Client)
  $ prisma migrate dev

  Pull the schema from an existing database, updating the Prisma schema
  $ prisma db pull

  Push the Prisma schema state to the database
  $ prisma db push

Have prisma read the table in the database and generate it's own model

To have Prisma read an existing table in the database and generate its own model, you can use the Prisma CLI's introspect command. This command inspects your database schema and generates a Prisma schema based on it.

Here are the steps you can follow:

Install Prisma CLI by running

npm install -g prisma

Create a new Prisma schema file if you haven't already. You can do this by running prisma init in your project directory and selecting a database provider (in your case, Postgres).

In your terminal, run prisma introspect followed by the connection URL to your database, for example:

prisma introspect postgres://username:password@host:port/database

This will introspect your Postgres database and generate a Prisma schema file based on the existing tables and columns.

After running the prisma introspect command, you should see a new schema.prisma file in your project directory. This file will contain the models and fields that Prisma has generated based on the existing database schema. Note that Prisma's generated schema may not perfectly match your existing database schema, so you may need to make adjustments to it. For example, you may need to add or remove fields, or specify custom mappings for specific fields. Once you have made the necessary changes, you can run prisma generate to generate the Prisma client based on your updated schema.

npx prisma db pull

Prisma schema loaded from prisma/schema.prisma
Environment variables loaded from .env
Datasource "db": PostgreSQL database "prisma-postgresql-seeding-example", schema "public" at "localhost:5432"

✖ Introspecting based on datasource defined in prisma/schema.prisma

Error: P1012 Introspection failed as your current Prisma schema file is invalid

Please fix your current schema manually, use prisma validate to confirm it is valid and then run this command again.
Or run this command with the --force flag to ignore your current schema and overwrite it. All local modifications will be lost.

Fix via force

npx prisma db pull --force

Output

Prisma schema loaded from prisma/schema.prisma
Environment variables loaded from .env
Datasource "db": PostgreSQL database "prisma-postgresql-seeding-example", schema "public" at "localhost:5432"

✔ Introspected 3 models and wrote them into prisma/schema.prisma in 239ms

Run prisma generate to generate Prisma Client.

npx prisma generate

Output

Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma

✔ Generated Prisma Client (3.14.0 | library) to ./node_modules/@prisma/client in 278ms
You can now start using Prisma Client in your code. Reference: https://pris.ly/d/client

```java
import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient()

npx prisma migrate dev --name init

Output

Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "prisma-postgresql-seeding-example", schema "public" at "localhost:5432"

Applying migration `20220519084519_init`

The following migration(s) have been created and applied from new schema changes:

migrations/
  └─ 20220519084519_init/
    └─ migration.sql

Your database is now in sync with your schema.

✔ Generated Prisma Client (3.14.0 | library) to ./node_modules/@prisma/client in 220ms

Prisma Studio is a visual editor for the data in your database. You can run it with two ways:

Run npx prisma studio in your terminal.

npx prisma studio

Install Postgresql on Debian and Ubuntu

You can either choose to use the version of PostgreSQL available in your distribution's default repositories or use repositories provided by the PostgreSQL project. Packages in the default repository are tested to work with all other software provided for your distribution, but may be older. Packages from the PostgreSQL project will be more up-to-date but may require extra configuration.

Install using Debian or Ubuntu's default repositories

Install using the PostgreSQL project's Debian and Ubuntu repositories

Install using Debian or Ubuntu's default repositories

Both Ubuntu and Debian provide versions of PostgreSQL server as packages within their default repositories. The PostgreSQL version may be older than those found on the PostgreSQL website, but this is the simplest way to install on these distributions.

To install PostgreSQL server, update your computer's local package cache with the latest set of packages. Afterwards, install the postgresql package:

sudo apt update
sudo apt install postgresql

By default, PostgreSQL is configured to use peer authentication, which allows users to log in if their operating system user name matches a PostgreSQL internal name.

The installation process created an operating system user called postgres to match the postgres database administrative account. To log into PostgreSQL with the psql client, use sudo to run the command as the postgres user:

sudo -u postgres psql

or

psql -h localhost -p 5432 -U postgres

Once you are connected to your database, run the following command to list all tables in the current schema:

\dt

This should display a list of all tables in the current schema, including the tables you have created.

If you want to see more information about a specific table, you can use the \d command followed by the name of the table. For example, if you want to see the details of the ev_locations table, you can run:

\d ev_locations

To determine the name of the database and schema that you are currently connected to in psql, you can use the \conninfo command.

Simply open psql and run the following command:

\conninfo

This should display information about the columns, constraints, and indexes defined on the ev_locations table.

You are connected to database "mydatabase" as user "myuser" via socket in "/var/run/postgresql" at port "5432".

You can check the current database and schema in psql by running the following command:

SELECT current_database(), current_schema();

To list the different databases in PostgreSQL, you can use the following command in the psql command-line interface:

\list

If you've inserted rows into your database outside of Prisma, then Prisma's knowledge of the number of rows in the affected table(s) may be out of date. To update Prisma's knowledge of the row count, you can use the prisma.db.$queryRaw() method to execute a SQL query that retrieves the row count for the table.

Here's an example of how to update Prisma's knowledge of the row count for the ev_locations table:

const rowCount = await prisma.db.$queryRaw(
  'SELECT COUNT(*) FROM ev_locations'
);
prisma.ev_locations.count = rowCount[0].count;

In this example, the prisma.db.$queryRaw() method is used to execute a SQL query that returns the row count for the ev_locations table. The result is an array with a single object that has a count property. This property contains the row count for the table.

The count property is then assigned to the count property of the ev_locations Prisma client, which updates Prisma's knowledge of the row count for the table.

You can use similar code to update the row count for any other tables that you've modified outside of Prisma.

When you are finished, you can exit the psql session by typing:

\quit

Changing the Password

With a connection now established to Postgres at the psql prompt, issue the ALTER USER command to change the password for the postgres user:

postgres=# ALTER USER postgres PASSWORD 'myPassword';

Output

ALTER ROLE

If successful, Postgres will output a confirmation of ALTER ROLE as seen above.

Finally, exit the psql client by using the \q command.

postgres=# \q

You’re all done. The default postgres user now has a password associated with the account for use in your other applications.

Relational databases - TypeScript - PostgreSQL

Learn how to add Prisma to an existing Node.js or TypeScript project by connecting it to your database and generating a Prisma Client for database access. The following tutorial introduces you to the Prisma CLI, Prisma Client, and Prisma Introspection.

Prerequisites

In order to successfully complete this guide, you need:

• an existing Node.js project with a package.json
• Node.js installed on your machine
• a PostgreSQL database server running and a database with at least one table

See System requirements for exact version requirements.

Make sure you have your database connection URL (that includes your authentication credentials) at hand! If you don't have a database server running and just want to explore Prisma, check out the Quickstart.

Set up Prisma

As a first step, navigate into your project directory that contains the package.json file.

Next, add the Prisma CLI as a development dependency to your project:

npm install prisma --save-dev

You can now invoke the Prisma CLI by prefixing it with npx:

npx prisma

Next, set up your Prisma project by creating your Prisma schema file template with the following command:

npx prisma init

Output

✔ Your Prisma schema was created at prisma/schema.prisma
  You can now open it in your favorite editor.

warn Prisma would have added DATABASE_URL but it already exists in .env
warn You already have a .gitignore. Don't forget to exclude .env to not commit any secret.

Next steps:
1. Set the DATABASE_URL in the .env file to point to your existing database. If your database has no tables yet, read https://pris.ly/d/getting-started
2. Set the provider of the datasource block in schema.prisma to match your database: postgresql, mysql, sqlite, sqlserver, mongodb or cockroachdb (Preview).
3. Run prisma db pull to turn your database schema into a Prisma schema.
4. Run prisma generate to generate the Prisma Client. You can then start querying your database.

More information in our documentation:
https://pris.ly/d/getting-started

This command does two things:

• creates a new directory called prisma that contains a file called schema.prisma, which contains the Prisma schema with your database connection variable and schema models
• creates the .env file in the root directory of the project, which is used for defining environment variables (such as your database connection)

Using Prisma Migrate

Creating the database schema

In this guide, you'll use Prisma Migrate to create the tables in your database. Add the following Prisma data model to your Prisma schema in prisma/schema.prisma:

//prisma/schema.prisma
model Post {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  title     String   @db.VarChar(255)
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
}

model Profile {
  id     Int     @id @default(autoincrement())
  bio    String?
  user   User    @relation(fields: [userId], references: [id])
  userId Int     @unique
}

model User {
  id      Int      @id @default(autoincrement())
  email   String   @unique
  name    String?
  posts   Post[]
  profile Profile?
}

To map your data model to the database schema, you need to use the prisma migrate CLI commands:

npx prisma migrate dev --name init

This command does two things:

• It creates a new SQL migration file for this migration
• It runs the SQL migration file against the database
• Note: generate is called under the hood by default, after running prisma migrate dev. If the prisma-client-js generator is defined in your schema, this will check if @prisma/client is installed and install it if it's missing.

Great, you now created three tables in your database with Prisma Migrate 🚀

CREATE TABLE "Post" (
  "id" SERIAL,
  "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" TIMESTAMP(3) NOT NULL,
  "title" VARCHAR(255) NOT NULL,
  "content" TEXT,
  "published" BOOLEAN NOT NULL DEFAULT false,
  "authorId" INTEGER NOT NULL,
  PRIMARY KEY ("id")
);

CREATE TABLE "Profile" (
  "id" SERIAL,
  "bio" TEXT,
  "userId" INTEGER NOT NULL,
  PRIMARY KEY ("id")
);

CREATE TABLE "User" (
  "id" SERIAL,
  "email" TEXT NOT NULL,
  "name" TEXT,
  PRIMARY KEY ("id")
);

CREATE UNIQUE INDEX "Profile.userId_unique" ON "Profile"("userId");
CREATE UNIQUE INDEX "User.email_unique" ON "User"("email");
ALTER TABLE "Post" ADD FOREIGN KEY("authorId")REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "Profile" ADD FOREIGN KEY("userId")REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;

Install Prisma Client

https://www.prisma.io/docs/getting-started/setup-prisma/add-to-existing-project/relational-databases/install-prisma-client-typescript-postgres

TypeScript - PostgreSQL

Install and generate Prisma Client

To get started with Prisma Client, you need to install the @prisma/client package:

npm install @prisma/client

Notice that the @prisma/client node module references a folder named .prisma/client. The .prisma/client folder contains your generated Prisma client, and is modified each time you change the schema and run the following command:

npx prisma generate

Output

Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma

✔ Generated Prisma Client (3.14.0 | library) to ./node_modules/@prisma/client in 387ms
You can now start using Prisma Client in your code. Reference: https://pris.ly/d/client

import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient()

This command reads your Prisma schema and generates your Prisma Client library

Querying the database

Write your first query with Prisma Client

Now that you have generated the Prisma Client, you can start writing queries to read and write data in your database.

If you're building a REST API, you can use Prisma Client in your route handlers to read and write data in the database based on incoming HTTP requests. If you're building a GraphQL API, you can use Prisma Client in your resolvers to read and write data in the database based on incoming queries and mutations.

For the purpose of this guide however, you'll just create a plain Node.js script to learn how to send queries to your database using Prisma Client. Once you have an understanding of how the API works, you can start integrating it into your actual application code (e.g. REST route handlers or GraphQL resolvers).

Create a new file named index.ts and add the following code to it:

//index.ts
import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

async function main() {
  // ... you will write your Prisma Client queries here
}

main()
  .catch((e) => {
    throw e
  })
  .finally(async () => {
    await prisma.$disconnect()
  })

Here's a quick overview of the different parts of the code snippet:

• Import the PrismaClient constructor from the @prisma/client node module
• Instantiate PrismaClient
• Define an async function named main to send queries to the database
• Call the main function
• Close the database connections when the script terminates

Depending on what your models look like, the Prisma Client API will look different as well. For example, if you have a User model, your PrismaClient instance exposes a property called user on which you can call CRUD methods like findMany, create or update. The property is named after the model, but the first letter is lowercased (so for the Post model it's called post, for Profile it's called profile).

The following examples are all based on the models in the Prisma schema.

Inside the main function, add the following query to read all User records from the database and print the result:

// index.ts
async function main() {
  const allUsers = await prisma.user.findMany()
  console.log(allUsers)
}

Now run the code with your current TypeScript setup. If you're using ts-node, you can run it like this:

npx ts-node index.ts

Output

Need to install the following packages:
  ts-node
Ok to proceed? (y) y
[]

This will print an array of User records as plain old JavaScript objects.

Write data into the database

The findMany query you used in the previous section only reads data from the database. In this section, you'll learn how to write a query to write new records into the Post and User tables.

Adjust the main function to send a create query to the database:

//index.ts
async function main() {
  await prisma.user.create({
    data: {
      name: 'Alice',
      email: 'alice@prisma.io',
      posts: {
        create: { title: 'Hello World' },
      },
      profile: {
        create: { bio: 'I like turtles' },
      },
    },
  })

  const allUsers = await prisma.user.findMany({
    include: {
      posts: true,
      profile: true,
    },
  })
  console.dir(allUsers, { depth: null })
}

This code creates a new User record together with new Post and Profile records using a nested write query. The User record is connected to the two other ones via the Post.author ↔ User.posts and Profile.user ↔ User.profile relation fields respectively.

Notice that you're passing the include option to findMany which tells Prisma Client to include the posts and profile relations on the returned User objects.

Run the code with your current TypeScript setup. If you're using ts-node, you can run it like this:

npx ts-node index.ts

Before moving on to the next section, you'll "publish" the Post record you just created using an update query. Adjust the main function as follows:

//index.ts
async function main() {
  const post = await prisma.post.update({
    where: { id: 1 },
    data: { published: true },
  })
  console.log(post)
}

Run the code with your current TypeScript setup. If you're using ts-node, you can run it like this:

npx ts-node index.ts

Explore the data in Prisma Studio

Prisma Studio is a visual editor for the data in your database. You can run it with two ways:

Run npx prisma studio in your terminal.

npx prisma studio

Getting started

This is a sample of how you can easily seed relational data using Prisma. This assumes that you've got a PostgreSQL database created and have some working knowledge of Prisma. If you are using a different SQL database other than PostgreSQL, see below for further instructions.

For more information on getting started, check out the official Prisma guide.

Installation

• Run npm install or yarn install
• Create a .env file in the root of the project. You can use the .env.example file as an example of what it should look like.
• Inside of your .env file, point your DATABASE_URL variable to a fresh PostgreSQL database of your choice. NOTE: this will wipe out any existing data in the database you point to, so make sure you set up a fresh database for this example!

Using this with a different SQL database other than PostgreSQL

If you are using a different database other than PostgreSQL, modify the provider at the top of the prisma/schema.prisma file.

Running the seed

• Use the command npm run seed or yarn seed

npm run seed

Viewing your seed data

• Use the prisma studio command

Further reading

You for additional reading, you can check out my full blog post here.