How to create relations between tables with knex on postgresql database

Question

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: "omerdmrsy@gmail.com",
      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: "omer@gmail.com",
      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

Answer 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');
    });

1. create a user table with an id and name column
2. create a project table with 'id,' 'name,' and 'user_id' columns
3. create a post table with 'id,' 'title,' and 'project_id' columns

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