updating the database with single migration?

user_18121540 0
Viewed 163

I have a table that has "projects" that have this field workspace which takes an array of strings. So I have converted this to store a single character

 create table("projects") do
  add(:name, :string, null: false)
  add(:title, :string, null: false)
  add(:workspaces, {:array, :string})

  timestamps()
end

I have written this migration to store a single string

alter table(:projects) do
      modify(:workspace, :string)

I want to run this migration but also I want to update all the data which is stored in the database. I need to update all the workspace data to store a single string.

Suppose if workspace has this value {value1, value2}. It will look for value1 and if that is found it will store the value1 otherwise it will store value2

This is how {value1, value2} it is stored in the database table. So when we load it in elixir it looks something like this ["value1", "value2"]

What can I do here?

ecto elixir
2 Answers

If I understood correctly, you want to change the workspaces array column to a workspace string column that contains the first element of the array. If that is the case you can do something like this in a migration:

# Add the `workspace` column:
alter table(:projects) do
  add(:workspace, :string)
end

# Set the value of the new column:
execute("""
  UPDATE projects SET workspace = CASE
    WHEN 'value1' = ANY(workspaces) THEN 'value1'
    WHEN 'value2' = ANY(workspaces) THEN 'value2'
    ELSE 'default_value'
    END
  """)

# Remove the old column
alter table(:projects) do
  remove(:workspaces)
end
user_55570130

It looks like you're trying to do a schema-less database within your relational database. This is going to get hard pretty quickly. You might be better served using a schemaless database like couch or mongo.

However if you're using postgres, the jsonb or json column types would better serve this use case IMO. The data is a tad bit harder to query as the postgres operators are interesting, but once you wrap your head around it it's not too bad and the performance with proper indexes will be on par with schemaless. Also you get the added benefit of not having to write serializing/deserializing to your database layer.

user_7933300
Related