Can you show me how can i realiton between 2 table with knex on postgresql that tables are 'user' , 'project' , 'post'. I want to make a relation like in project table i have 'user_id' column so it should be connect with 'user' table with id. also post too.My migration file like this.
/**
* @param { import("knex").Knex } knex
* @returns { Promise<void> }
*/
exports.up = function (knex) {
return knex.schema.createTable('user', (table) => {
table.increments().notNullable();
table.string('created_at').notNullable();
table.string('updated_at');
table.string('first_name').notNullable();
table.string('last_name').notNullable();
table.integer('age').notNullable();
table.string('email', 100);
table.text('user_image').notNullable();
table.jsonb('social_links'); //tek bir obje olcak
table.text('introduction');
table.text('description');
table.jsonb('medias'); //şüpheliyim
table.specificType('marked_projects', 'int[]');
table.specificType('marked_blogs', 'int[]');
table.specificType('experiences', 'jsonb[]');
table.specificType('education', 'jsonb[]'); //objeler arrayi olcak
table.specificType('skills', 'jsonb[]');
table.string('sign_mail');
table.string('password');
})
.createTable('project', (table) => {
table.increments().notNullable();
table.integer('user_id').notNullable();
table.string('project_name').notNullable();
table.string('project_type');
table.string('project_title').notNullable();
table.string('project_intro').notNullable();
table.string('intro_image').notNullable();
table.specificType('members', 'text[]');
table.jsonb('medias'); //kullanmıycağız şimdilik ama koyuyorum değişeiblir diye
table.specificType('paragraphs', 'jsonb[]');
table.specificType('links', 'text[]');
table.foreign('user_id').references('user.id')
.onUpdate('CASCADE')
.onDelete('CASCADE');
})
.createTable('post', (table) => {
table.increments().notNullable();
table.integer('user_id').notNullable();
table.string('post_name').notNullable();
table.string('post_type');
table.string('post_title').notNullable();
table.string('post_intro').notNullable();
table.string('intro_image').notNullable();
table.jsonb('medias'); //kullanmıycağız şimdilik ama koyuyorum değişeiblir diye
table.specificType('paragraphs', 'jsonb[]');
table.specificType('links', 'text[]');
table.foreign('user_id').references('user.id')
.onUpdate('CASCADE')
.onDelete('CASCADE');
})
};
/**
* @param { import("knex").Knex } knex
* @returns { Promise<void> }
*/
exports.down = function (knex) {
return knex.schema.dropTableIfExists('post')
.dropTableIfExists('project')
.dropTableIfExists('user');
};
Also i want to this connection: Tables:| user | project | post |
so in my project table i have this column: 'user_id' , if my user_id: 2 i need to reach user that have id 2. This database should be relational database. in post table i have 'user_id' column and its same too.
i want to reach which person did create a post and project.
if my migration is true why i cannot run my seeds.
my seed file like this:
/**
* @param { import("knex").Knex } knex
* @returns { Promise<void> }
*/
exports.seed = async function (knex) {
// Deletes ALL existing entries
await knex('user').del()
await knex('user').insert([
{
id: 1,
created_at: "02/04/2021",
updated_at: "02/04/2022",
first_name: "Betül",
last_name: "Özkan",
age: 23,
email: "[email protected]",
user_image: "https://t3.ftcdn.net/jpg/03/46/83/96/360_F_346839683_6nAPzbhpSkIpb8pmAwufkC7c5eD7wYws.jpg",
social_links: {
linkedn: "https://www.linkedin.com/in/%C3%B6mer-faruk-demirsoy-290642196/",
behance: "https://www.behance.net/betul-0zkan",
medium: "https://medium.com/@betul-0zkan",
instagram: "",
github: "https://github.com/OFD16",
},
introduction: "I am a 20-year-old, self-taught freelance UI/UX designer based in Turkey. My passion is to using design to inspire and impact others, so I make it a point to constantly improve my skills and write my learnings in both English and Turkish.",
description: "Lorem ipsum dolor sit amet consectetur. In volutpat sed quis tortor magna libero mi. At aliquam nec consectetur sem eget nunc aliquam. In adipiscing sit tristique nunc sodales. Id nisi libero massa mi senectus quis. Erat varius tortor lobortis et nunc laoreet cras. Lorem ipsum dolor sit amet consectetur. In volutpat sed quis tortor magna libero mi. At aliquam nec consectetur sem eget nunc aliquam. In adipiscing sit tristique nunc sodales. Id nisi libero massa mi senectus quis. Erat varius tortor lobortis et nunc laoreet cras. Lorem ipsum dolor sit amet consectetur. In volutpat sed quis tortor magna libero mi. At aliquam nec consectetur sem eget nunc aliquam. In adipiscing sit tristique nunc sodales. Id nisi libero massa mi senectus quis. Erat varius tortor lobortis et nunc laoreet cras. ",
medias: {
videos: ["https://www.youtube.com/watch?v=lSIWNfNzJ18", "https://www.youtube.com/watch?v=G1GgjXbc6VM"],
images: ["https://t3.ftcdn.net/jpg/03/46/83/96/360_F_346839683_6nAPzbhpSkIpb8pmAwufkC7c5eD7wYws.jpg"],
},
marked_projects: [2, 7],
marked_blogs: [],
experiences: [
{ started_time: "01/01/2023", finished_time: "01/03/2023", company_name: "Figma", job_name: "UI/UX designer", introduction: "I am a 20-year-old, self-taught freelance UI/UX designer based in Turkey. My passion is to using design to inspire and impact others, so I make it a point to constantly improve my skills and write my learnings in both English and Turkish.", image: "https://upload.wikimedia.org/wikipedia/commons/thumb/9/95/Instagram_logo_2022.svg/1000px-Instagram_logo_2022.svg.png" },
{ started_time: "01/01/2023", finished_time: "", company_name: "google", job_name: "coder", introduction: "I am a 20-year-old, self-taught freelance UI/UX designer based in Turkey. My passion is to using design to inspire and impact others, so I make it a point to constantly improve my skills and write my learnings in both English and Turkish.", image: "https://upload.wikimedia.org/wikipedia/commons/thumb/9/95/Instagram_logo_2022.svg/1000px-Instagram_logo_2022.svg.png" }
],
education: [
{ started_time: "01/01/2015", finished_time: "01/03/2020", school_name: "EAİHL", degree: "Lise", image: "https://upload.wikimedia.org/wikipedia/commons/thumb/9/95/Instagram_logo_2022.svg/1000px-Instagram_logo_2022.svg.png" },
{ started_time: "01/01/2020", finished_time: "01/03/2026", school_name: "AGÜ", degree: "lisans", image: "https://upload.wikimedia.org/wikipedia/commons/thumb/9/95/Instagram_logo_2022.svg/1000px-Instagram_logo_2022.svg.png" },
],
skills: [
{ skill_name: "java", experience_time: "6 ay", description: "Lorem ipsum dolor sit amet consectetur. In volutpat sed quis tortor magna libero mi. At aliquam nec consectetur sem eget nunc aliquam. In adipiscing sit tristique nunc sodales.", image: "https://upload.wikimedia.org/wikipedia/en/thumb/3/30/Java_programming_language_logo.svg/1200px-Java_programming_language_logo.svg.png" },
{ skill_name: "c++", experience_time: "1 ay", description: "Lorem ipsum dolor sit amet consectetur. In volutpat sed quis tortor magna libero mi. At aliquam nec consectetur sem eget nunc aliquam. In adipiscing sit tristique nunc sodales.", image: "https://upload.wikimedia.org/wikipedia/commons/thumb/1/18/ISO_C%2B%2B_Logo.svg/1822px-ISO_C%2B%2B_Logo.svg.png" },
],
sign_mail: "[email protected]",
password: "123456"
},
]);
await knex('project').del()
await knex('project').insert([
{
id: 1,
user_id: 0,
project_name: "proje adı",
project_type: "proje tipi",
project_title: "proje başlıpı",
intro_image: "https://t3.ftcdn.net/jpg/03/46/83/96/360_F_346839683_6nAPzbhpSkIpb8pmAwufkC7c5eD7wYws.jpg",
project_intro: "I am a 20-year-old, self-taught freelance UI/UX designer based in Turkey. My passion is to using design to inspire and impact others, so I make it a point to constantly improve my skills and write my learnings in both English and Turkish.",
paragraphs: [
{
first_video: "",
first_image: "",
left_image: "",
paragpaph: "Lorem ipsum dolor sit amet consectetur. In volutpat sed quis tortor magna libero mi. At aliquam nec consectetur sem eget nunc aliquam. In adipiscing sit tristique nunc sodales. Id nisi libero massa mi senectus quis. Erat varius tortor lobortis et nunc laoreet cras. Lorem ipsum dolor sit amet consectetur. In volutpat sed quis tortor magna libero mi. At aliquam nec consectetur sem eget nunc aliquam. In adipiscing sit tristique nunc sodales. Id nisi libero massa mi senectus quis. Erat varius tortor lobortis et nunc laoreet cras. Lorem ipsum dolor sit amet consectetur. In volutpat sed quis tortor magna libero mi. At aliquam nec consectetur sem eget nunc aliquam. In adipiscing sit tristique nunc sodales. Id nisi libero massa mi senectus quis. Erat varius tortor lobortis et nunc laoreet cras. ",
right_image: "",
last_image: "",
last_video: "",
},
{
first_video: "",
first_image: "",
left_image: "",
paragpaph: "Lorem ipsum dolor sit amet consectetur. In volutpat sed quis tortor magna libero mi. At aliquam nec consectetur sem eget nunc aliquam. In adipiscing sit tristique nunc sodales. Id nisi libero massa mi senectus quis. Erat varius tortor lobortis et nunc laoreet cras. Lorem ipsum dolor sit amet consectetur. In volutpat sed quis tortor magna libero mi. At aliquam nec consectetur sem eget nunc aliquam. In adipiscing sit tristique nunc sodales. Id nisi libero massa mi senectus quis. Erat varius tortor lobortis et nunc laoreet cras. Lorem ipsum dolor sit amet consectetur. In volutpat sed quis tortor magna libero mi. At aliquam nec consectetur sem eget nunc aliquam. In adipiscing sit tristique nunc sodales. Id nisi libero massa mi senectus quis. Erat varius tortor lobortis et nunc laoreet cras. ",
right_image: "",
last_image: "",
last_video: "",
}
],
medias: {
videos: ["https://www.youtube.com/watch?v=lSIWNfNzJ18", "https://www.youtube.com/watch?v=G1GgjXbc6VM"],
images: ["https://t3.ftcdn.net/jpg/03/46/83/96/360_F_346839683_6nAPzbhpSkIpb8pmAwufkC7c5eD7wYws.jpg"],
},
members: ["ali", "ayşe"],
links: ["https://www.twitch.tv/", "https://www.twitch.tv/elraenn"]
},
{
id: 1,
project_name: "proje adı 1",
project_type: "proje tipi 1",
project_title: "proje başlıpı 1",
intro_image: "https://t3.ftcdn.net/jpg/03/46/83/96/360_F_346839683_6nAPzbhpSkIpb8pmAwufkC7c5eD7wYws.jpg",
project_intro: "1 I am a 20-year-old, self-taught freelance UI/UX designer based in Turkey. My passion is to using design to inspire and impact others, so I make it a point to constantly improve my skills and write my learnings in both English and Turkish.",
paragraphs: [
{
first_video: "",
first_image: "",
left_image: "",
paragpaph: "1 Lorem ipsum dolor sit amet consectetur. In volutpat sed quis tortor magna libero mi. At aliquam nec consectetur sem eget nunc aliquam. In adipiscing sit tristique nunc sodales. Id nisi libero massa mi senectus quis. Erat varius tortor lobortis et nunc laoreet cras. Lorem ipsum dolor sit amet consectetur. In volutpat sed quis tortor magna libero mi. At aliquam nec consectetur sem eget nunc aliquam. In adipiscing sit tristique nunc sodales. Id nisi libero massa mi senectus quis. Erat varius tortor lobortis et nunc laoreet cras. Lorem ipsum dolor sit amet consectetur. In volutpat sed quis tortor magna libero mi. At aliquam nec consectetur sem eget nunc aliquam. In adipiscing sit tristique nunc sodales. Id nisi libero massa mi senectus quis. Erat varius tortor lobortis et nunc laoreet cras. ",
right_image: "",
last_image: "",
last_video: "",
},
{
first_video: "",
first_image: "",
left_image: "",
paragpaph: "1 Lorem ipsum dolor sit amet consectetur. In volutpat sed quis tortor magna libero mi. At aliquam nec consectetur sem eget nunc aliquam. In adipiscing sit tristique nunc sodales. Id nisi libero massa mi senectus quis. Erat varius tortor lobortis et nunc laoreet cras. Lorem ipsum dolor sit amet consectetur. In volutpat sed quis tortor magna libero mi. At aliquam nec consectetur sem eget nunc aliquam. In adipiscing sit tristique nunc sodales. Id nisi libero massa mi senectus quis. Erat varius tortor lobortis et nunc laoreet cras. Lorem ipsum dolor sit amet consectetur. In volutpat sed quis tortor magna libero mi. At aliquam nec consectetur sem eget nunc aliquam. In adipiscing sit tristique nunc sodales. Id nisi libero massa mi senectus quis. Erat varius tortor lobortis et nunc laoreet cras. ",
right_image: "",
last_image: "",
last_video: "",
}
],
medias: {
videos: ["https://www.youtube.com/watch?v=lSIWNfNzJ18", "https://www.youtube.com/watch?v=G1GgjXbc6VM"],
images: ["https://t3.ftcdn.net/jpg/03/46/83/96/360_F_346839683_6nAPzbhpSkIpb8pmAwufkC7c5eD7wYws.jpg"],
},
members: ["1ali", "a1yşe"],
links: ["https://www.twitch.tv/", "https://www.twitch.tv/elraenn"]
}
]);
await knex('post').del()
await knex('post').insert([
{
id: 1,
user_id: 1,
post_name: "post adı 1",
post_type: "post tipi 1",
post_title: "post başlıpı 1",
intro_image: "https://t3.ftcdn.net/jpg/03/46/83/96/360_F_346839683_6nAPzbhpSkIpb8pmAwufkC7c5eD7wYws.jpg",
post_intro: "1 I am a 20-year-old, self-taught freelance UI/UX designer based in Turkey. My passion is to using design to inspire and impact others, so I make it a point to constantly improve my skills and write my learnings in both English and Turkish.",
paragraphs: [
{
first_video: "",
first_image: "",
left_image: "",
paragpaph: "1 Lorem ipsum dolor sit amet consectetur. In volutpat sed quis tortor magna libero mi. At aliquam nec consectetur sem eget nunc aliquam. In adipiscing sit tristique nunc sodales. Id nisi libero massa mi senectus quis. Erat varius tortor lobortis et nunc laoreet cras. Lorem ipsum dolor sit amet consectetur. In volutpat sed quis tortor magna libero mi. At aliquam nec consectetur sem eget nunc aliquam. In adipiscing sit tristique nunc sodales. Id nisi libero massa mi senectus quis. Erat varius tortor lobortis et nunc laoreet cras. Lorem ipsum dolor sit amet consectetur. In volutpat sed quis tortor magna libero mi. At aliquam nec consectetur sem eget nunc aliquam. In adipiscing sit tristique nunc sodales. Id nisi libero massa mi senectus quis. Erat varius tortor lobortis et nunc laoreet cras. ",
right_image: "",
last_image: "",
last_video: "",
},
{
first_video: "",
first_image: "",
left_image: "",
paragpaph: "1 Lorem ipsum dolor sit amet consectetur. In volutpat sed quis tortor magna libero mi. At aliquam nec consectetur sem eget nunc aliquam. In adipiscing sit tristique nunc sodales. Id nisi libero massa mi senectus quis. Erat varius tortor lobortis et nunc laoreet cras. Lorem ipsum dolor sit amet consectetur. In volutpat sed quis tortor magna libero mi. At aliquam nec consectetur sem eget nunc aliquam. In adipiscing sit tristique nunc sodales. Id nisi libero massa mi senectus quis. Erat varius tortor lobortis et nunc laoreet cras. Lorem ipsum dolor sit amet consectetur. In volutpat sed quis tortor magna libero mi. At aliquam nec consectetur sem eget nunc aliquam. In adipiscing sit tristique nunc sodales. Id nisi libero massa mi senectus quis. Erat varius tortor lobortis et nunc laoreet cras. ",
right_image: "",
last_image: "",
last_video: "",
}
],
medias: {
videos: ["https://www.youtube.com/watch?v=lSIWNfNzJ18", "https://www.youtube.com/watch?v=G1GgjXbc6VM"],
images: ["https://t3.ftcdn.net/jpg/03/46/83/96/360_F_346839683_6nAPzbhpSkIpb8pmAwufkC7c5eD7wYws.jpg"],
},
links: ["https://www.twitch.tv/", "https://www.twitch.tv/elraenn"]
}
]);
};
and this is the error:
Error while executing "C:\Users\CABBAR\Desktop\projelerim\portfolio-backend\src\data\seeds\user.js" seed: insert into "project" ("id", "intro_image", "links", "medias", "members", "paragraphs", "project_intro", "project_name", "project_title", "project_type", "user_id") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11), ($12,
$13, $14, $15, $16, $17, $18, $19, $20, $21, DEFAULT) - column "user_id" of relation "project" does not exist
Error: Error while executing "C:\Users\CABBAR\Desktop\projelerim\portfolio-backend\src\data\seeds\user.js" seed: insert into "project" ("id", "intro_image", "links",
"medias", "members", "paragraphs", "project_intro", "project_name", "project_title", "project_type", "user_id") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11), ($12, $13, $14, $15, $16, $17, $18, $19, $20, $21, DEFAULT) - column "user_id" of relation "project" does not exist
at Seeder._waterfallBatch (C:\Users\CABBAR\Desktop\projelerim\portfolio-backend\node_modules\knex\lib\migrations\seed\Seeder.js:118:23)
error: insert into "project" ("id", "intro_image", "links", "medias", "members", "paragraphs", "project_intro", "project_name", "project_title", "project_type", "user_id") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11), ($12, $13, $14, $15, $16, $17, $18, $19, $20, $21, DEFAULT) - column "user_id" of relation "project" does not exist
at Parser.parseErrorMessage (C:\Users\CABBAR\Desktop\projelerim\portfolio-backend\node_modules\pg-protocol\dist\parser.js:287:98)
at Parser.handlePacket (C:\Users\CABBAR\Desktop\projelerim\portfolio-backend\node_modules\pg-protocol\dist\parser.js:126:29)
at Parser.parse (C:\Users\CABBAR\Desktop\projelerim\portfolio-backend\node_modules\pg-protocol\dist\parser.js:39:38)
at Socket.<anonymous> (C:\Users\CABBAR\Desktop\projelerim\portfolio-backend\node_modules\pg-protocol\dist\index.js:11:42)
at Socket.emit (node:events:513:28)
at addChunk (node:internal/streams/readable:324:12)
at readableAddChunk (node:internal/streams/readable:297:9)
at Readable.push (node:internal/streams/readable:234:10)
at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
but i have this user in my database: i have user that have id : 1
Welcome to the StackOverflow community.
Update: The author updated the post later with new information. Not sure how much this answer is relevant for the current scenario, but I will keep it posted because other new learners may find it helpful.
However, your issue seems related to foreign key constraints not being created in the database.
I am also a student; here is a small one-to-many relationship using knex.js.
knex.schema.createTable('user', function(table) {
table.increments('id').primary();
table.string('name');
})
.createTable('project', function(table) {
table.increments('id').primary();
table.string('name');
table.integer('user_id').unsigned().references('user.id');
})
.createTable('post', function(table) {
table.increments('id').primary();
table.string('title');
table.integer('project_id').unsigned().references('project.id');
});
The 'user_id' column in the project table and the 'project_id' column in the post table are foreign keys referencing the 'id' column in the respective tables.
Hope you find it helpful, don't forget to share areas of improvement in my comment, thanks