Search code examples
postgresqlnext.jsschemapsqlprisma

How do I find the rows I put into my postgres db with Prisma in the psql command line?


I created a schema with prisma. Here is what my prisma file looks like:
schema.prisma

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

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

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

model User {
  id            String       @default(cuid()) @id
  name          String?
  email         String?   @unique
  createdAt     DateTime  @default(now()) @map(name: "created_at")
  updatedAt     DateTime  @updatedAt @map(name: "updated_at")
  is_confirmed  Boolean   @default(false)
  confirm_key   String
  @@map(name: "users")
}  

I have a running instance of Postgres in a docker container and I referenced it like this in my env file:
.env

# See the documentation for more detail: https://pris.ly/d/prisma-schema#accessing-environment-variables-from-the-schema

# Prisma supports the native connection string format for PostgreSQL, MySQL, SQLite, SQL Server, MongoDB and CockroachDB.
# See the documentation for all the connection string options: https://pris.ly/d/connection-strings

DATABASE_URL="postgresql://POSTGRES_USERNAME:PASSWORD@localhost:5432/DB_NAME?schema=users"  

I ran the command npx prisma db push and it appeared to work because I got this in my terminal:

Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "DB_NAME", schema "users" at "localhost:5432"

🚀  Your database is now in sync with your Prisma schema. Done in 132ms

Running generate... (Use --skip-generate to skip the generators)

added 2 packages, and audited 338 packages in 5s

117 packages are looking for funding
  run `npm fund` for details

found 0 vulnerabilities

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

I opened prisma studio with npx prisma studio

I added 2 entries via prisma studio:
enter image description here

But when I log into the db with psql, I can't find the data that I input into the db:

MYUSERNAME@MYCOMPUTERNAME my-current-directory % docker exec -it DOCKERCONTAINERNAME bash
root@DOCKERCONTAINERID:/# psql -h localhost -U POSTGRES_USERNAME DB_NAME
psql (15.2 (Debian 15.2-1.pgdg110+1))
Type "help" for help.

DB_NAME=> SELECT * FROM User;
        user        
--------------------
 POSTGRES_USERNAME
(1 row)

DB_NAME=>

I don't see any entries. What am I doing wrong?


Solution

  • It was a simple fix. DATABASE_URL="postgresql://POSTGRES_USERNAME:PASSWORD@localhost:5432/DB_NAME?schema=users" should have been set to DATABASE_URL="postgresql://POSTGRES_USERNAME:PASSWORD@localhost:5432/DB_NAME?schema=public". Notice I changed schema=users to schema=public