Migrate: Prisma doesn't create tables in SQLite

[hamidb80]

Bug description

When I want to do a query from user table (or any other tables) prisma says the table does not exist.

error:

  The table `dev.User` does not exist in the current database.
    at iy.request (/home/hamidb80/Documents/programming/projects/dairyto/backend/node_modules/@prisma/client/runtime/src/runtime/getPrismaClient.ts:1181:15)
    at process._tickCallback (internal/process/next_tick.js:68:7)
(node:17816) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). (rejection id: 4)
(node:17816) UnhandledPromiseRejectionWarning: Error: 
Invalid `prisma.user.findMany()` invocation in
/home/hamidb80/Documents/programming/projects/dairyto/backend/src/views/main.ts:17:31


  The table `dev.User` does not exist in the current database.
    at iy.request (/home/hamidb80/Documents/programming/projects/dairyto/backend/node_modules/@prisma/client/runtime/src/runtime/getPrismaClient.ts:1181:15)
    at process._tickCallback (internal/process/next_tick.js:68:7)
(node:17816) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). (rejection id: 6)
prisma:query SELECT 1
prisma:query SELECT `dev`.`User`.`id`, `dev`.`User`.`email`, `dev`.`User`.`name` FROM `dev`.`User` WHERE 1=1 LIMIT ? OFFSET ?
^C

I opened the database by a database manager software (dbeaver in this case), there was only one table (_Migrations) which was empty.

How to reproduce

You have 2 choices:

1. you can clone my repo on your computer:
   https://github.com/hamidb80/diaryto-backend/tree/a83327c108186ee7c12ca712ddd0253323f0da1d

run these commands:

npx prisma generate --schema=./src/database/schema.prisma
npx prisma migrate up --schema=./src/database/schema.prisma --experimental
npx prisma migrate save --name init --schema=./src/database/schema.prisma --experimental

run the server by this:

npm start

and go to http://127.0.0.1:<port>/api/ [the default value of port is 8000]

1. put my Prisma schema file in a new project
   after that, enter these commands in the terminal

npx prisma generate --schema=<schema.prisma path>
npx prisma migrate up --schema=<schema.prisma path> --experimental
npx prisma migrate save --name init --schema=<schema.prisma path> --experimental

and then do a query or check the database

Expected behavior

get records from the tables correctly

Prisma information

Prisma schema:

datasource db {
    provider = "sqlite"
    url      = "file:./dev.db"
}

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

model User {
    id    Int     @id @default(autoincrement())
    email String  @unique
    name  String? 
}

model Video {
    id    Int     @id @default(autoincrement())
    path String  @unique
    title  String? 
}

Environment & setup

• OS: ubuntu mate 20.04.1 LTS
• Database: SQLite
• Node.js version: v10.19.0
• Prisma version: ^2.7.1

[tomhoule]

I think I may have found the problem.

I cloned the repo at the commit hash you provided, then ran npm i, then npm run build-up and the database was migrated correctly:

I think the issue comes from these commands:

npx prisma generate --schema=/src/database/schema.prisma
npx prisma migrate up --schema=</src/database/schema.prisma --experimental
npx prisma migrate save --name init --schema=/src/database/schema.prisma --experimental

I did try to run them with logging enabled and got:

erewhon.tom.~/src/tmp/diaryto-backend λ npx prisma generate --schema=/src/database/schema.prisma
npx prisma migrate up --schema=</src/database/schema.prisma --experimental
npx prisma migrate save --name init --schema=/src/database/schema.prisma --experimental
  prisma Environment variables not loaded (--schema was provided) +0ms
Error: Error: Provided --schema at /src/database/schema.prisma doesn't exist.
    at /home/tom/src/tmp/diaryto-backend/node_modules/@prisma/cli/build/index.js:2:2830627
    at Generator.next (<anonymous>)
    at fulfilled (/home/tom/src/tmp/diaryto-backend/node_modules/@prisma/cli/build/index.js:2:2829548)
zsh: no such file or directory: /src/database/schema.prisma
  prisma Environment variables not loaded (--schema was provided) +0ms
Error: Error: Provided --schema at /src/database/schema.prisma doesn't exist.
    at /home/tom/src/tmp/diaryto-backend/node_modules/@prisma/cli/build/index.js:2:2830627
    at Generator.next (<anonymous>)
    at fulfilled (/home/tom/src/tmp/diaryto-backend/node_modules/@prisma/cli/build/index.js:2:2829548)

I think the correct command would be npx @prisma/cli. @Jolg42 can you confirm?

[hamidb80]

I think the issue comes from these commands:

npx prisma generate --schema=/src/database/schema.prisma
npx prisma migrate up --schema=</src/database/schema.prisma --experimental
npx prisma migrate save --name init --schema=/src/database/schema.prisma --experimental

it was a my mistake. I forgot to add a dot before the commands. I corrected my issue.

now commands are:

npx prisma generate --schema=./src/database/schema.prisma
npx prisma migrate up --schema=./src/database/schema.prisma --experimental
npx prisma migrate save --name init --schema=./src/database/schema.prisma --experimental

when enter command npx prisma migrate up --schema=./src/database/schema.prisma --experimental the prisma says:

You are trying to apply a migration for SQLite database dev.db.
A database with that name doesn't exist at file:./dev.db

and I pick yes.

prisma creates the database but it's empty:

[Jolg42]

I could not reproduce, I cloned the repo and ran:

rm src/database/dev.db 
rm -R src/database/migrations/
npx prisma migrate save --name init --schema=./src/database/schema.prisma --experimental
npx prisma migrate up --schema=./src/database/schema.prisma --experimental

TablePlus shows the tables

Prisma version from npx prisma -v

@prisma/cli          : 2.7.1
Current platform     : debian-openssl-1.1.x
Query Engine         : query-engine 5c2ad460cf4fe8c9330e6640b266c046542c8b6a (at node_modules/@prisma/cli/query-engine-debian-openssl-1.1.x)
Migration Engine     : migration-engine-cli 5c2ad460cf4fe8c9330e6640b266c046542c8b6a (at node_modules/@prisma/cli/migration-engine-debian-openssl-1.1.x)
Introspection Engine : introspection-core 5c2ad460cf4fe8c9330e6640b266c046542c8b6a (at node_modules/@prisma/cli/introspection-engine-debian-openssl-1.1.x)
Format Binary        : prisma-fmt 5c2ad460cf4fe8c9330e6640b266c046542c8b6a (at node_modules/@prisma/cli/prisma-fmt-debian-openssl-1.1.x)
Studio               : 0.288.0

Could you check which version of Prisma you are using?
Could you try again with the same commands as me maybe?

[hamidb80]

I upgraded my nodejs from 10 to 14 and updated my npm.
Everything works great.

Thanks for answering.