Connecting Nile DB with prisma

[trace2798]

I was playing around with Nile DB and came across the following error when I ran npx prisma db push

My schema.prisma file contain the following
`generator client {
provider = "prisma-client-js"
}

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

model Book {
id Int @id @default(autoincrement())
user_id String
user_name String
src String
name String
description String
author String
createdAt DateTime @default(now())

summaries Summary[]
}

model Summary {
id Int @id @default(autoincrement())
title String
content String
createdAt DateTime @default(now())

book_id Int
user_id String
user_name String
book Book @relation(fields: [book_id], references: [id], onDelete: Cascade)
rating Int?

@@index([book_id])
}`

Framework in use: Next.js

[gwenshap]

Since Nile includes built-in tables (for example, tenants), you need to start by pulling the existing schema from Nile to Prisma.

npx prisma db pull should do the trick.
Then you can use these to generate Prisma objects for the built in tables with npx prisma generate

and then pushing your own objects with npx prisma db push should work.

[trace2798]

Hey @gwenshap thanks. I did npx prisma db pull and then the rest of the command.
I got the following

[gwenshap]

Mmm... "unexpected message from server" isn't very informative from Prisma.

Let me see if I can reproduce this locally and look into it.
If you can share your workspace name, I can also check if there were errors on Nile side.

[trace2798]

Hey the workspace name is adventurous_fish and the database name is demo_db_nextjs_qs

[gwenshap]

I checked the logs on our side, and it looks like the last message sent back from your database to Prisma is:
adding constraints to an existing table is currently not supported

Which is exactly what I got when trying to use npx prisma db push to my database:

I am not sure why Prisma isn't showing you that error though...

The reason for this error is that Nile expects the create table statement to also include the constraints, rather than perform a separate alter step. I'll update the docs with this limitation and we'll target a solution in Nile as well.

Meanwhile, to unblock the project... I suggest managing tables manually for now.

Something like this:

CREATE TABLE "book" (
    "id" UUID PRIMARY KEY default public.uuid_generate_v7(),
    "user_id" UUID NOT NULL,
    "user_name" TEXT NOT NULL,
    "src" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "description" TEXT NOT NULL,
    "author" TEXT NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE "summary" (
    "id" UUID PRIMARY KEY default public.uuid_generate_v7(),
    "title" TEXT NOT NULL,
    "content" TEXT NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "book_id" UUID NOT NULL references "book"("id") ON DELETE CASCADE,
    "user_id" UUID NOT NULL,
    "user_name" TEXT NOT NULL,
    "rating" INTEGER
);

I also noticed that these tables don't have tenant_id column. Without this column, the table won't be tenant aware and you won't get most of Nile's features.

If you want to include tenant_id in your tables, you can use something like this:

CREATE TABLE "book" (
    "id" UUID,
    "tenant_id" UUID NOT NULL,
    "user_id" UUID NOT NULL,
    "user_name" TEXT NOT NULL,
    "src" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "description" TEXT NOT NULL,
    "author" TEXT NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT "book_pkey" PRIMARY KEY ("id", "tenant_id")
);

CREATE TABLE "summary" (
    "id" UUID,
    "tenant_id" UUID NOT NULL,
    "title" TEXT NOT NULL,
    "content" TEXT NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "book_id" UUID NOT NULL,
    "user_id" UUID NOT NULL,
    "user_name" TEXT NOT NULL,
    "rating" INTEGER,
    CONSTRAINT "summary_pkey" PRIMARY KEY ("id", "tenant_id"),
    CONSTRAINT "summary_book_id_fkey" FOREIGN KEY ("book_id", "tenant_id") REFERENCES "book"("id","tenant_id") ON DELETE CASCADE
);

[trace2798]

Thanks for the response @gwenshap, I will work with this recommendation.