Migrate: code: SqlState("42703"), message: "column \"personId\" does not exist",

[AhmedElywa]

i get this error with postgresql anf not get it with sqlite
if i delete this relation inside Person model
student Student? @relation("PersonStudent")
the error go

➜  testHup prisma2 lift up  
🏋️‍ lift up

Datamodel that will initialize the db:

generator photon {
  provider = "photonjs"
}

datasource db {
  provider = "postgresql"
  url = "***"
}

model Activity {
  id        Int      @id @unique
  person    Person   @map("personId")
  day       DateTime
  title     String
  color     String?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

model Person {
  id         Int         @id @unique
  user       User        @map("userId") @relation("PersonUser") @unique
  student    Student?    @relation("PersonStudent")
  parent     Parent?
  activities Activity[]
  firstName  String
  lastName   String
  dob        DateTime?
  gender     String?
  grade      String?
  phone      String?
  addrLine1  String?
  addrLine2  String?
  city       String?
  state      String?
  zip        String?
  country    String?
  email      String?
  createdAt  DateTime    @default(now())
  updatedAt  DateTime    @updatedAt
}
model User {
  id                       Int                  @id @unique
  person                   Person               @map("personId") @relation("PersonUser")
  username                 String               @unique
  password                 String
  passwordResetToken       String?              @unique
  passResetExpires         DateTime?
  pressAcctNum             Int?
  avatarLink               String?
  userTheme                String?
  font                     String?
  isParent                 Boolean?
  isCommunity              Boolean?
  isVisible                Boolean?
  lessonsPerDay            Int?
  canSeeGrades             Boolean?
  canReceiveDirectMessages Boolean?
  canAddEventsToCalendar   Boolean?
  canAccessCommunity       Boolean?
  communityPostAlertAlways Boolean?
  createdAt                DateTime             @default(now())
  updatedAt                DateTime             @updatedAt
}
model Parent {
  id               Int            @id @unique
  person           Person         @map("personId") @unique
  students         Student[]      @relation("Students")
  coppaSignedTime  DateTime?
  coppaIsSigned    Boolean?       @default(false)
  profileEdited    Boolean?       @default(false)
  hideVideo        Boolean?       @default(false)
  vitalSourceToken String?
  createdAt        DateTime       @default(now())
  updatedAt        DateTime       @updatedAt
}
model Student {
  id            Int            @id @unique
  person        Person         @map("personId") @relation("PersonStudent") @unique
  parent        Parent         @map("parentId") @relation("Students")
  assessmentPwd String?
  lessonsPerDay Int?
  createdAt     DateTime       @default(now())
  updatedAt     DateTime       @updatedAt
}

Checking the datasource for potential data loss...

Database Changes:

Migration            Database actions           Status

20191128220309-init  5 CreateTable statements.  

You can get the detailed db changes with prisma2 lift up --verbose
Or read about them in the ./migrations/MIGRATION_ID/README.md
Error: QueryError(QueryError(QueryError(Error { kind: Db, cause: Some(DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState("42703"), message: "column \"personId\" does not exist", detail: None, hint: None, position: None, where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("indexcmds.c"), line: Some(1582), routine: Some("ComputeIndexAttrs") }) }

stack backtrace:
   0: backtrace::backtrace::trace
   1: backtrace::capture::Backtrace::new_unresolved
   2: failure::backtrace::internal::InternalBacktrace::new
   3: <failure::backtrace::Backtrace as core::default::Default>::default
   4: quaint::connector::postgres::error::<impl core::convert::From<tokio_postgres::error::Error> for quaint::error::Error>::from
   5: <std::future::GenFuture<T> as core::future::future::Future>::poll
   6: tokio_executor::enter::Enter::block_on
   7: tracing_core::dispatcher::with_default
   8: std::thread::local::LocalKey<T>::with
   9: tokio::runtime::threadpool::Runtime::block_on
  10: <sql_connection::generic_sql_connection::GenericSqlConnection as sql_connection::traits::SyncSqlConnection>::query_raw
  11: sql_migration_connector::sql_database_step_applier::SqlDatabaseStepApplier::apply_next_step
  12: <sql_migration_connector::sql_database_step_applier::SqlDatabaseStepApplier as migration_connector::database_migration_step_applier::DatabaseMigrationStepApplier<sql_migration_connector::sql_migration::SqlMigration>>::apply_step
  13: <migration_connector::migration_applier::MigrationApplierImpl<T> as migration_connector::migration_applier::MigrationApplier<T>>::apply
  14: migration_engine::commands::apply_migration::ApplyMigrationCommand::handle_migration
  15: <migration_engine::commands::apply_migration::ApplyMigrationCommand as migration_engine::commands::command::MigrationCommand>::execute
  16: <migration_engine::api::MigrationApi<C,D> as migration_engine::api::GenericApi>::apply_migration
  17: migration_engine::api::rpc::RpcApi::create_sync_handler
  18: tokio_executor::enter::exit
  19: tokio_threadpool::blocking::blocking
  20: <futures::future::lazy::Lazy<F,R> as futures::future::Future>::poll
  21: futures::future::chain::Chain<A,B,C>::poll
  22: <futures::future::then::Then<A,B,F> as futures::future::Future>::poll
  23: <futures::future::lazy::Lazy<F,R> as futures::future::Future>::poll
  24: <futures::future::then::Then<A,B,F> as futures::future::Future>::poll
  25: <futures::future::map::Map<A,F> as futures::future::Future>::poll
  26: <futures::future::either::Either<A,B> as futures::future::Future>::poll
  27: <futures::future::map::Map<A,F> as futures::future::Future>::poll
  28: <futures::future::map_err::MapErr<A,F> as futures::future::Future>::poll
  29: <futures::stream::and_then::AndThen<S,F,U> as futures::stream::Stream>::poll
  30: <futures::stream::forward::Forward<T,U> as futures::future::Future>::poll
  31: <futures::future::map::Map<A,F> as futures::future::Future>::poll
  32: <futures::future::map_err::MapErr<A,F> as futures::future::Future>::poll
  33: futures::task_impl::std::set
  34: std::panicking::try::do_call
  35: __rust_maybe_catch_panic
  36: tokio_threadpool::task::Task::run
  37: tokio_threadpool::worker::Worker::run_task
  38: tokio_threadpool::worker::Worker::run
  39: std::thread::local::LocalKey<T>::with
  40: std::thread::local::LocalKey<T>::with
  41: std::thread::local::LocalKey<T>::with
  42: tokio::runtime::threadpool::builder::Builder::build::{{closure}}
  43: std::thread::local::LocalKey<T>::with
  44: std::thread::local::LocalKey<T>::with
  45: std::sys_common::backtrace::__rust_begin_short_backtrace
  46: std::panicking::try::do_call
  47: __rust_maybe_catch_panic
  48: core::ops::function::FnOnce::call_once{{vtable.shim}}
  49: <alloc::boxed::Box<F> as core::ops::function::FnOnce<A>>::call_once
  50: std::sys::unix::thread::Thread::new::thread_start
  51: _pthread_start
)

stack backtrace:
   0: backtrace::backtrace::trace
   1: backtrace::capture::Backtrace::new_unresolved
   2: failure::backtrace::internal::InternalBacktrace::new
   3: <failure::backtrace::Backtrace as core::default::Default>::default
   4: <failure::error::error_impl::ErrorImpl as core::convert::From<F>>::from
   5: sql_migration_connector::sql_database_step_applier::SqlDatabaseStepApplier::apply_next_step
   6: <sql_migration_connector::sql_database_step_applier::SqlDatabaseStepApplier as migration_connector::database_migration_step_applier::DatabaseMigrationStepApplier<sql_migration_connector::sql_migration::SqlMigration>>::apply_step
   7: <migration_connector::migration_applier::MigrationApplierImpl<T> as migration_connector::migration_applier::MigrationApplier<T>>::apply
   8: migration_engine::commands::apply_migration::ApplyMigrationCommand::handle_migration
   9: <migration_engine::commands::apply_migration::ApplyMigrationCommand as migration_engine::commands::command::MigrationCommand>::execute
  10: <migration_engine::api::MigrationApi<C,D> as migration_engine::api::GenericApi>::apply_migration
  11: migration_engine::api::rpc::RpcApi::create_sync_handler
  12: tokio_executor::enter::exit
  13: tokio_threadpool::blocking::blocking
  14: <futures::future::lazy::Lazy<F,R> as futures::future::Future>::poll
  15: futures::future::chain::Chain<A,B,C>::poll
  16: <futures::future::then::Then<A,B,F> as futures::future::Future>::poll
  17: <futures::future::lazy::Lazy<F,R> as futures::future::Future>::poll
  18: <futures::future::then::Then<A,B,F> as futures::future::Future>::poll
  19: <futures::future::map::Map<A,F> as futures::future::Future>::poll
  20: <futures::future::either::Either<A,B> as futures::future::Future>::poll
  21: <futures::future::map::Map<A,F> as futures::future::Future>::poll
  22: <futures::future::map_err::MapErr<A,F> as futures::future::Future>::poll
  23: <futures::stream::and_then::AndThen<S,F,U> as futures::stream::Stream>::poll
  24: <futures::stream::forward::Forward<T,U> as futures::future::Future>::poll
  25: <futures::future::map::Map<A,F> as futures::future::Future>::poll
  26: <futures::future::map_err::MapErr<A,F> as futures::future::Future>::poll
  27: futures::task_impl::std::set
  28: std::panicking::try::do_call
  29: __rust_maybe_catch_panic
  30: tokio_threadpool::task::Task::run
  31: tokio_threadpool::worker::Worker::run_task
  32: tokio_threadpool::worker::Worker::run
  33: std::thread::local::LocalKey<T>::with
  34: std::thread::local::LocalKey<T>::with
  35: std::thread::local::LocalKey<T>::with
  36: tokio::runtime::threadpool::builder::Builder::build::{{closure}}
  37: std::thread::local::LocalKey<T>::with
  38: std::thread::local::LocalKey<T>::with
  39: std::sys_common::backtrace::__rust_begin_short_backtrace
  40: std::panicking::try::do_call
  41: __rust_maybe_catch_panic
  42: core::ops::function::FnOnce::call_once{{vtable.shim}}
  43: <alloc::boxed::Box<F> as core::ops::function::FnOnce<A>>::call_once
  44: std::sys::unix::thread::Thread::new::thread_start
  45: _pthread_start
)

stack backtrace:
   0: backtrace::backtrace::trace
   1: backtrace::capture::Backtrace::new_unresolved
   2: failure::backtrace::internal::InternalBacktrace::new
   3: <failure::backtrace::Backtrace as core::default::Default>::default
   4: <failure::error::error_impl::ErrorImpl as core::convert::From<F>>::from
   5: <sql_migration_connector::sql_database_step_applier::SqlDatabaseStepApplier as migration_connector::database_migration_step_applier::DatabaseMigrationStepApplier<sql_migration_connector::sql_migration::SqlMigration>>::apply_step
   6: <migration_connector::migration_applier::MigrationApplierImpl<T> as migration_connector::migration_applier::MigrationApplier<T>>::apply
   7: migration_engine::commands::apply_migration::ApplyMigrationCommand::handle_migration
   8: <migration_engine::commands::apply_migration::ApplyMigrationCommand as migration_engine::commands::command::MigrationCommand>::execute
   9: <migration_engine::api::MigrationApi<C,D> as migration_engine::api::GenericApi>::apply_migration
  10: migration_engine::api::rpc::RpcApi::create_sync_handler
  11: tokio_executor::enter::exit
  12: tokio_threadpool::blocking::blocking
  13: <futures::future::lazy::Lazy<F,R> as futures::future::Future>::poll
  14: futures::future::chain::Chain<A,B,C>::poll
  15: <futures::future::then::Then<A,B,F> as futures::future::Future>::poll
  16: <futures::future::lazy::Lazy<F,R> as futures::future::Future>::poll
  17: <futures::future::then::Then<A,B,F> as futures::future::Future>::poll
  18: <futures::future::map::Map<A,F> as futures::future::Future>::poll
  19: <futures::future::either::Either<A,B> as futures::future::Future>::poll
  20: <futures::future::map::Map<A,F> as futures::future::Future>::poll
  21: <futures::future::map_err::MapErr<A,F> as futures::future::Future>::poll
  22: <futures::stream::and_then::AndThen<S,F,U> as futures::stream::Stream>::poll
  23: <futures::stream::forward::Forward<T,U> as futures::future::Future>::poll
  24: <futures::future::map::Map<A,F> as futures::future::Future>::poll
  25: <futures::future::map_err::MapErr<A,F> as futures::future::Future>::poll
  26: futures::task_impl::std::set
  27: std::panicking::try::do_call
  28: __rust_maybe_catch_panic
  29: tokio_threadpool::task::Task::run
  30: tokio_threadpool::worker::Worker::run_task
  31: tokio_threadpool::worker::Worker::run
  32: std::thread::local::LocalKey<T>::with
  33: std::thread::local::LocalKey<T>::with
  34: std::thread::local::LocalKey<T>::with
  35: tokio::runtime::threadpool::builder::Builder::build::{{closure}}
  36: std::thread::local::LocalKey<T>::with
  37: std::thread::local::LocalKey<T>::with
  38: std::sys_common::backtrace::__rust_begin_short_backtrace
  39: std::panicking::try::do_call
  40: __rust_maybe_catch_panic
  41: core::ops::function::FnOnce::call_once{{vtable.shim}}
  42: <alloc::boxed::Box<F> as core::ops::function::FnOnce<A>>::call_once
  43: std::sys::unix::thread::Thread::new::thread_start
  44: _pthread_start
)

[AhmedElywa]

the error from @unique in this line
person Person @map("personId") @relation("PersonStudent") @unique

[pantharshit00]

I can confirm this bug.

I am able to migrate the schema that you have shared in the sqlite connector but the same schema is throwing an error in the postgres connector.

I think this belongs to the Prisma 2 lift repository so I am going to transfer this over there.

The version that I used to confirm this bug:

[email protected], binary version: 6159bf3a263921c3c28ee68e2c9e130b5a69c293

[AhmedElywa]

the issue is @map("personId") not create this field. and when we use @unique will try to create index for this filed and get it does not exist

[tomhoule]

That's exactly the case @AhmedElywa !

I wrote a minimal schema that exhibits the bug in my tests:

        model Person {
            id Int @id
            student Student? @relation("PersonStudent")
        }

        model Student {
            id Int @id
            person Person @map("personId") @relation("PersonStudent") @unique
        }

We could fix the bug right now at the _migration_ level (lift), but we are going to implement this proposal for relations: https://github.com/prisma/specs/issues/348 - this can then become a _schema_ error, so you would see the problem with a good error message right in your editor (and lift would return the same error). This change is going to happen soon, so we will not implement a temporary fix for this issue.

[janpio]

Doesn't this only help for users actually using the formatter @tomhoule?

[tomhoule]

The datamodel validation is run whenever we parse the schema, so Lift should automatically refuse schemas with an @map on the virtual side of a relation once the relations proposal is implemented.

[AhmedElywa]

@tomhoule what i understand now is the problem id add @map with @relation . But if you looked at my schema will get many cases like this one and worked good
person Person @map("personId") @unique
user User @map("userId") @relation("PersonUser") @unique
Or i misunderstand and you mean another thing ?

[tomhoule]

Yes it's very subtle currently. A 1:1 relation in SQL is represented with a foreign key in one of the tables. The current logic in prisma 2 is that the foreign key will be on the table for the model with the lexicographically smaller name. The proposal I linked will make the side of the foreign key explicit.

So now the @map produces crashes only if it is on the side of the relation that doesn't have a concrete foreign key in the database (you can put it on the other model/field of the relation). It's definitely a bug, and we'll make sure it can't happen anymore when implement the relations proposal (very soon hopefully).

[tomhoule]

Update: this is still blocked on the changes we want to make to relations, as explained in the previous messages.

[tomhoule]

Status update: the changes to schema validation that would be required to make this crash impossible have been brought up in a meeting this week. (The feature is "more explicit relations", to force disambiguation of the foreign key side. specs issue: https://github.com/prisma/specs/issues/348). It will hopefully be tackled soon.

[tomhoule]

Status update: there is ongoing work on the schema/datamodel side of this issue in this PR: https://github.com/prisma/prisma-engine/pull/433

[tomhoule]

The relations syntax has changed with the beta release, and this crash is no longer possible. We are slowly introducing more validations on relations that should tell you which annotations are possible, and which not. Thanks for reporting this, and please open new issues if you find other problems :)