Table Doesn't Exist in Database

[xsky13]

I’m making an education website using remix.run and prisma, and uploaded everything without errors, but I’m getting this error:

[jsierles]

Did you setup your fly.toml to run Prisma migrations?

[xsky13]

Here’s the contents of the fly.toml file:

# fly.toml file generated for setela on 2022-04-25T16:17:16-03:00

app = "setela"

kill_signal = "SIGINT"
kill_timeout = 5
processes = []

[env]
  DATABASE_URL = "file:/data/sqlite.db"
  PORT = "8080"

[experimental]
  cmd = "start_with_migrations.sh"
  entrypoint = "sh"

[[mounts]]
  destination = "/data"
  source = "data"

[[services]]
  http_checks = []
  internal_port = 8080
  processes = ["app"]
  protocol = "tcp"
  script_checks = []

  [services.concurrency]
    hard_limit = 25
    soft_limit = 20
    type = "connections"

  [[services.ports]]
    force_https = true
    handlers = ["http"]
    port = 80

  [[services.ports]]
    handlers = ["tls", "http"]
    port = 443

  [[services.tcp_checks]]
    grace_period = "1s"
    interval = "15s"
    restart_limit = 0
    timeout = "2s"

[jsierles]

Can you check fly logs to see if your migrations are running correctly during a deployment?

[xsky13]

Here they are:

[jsierles]

It looks like your DATABASE_URL is not being picked up. Can you post your Prisma schema?

[xsky13]

Here:

// 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 = "sqlite"
    url      = env("DATABASE_URL")
}

model User {
    id                  String                @id @default(uuid())
    name                String
    email               String
    password            String
    createdAt           String
    telefono            String
    direccion           String
    profesor            Boolean
    admin               Boolean
    EmailChangeRequests EmailChangeRequests[]
    Material            Material[]
    Curso               Curso?                @relation(fields: [cursoId], references: [id])
    cursoId             String?
}

model Curso {
    id         String      @id @default(uuid())
    title      String
    Users      User[]
    Trimestres Trimestre[]
}

model Trimestre {
    id       String    @id @default(uuid())
    number   String
    cursoId  String?
    Curso    Curso?    @relation(fields: [cursoId], references: [id], onDelete: Cascade)
    Periodos Periodo[]
}

model Periodo {
    id          String    @id @default(uuid())
    title       String
    number      String
    Trimestre   Trimestre @relation(fields: [trimestreId], references: [id], onDelete: Cascade)
    Lecciones   Leccion[]
    trimestreId String
}

model Leccion {
    id        String     @id @default(uuid())
    title     String
    number    String
    content   String
    createdAt String
    periodoId String
    Material  Material[]
    Periodo   Periodo?   @relation(fields: [periodoId], references: [id], onDelete: Cascade)
}

model EmailChangeRequests {
    id          String @id @default(uuid())
    userId      String
    email       String
    newEmail    String
    requestedAt String
    user        User   @relation(fields: [userId], references: [id], onDelete: Cascade)
}

model Material {
    id        String  @id @default(uuid())
    userId    String
    leccionId String
    createdAt String
    link      String
    tarea     Boolean
    User      User    @relation(fields: [userId], references: [id], onDelete: Cascade)
    Leccion   Leccion @relation(fields: [leccionId], references: [id], onDelete: Cascade)
}

[gerardnico]

If this is SQLite, as I see, your table should have no schema.

They are not supported in SQLite.

[xsky13]

What do you mean? What should I do?

[xsky13]

Maybe I should rename all the tables in the migrations folder to begin with “Main.”? Such as “Main.User”, “Main.Curso”, etc.

[gerardnico]

I don’t have any Prisma knowledge but SQLite does not understand/support SQL schema.

A table name in SQL as the form catalog.schema.name.

SQLite does not support catalog either.

It means that the table name main.curso is not valid (table does not exist cause may be) but the name curso is.

I have no knowledge of Prisma and of your code but you should delete the schema.

[xsky13]

I don’t know, I followed a tutorial (Remix | Jokes App), and I think I followed it step by step, and that’s how they had that code

[jsierles]

I think your schema is fine. What looks odd to me here is:

Datasource "db": SQLite database "dev.db" at "file:./dev.db"

This means your DATABASE_URL is getting reset somehow. What command do you use to deploy?

[xsky13]

I used this:

fly secrets set DATABASE_URL=file:./dev.db

[jsierles]

Also, is it possible that you did not create migrations for some of these tables? If you updated your schema manually, it will not be loaded in production. You need to do everything via migrations.

[jsierles]

@xsky13

I used this:

fly secrets set DATABASE_URL=file:./dev.db

So this means your database will be wiped out on every deploy. You already have DATABASE_URL set in fly.toml and secrets will override that.

It needs to live on a persistent volume you’d create with fly volumes create. The Remix Indie Stack is a good guide to making SQLite deployment work.

[xsky13]

No, I have them here:

[jsierles]

For now, try fly secrets unset DATABASE_URL and see what happens. That should allow the database to be created on the persistent volume.

[xsky13]

Ok, I’ll try that

[xsky13]

Ok, I did that, and it worked. So then I created a new table, ran the seed command, and deployed a new version. Bu I got this error now: