MySQL datamodel not matching prisma2 introspect generated schema

[hatchli]

I am using an RDS MySQL database. The only difference in the SQL schema from that provided in the "Get Started" doc is removal of WITH TIME ZONE on created_at for the posts table.

CREATE TABLE users (
	user_id SERIAL PRIMARY KEY NOT NULL,
	name VARCHAR(256),
	email VARCHAR(256) UNIQUE NOT NULL
);

CREATE TABLE posts (
	post_id SERIAL PRIMARY KEY NOT NULL,
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	title VARCHAR(256) NOT NULL,
	content TEXT,
	author_id INTEGER REFERENCES users(user_id) 
);

CREATE TABLE profiles (
	profile_id SERIAL PRIMARY KEY NOT NULL,
	bio TEXT,
	user_id INTEGER NOT NULL REFERENCES users(user_id) 
);

When I run introspect on prisma2, I get the following data model schema in schema.prisma:

model posts {
  author_id  Int?
  content    String?
  created_at DateTime?
  post_id    Int       @id
  title      String
}

model profiles {
  bio        String?
  profile_id Int     @id
  user_id    Int
}

model users {
  email   String  @unique
  name    String?
  user_id Int     @id
}

This is quite different from the schema that is supposed to be generated, per the "Get Starting" docs. Primarily, the relations seem to be missing entirely:

model posts {
  author_id  users?
  content    String?
  created_at DateTime?
  post_id    Int       @id
  title      String
}

model profiles {
  bio        String?
  profile_id Int     @id
  user_id    users
}

model users {
  email      String     @unique
  name       String?
  user_id    Int        @id
  postses    posts[]
  profileses profiles[]
}

What exactly am I doing wrong here? I suppose this might be a MySQL specific issue?

[janpio]

I assume you refer to https://github.com/prisma/prisma2/blob/master/docs/getting-started.md#2-introspect-your-database-to-generate-a-data-model?

I think the documentation might just be missing the foreign key definitions - which leads to the generated schema to also miss all relations unfortunately.

We should fix this as soon as possible.

[hatchli]

That's correct @janpio

I figured it out in the meanwhile after referencing the MySQL docs for a while.
The following SQL schema:

CREATE TABLE users (
	user_id SERIAL PRIMARY KEY,
	name VARCHAR(256),
	email VARCHAR(256) UNIQUE NOT NULL
);

CREATE TABLE posts (
	post_id SERIAL PRIMARY KEY,
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	title VARCHAR(256) NOT NULL,
	content TEXT,
	author_id BIGINT UNSIGNED NOT NULL,
    INDEX author_index (author_id),
	FOREIGN KEY (author_id) REFERENCES users(user_id)
    ON DELETE CASCADE
);

CREATE TABLE profiles (
	profile_id SERIAL PRIMARY KEY,
	bio TEXT,
	user_id BIGINT UNSIGNED NOT NULL,
    INDEX user_index (user_id),
	FOREIGN KEY (user_id) REFERENCES users(user_id)
    ON DELETE CASCADE
);

Creates the following datamodel schema in prisma.schema:

model posts {
  content    String?
  created_at DateTime?
  post_id    Int       @id
  title      String
  author_id  users
}

model profiles {
  bio        String?
  profile_id Int     @id
  user_id    users
}

model users {
  email      String     @unique
  name       String?
  user_id    Int        @id
  postses    posts[]
  profileses profiles[]
}

Which from what I can tell restores the relationships, with the only difference being that an author_id is not null, versus the docs allowing for a null author_id.

[janpio]

Thanks! Let's leave this issue open though until the problem is really fixed in the docs. We can now pretty much just take what you discovered.

[nikolasburk]

Thanks for all the info @hatchli, that's super helpful! I've adjusted the SQL schema in the docs to contain an explicit declaration of the FOREIGN KEY constraint:

CREATE TABLE users (
  user_id SERIAL PRIMARY KEY NOT NULL,
  name VARCHAR(256),
  email VARCHAR(256) UNIQUE NOT NULL
);

CREATE TABLE posts (
  post_id SERIAL PRIMARY KEY NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  title VARCHAR(256) NOT NULL,
  content TEXT,
  author_id INTEGER,
  FOREIGN KEY (author_id) REFERENCES users(user_id) 
);

CREATE TABLE profiles (
  profile_id SERIAL PRIMARY KEY NOT NULL,
  bio TEXT,
  user_id INTEGER NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(user_id)
);

This issue occurred because there seem to be differences in how foreign keys are defined in MySQL and Postgres. With Postgres, you can actually omit the FOREIGN KEY declaration, that's why the initial version also worked. For a bit more context, check out this question on Stackoverflow.