Need to change the current one and add more fields into my DB table in Postgres

Phoenix Forum Questions / Help
,
1

I have generated my context like:

$ mix phx.gen.context Cars Car cars modle:string age:integer

but I made a typo and I also need to add additional fields, one of them with default true boolean value.

So, I have 3 problems:

  1. correct modle into model
  2. add new price:integer
  3. add new sold:boolean but the default value should be true and not false

There are already many records in this table, so dumping it is not an option. It has to be updated somehow, preferably via mix.

Can anyone help with these problems?

2

You can use mix ecto.gen.migration — Ecto SQL v3.7.1 to generate a new migration file. Then inside this file you can create your migration using the helpers here Ecto.Migration — Ecto SQL v3.7.1. There are functions for adding and renaming columns.

3

Hmm, I don’t get it at all.

The first link you posted has very few explanations. And the second link has too much ;D.

Could you give an example how would the mix ecto.migration command loook like for renaming the modle into model name?

4

All mix ecto.gen.migration does is create an empty migration file. It is then up to you to fill it in. You want something like

defmodule MyRepo.Migrations.RenameModel do
  use Ecto.Migration

  def change do
    rename table("cars"), :modle, to: :model
  end
end

I will let you figure out adding the remaining fields, don’t want to just give you the answer :wink:

2 Likes
5

So, it’s not possible via mix in the shell/terminal?

I don’t want to have such migrations in my project with rename. Can I just manually change the name in the DB and then rename it in the first migration file? The same with making the default boolean true and not false?

By the way, how do you say that in Ecto?:

add :sold, :boolean, default: true, null: false

Then when I run ecto.migrate, will it overwrite the existing false value of sold in my cars/car.ex ecto schema file?

6

It’s not possible to do a migration just from the command line, no. I’m not sure you understand the point of migration files. The intent is to keep a record of all of the changes that have been made to your database as code, so that you or any other developers can rebuild the database at any time. If you have deployed your migrations to a server and run them against your production database, migrations should be considered immutable after that point.

Migrations also do not update your schema file automatically, because it’s possible that the database default is different from the schema default. I’m not sure why you’d want to do this in practice, but it is possible.

7

I know the reason for having migration files. Migration files are like bash batch files but for the DB. you can do everything manually, but it’s better to keep everything in an automated file/script - with additional benefits like rollbacks etc.

But instead of changing things manually at at least two locations:

  1. migration file
  2. schema file
    *3. perhaps the test file too?

You could do that from the command line during the 1st initialization of those 5 or how many files that phx.gen.context creates. and something like:

sold:boolean:true

looks super neat to me, or, what do you think?

Instead, one have to do just:

sold:boolean

and then manually add

the info that the default value is true to 2 files (1st for migration and 2nd for the ecto schema file.

Wouldn’t it be nice to do it the first way and not manually alter multiple files after the shell mix command?

8

The two places aren’t the same, and aren’t always changed together.

For instance, in versions of PostgreSQL prior to 11 adding a column with a default requires a full table rewrite which prevents all writes to the table until it is done.

On a production system with tens of millions of rows, that could mean minutes to hours of downtime.

One other gotcha: how would that hypothetical feature distinguish “a string column named foo with a unique index” from "a string column named foo with a default value of unique"? Both would be spelled foo:string:unique :thinking:

9

I am talking about generating files and helping the programmer. I am not talking about executing ecto.migrate command.

One other gotcha: how would that hypothetical feature distinguish “a string column named foo with a unique index” from “a string column named foo with a default value of unique”? Both would be spelled foo:string:unique

This is about finding the right syntax. I am not saying mine version is_supercool:boolean:true is the best syntax we can come with.

It all comes down to writing less on your keyboard.

Or I am missing something and you can set this in one go? From what I gather, you have to visit the ecto schema file and the migration file and type on your keyboard the same thing twice.

Wouldn’t it be nice to be able to not do that after you generate the context? At least for this trivial thing of setting the column to a default value?

10

Generators are not meant to replace coding. They are meant as a starting point for your code, mostly useful for newcomers. For more serious development, you’re expected to tweak and review the generated code. This means that generators are most likely to have a limited set of features.

1 Like