Adding association to existing tables in a migration `up` and `down`

Hello all, I am working with Ecto on a clone of Instagram to get better at using it. I purposefully created tables without the associations before hand to get more practice writing migrations and I’m running into a pickle. By day I am a FE/React developer so forgive me if my question is noob level dumb. I don’t have a lot of experience doing migrations or working with databases.

I have a table of posts and I want to add an association to a user on each post. I have a database full of seeds of non-connected users and posts. At this point I’ve written both the up and down. Where things are breaking down is updating the existing data in the posts table to reflect the addition of the user_id column. At first I thought it must be a problem with my seeds.exs, but as I got to thinking about this the problem is really that there are not the same number of users as there are posts. This isn’t just a noob problem this is a real problem I’m sure people face when migrating production databases.

How do you handle updating rows in a table with an new association if the two tables don’t have the same number of rows?

I can run mix ecto.migrate and I will get back an error message that states

(not_null_violation) null value in column “user_id” of relation “posts” violates not-null constraint

And I’ve come to the realization that’s because when I’ve exhausted the rows in the users table will evaluate to nil.

Are there any Ecto or database ninja’s out there that can help me better understand the problem and possible solutions that get ran against production code bases when you run into something similar?

Current Migration File

defmodule Ectogram.Repo.Migrations.AddUserPostAssociation do
  use Ecto.Migration
  import Ecto.Query
  alias Ectogram.Repo
  alias Ectogram.{Post,User}

  def up do
    alter table(:posts) do
      # I found if I set `null: false` here the `flush/0` call would fail so I apply the non-null at the end of the `up`.
      add_if_not_exists :user_id, :binary_id


    # User Table Rows: 5
    # Post Table Rows: 10

    # primary_keys are set to be of type :binary_id
    from(u in User, select: [:id])
    |> Repo.all()
    |> Enum.each(fn row ->
      Repo.insert_all(Post, [[user_id:]])

    alter table(:posts) do
      modify :user_id, references(:users, on_delete: :delete_all), null: false

    create_if_not_exists index(:posts, [:user_id])

  def down do
    alter table(:posts) do
      remove_if_exists :user_id, references(:users, on_delete: :delete_all)

    drop_if_exists index(:posts, [:user_id]), mode: :cascade

Current seeds.exs

alias Ectogram.{Comment, Post, Repo, User}
import Faker

for _ <- 1..5 do
  |> User.registration_changeset(%{
    avatar: Faker.Avatar.image_url(),
    # Ensures string does not exceed 'max' validation.
    bio: String.slice(Faker.Lorem.paragraph(), 0..240),
    # Ensures user is of age constraint.
    birth_date: Faker.Date.date_of_birth(18..30),
    # Ensures string does not contain any punctuation (i.e. Henry O'Leary).
    name: String.replace(Faker.Person.first_name() <> Faker.Person.last_name(), ~r/[[:punct:]]/, ""),
    # Ensures string meets 'format' validation.
    password: String.capitalize(Faker.Internet.slug(["foo", "bar", "baz"], ["_"]) <> "#{random_between(2,99)}"),
    # Ensures string meets 'format' validation.
    phone: String.replace(Faker.Phone.EnGb.number(), ~r/\s/, ""),
    # Ensures string does not exceed 'max' validation.
    username: "@#{String.slice(Faker.Internet.user_name(), 0..19)}"
  |> Repo.insert!()

for _ <- 1..10 do
  |> Post.changeset(%{
    # Ensures string does not exceed 'max' validation.
    caption: String.slice(Faker.Lorem.paragraph(), 0..230),
    lat: Faker.Address.latitude(),
    long: Faker.Address.longitude(),
  |> Repo.insert!()

for _ <- 1..50 do
  |> Comment.changeset(%{
    # Ensures string does not exceed 'max' validation.
    contents: String.slice(Faker.Lorem.paragraph(), 0..230),
  |> Repo.insert!()
1 Like

Hello! Welcome to the forum!

In my experience, creating unassociated tables and then adding associations later is not particularly common. Ideally, for something like Posts and the Users that own them, you would include the references in the migration that creates the column. I get that you want to learn more about Ecto, but I’m not sure if writing a modification migration with references is really any more instructive that writing a creation migration with the same. And in the real world, once you’ve inserted data into the database without association, how would you ever know which user owns which post?!

Anyway, it seems to me that the solution to your mismatched record count is simple: make the same number of Posts and Users in your seeds.exs. Or, since it is a pretty artificial case anyway, you could just loop through the Users twice and associate each with two different Post records.

Hope this helps! To get better with Ecto I would just keep adding entities and expand your learning app, then start looking into things like creating a record and a child association at the time (learn put_assoc and cast_assoc and the difference between them). Also, learning how to build complex queries with Ecto is very instructive; figure out how to load a record with associations – possibly multiple levels deep — without falling into N+1 queries. Learn how to build a Query module with a base_query and functions for adding constraints to the base so that you can compose various queries programmatically. That’s my 2 cents! Happy learning!

1 Like

Why are you inserting posts in this migration?
Don’t you just want to update all the Posts to have some valid user_id?

1 Like

@yukster thanks for the feedback. Yeah when I started writing the migration I was like “maybe this wasn’t the smartest idea”. As you brought up how would I ever know who owns the post to draw the association. I will revert/rollback and add the associations to the initial migrations.

Thank you for the points on things I should focus on learning with in Ecto to level up my skills I appreciate it and will revisit your reply as I build this out to work with these concepts too.

@slouchpie you are right that should be an update_all not insert_all thanks