postgresql schema structuring

Yes, a reactions table referring to the post, user, and their reaction is correct.

knex.schema.createTable('post_reactions', function(t) {
  t.integer('user').notNullable();
  t.foreign('user').references('id').inTable('users');
  t.integer('post').notNullable();
  t.foreign('post').references('id').inTable('posts');
  t.string('reaction').notNullable();

  t.unique(['user', 'post', 'reaction']);
}

The unique is so a user can only make a particular reaction once per post.

Getting the reactions to post 3 does not need a join.

knex('post_reactions')
  .select('user', 'reaction')
  .where('post', 3)

But maybe you want the user’s names and the post’s title.

knex('post_reactions')
  .join('users', 'users.id', '=', 'post_reactions.user')
  .join('posts', 'posts.id', '=', 'post_reactions.post')
  .select('posts.title', 'users.name', 'reaction')
  .where('post', 3)

I made it post_reactions both because they are reactions to a post (leaving room for reactions to something else), and because you may want to make a table for all possible reactions and join with that.

knex.schema.createTable('reactions', function(t) {
  t.increments();
  t.string('keyword').notNullable();
  t.unique('keyword');
}

knex.schema.createTable('post_reactions', function(t) {
  t.integer('user').notNullable();
  t.foreign('user').references('id').inTable('users');
  t.integer('post').notNullable();
  t.foreign('post').references('id').inTable('posts');
  t.integer('reaction').notNullable();
  t.foreign('reaction').references('id').inTable('reactions');

  t.unique(['user', 'post', 'reaction']);
}

Now we need an extra join if we want the reaction keyword in addition to the post’s title and the user’s name.

knex('post_reactions')
  .join('users', 'users.id', '=', 'post_reactions.user')
  .join('posts', 'posts.id', '=', 'post_reactions.post')
  .join('reactions', 'reactions.id', '=', 'post_reactions.reaction')
  .select('posts.title', 'users.name', 'reaction.keyword')
  .where('post', 3)

This has the advantage of ensuring every entry in post_reactions is a real reaction. Typos are not possible. It also means the keyword or other things about the reaction can change, but the reaction will still work.

Note that I avoided storing the reaction image itself in the database. You can do that, but it’s often better to store assets on disk and refer to the file. This allows the assets to be directly linked to, reduces load on the database, and lets people work with the assets without having to touch the database.