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 row.id 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
end
flush()
# 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: row.id]])
end)
alter table(:posts) do
modify :user_id, references(:users, on_delete: :delete_all), null: false
end
create_if_not_exists index(:posts, [:user_id])
end
def down do
alter table(:posts) do
remove_if_exists :user_id, references(:users, on_delete: :delete_all)
end
drop_if_exists index(:posts, [:user_id]), mode: :cascade
end
end
Current seeds.exs
alias Ectogram.{Comment, Post, Repo, User}
import Faker
for _ <- 1..5 do
%User{}
|> 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),
email: Faker.Internet.email(),
# 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!()
end
for _ <- 1..10 do
%Post{}
|> 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!()
end
for _ <- 1..50 do
%Comment{}
|> Comment.changeset(%{
# Ensures string does not exceed 'max' validation.
contents: String.slice(Faker.Lorem.paragraph(), 0..230),
})
|> Repo.insert!()
end
