How to alter/modify the created Schema of the Postgres Database?

Phoenix Forum Questions / Help
,
1

I have followed this but can’t alter the schema of my Database. Following is the step I followed-
0- Run the mix ecto.gen.migration create_contracts. So got an error of existing migration file.
1- Deleted the **_migration_file which was stored in the repo/migrations
2- run the mix ecto.gen.migration create_contracts . So, a migration file is created.
3- run the mix ecto.migrate. Got an error- (Postgrex.Error) ERROR 42P07 (duplicate_table) relation "contracts" already exists.

Here is my schema-

schema "contracts" do
    field :Category, :string
    field :Cost, :decimal
    field :EndsOn, :date
    field :Vendor, :string

    timestamps()
  end

Firstly, I want to lowercase the column name and secondly, I want to change the type as well. Any response to alter/modify the schema of the database will be highly appreciated.

2

Whenever You get stuck like this… drop the database and restart from a clean state.

$ mix ecto.drop
$ mix ecto.create
$ mix ecto.migrate
2 Likes
3

I have 1 other schema too. Totally, 2 Schema(1 I want to kept and 1 I want to modify).
When I am running mix ecto.drop, it is showing error.

ERROR 55006 (object_in_use) database "learn_phoenix_dev" is being accessed by other users

There are 10 other sessions using the database.
4

You need to close applications accessing the db before dropping it.

1 Like
5

Nothing changes. See the screenshot
Two things to note-
–I have modified the **_migration_file manually when I was trying myself in starting(before posting the question here).
–The screenshot which I have shared, there the migration number on generated file(see terminal) is different from what I have opened in VS code.

6

Did You stop the server before trying to drop the database?

1 Like
7

Firstly, I don’t know to stop the server so when you said then I stop the local server by pressing ctrl + c x 2. After this, i hit the 3 commands you mention above.

8

Yes, this is what I mean… pressing ctrl-c twice should stop the server. But You need to close any other applications accessing the database, that could be a terminal with psql open, or any server instance.

2 Likes
9

Even, there is no access to database. No terminal with psql, no server instance, nothing.

10

Your screenshot tells the commands were succesful.

Is there something I miss?

1 Like
11

Your 3 commands didn’t show or points what to edit.

12

Before doing those commands, just edit your migration file to what You want, then edit your schema as needed.

So in your case, lowercase fields and change types (manually).

1 Like
13

Yup, it the schema and migration file seems good. But, other issue, column name is not globally change. See the screenshot

14

Ouch… You need to update context file as well. It should be your Vendor context.

PS : to search all files You need to convert, try this command.

$ find . -type f -exec grep -H EndsOn {} \;

PS2 : Typo is also your templates…

1 Like
15

How to do that?
One thing more, When I inspect struct of the schema. There was no change. This struct of schema was right now inserted. Check screenshot
See the 2nd last line of the screenshot. You will found those Uppercase Column Name.

16

The post You follow is from 2016, it is using the old phoenix version.

It is also using gen.html, which generates for You a lot of files.

You should use phx instead…

$ mix help | grep phx
mix local.phx          # Updates the Phoenix project generator locally
mix phx                # Prints Phoenix help information
mix phx.digest         # Digests and compresses static files
mix phx.digest.clean   # Removes old versions of static assets.
mix phx.gen.cert       # Generates a self-signed certificate for HTTPS testing
mix phx.gen.channel    # Generates a Phoenix channel
mix phx.gen.context    # Generates a context with functions around an Ecto schema
mix phx.gen.embedded   # Generates an embedded Ecto schema file
mix phx.gen.html       # Generates controller, views, and context for an HTML resource
mix phx.gen.json       # Generates controller, views, and context for a JSON resource
mix phx.gen.presence   # Generates a Presence tracker
mix phx.gen.schema     # Generates an Ecto schema and migration file
mix phx.gen.secret     # Generates a secret
mix phx.new            # Creates a new Phoenix v1.4.2 application
mix phx.new.ecto       # Creates a new Ecto project within an umbrella project
mix phx.new.web        # Creates a new Phoenix web project within an umbrella project
mix phx.routes         # Prints all routes
mix phx.server         # Starts applications and their servers

If I run the following command,

$ mix phx.gen.html Vendor Contract contracts name:string
* creating lib/koko_web/controllers/contract_controller.ex
* creating lib/koko_web/templates/contract/edit.html.eex
* creating lib/koko_web/templates/contract/form.html.eex
* creating lib/koko_web/templates/contract/index.html.eex
* creating lib/koko_web/templates/contract/new.html.eex
* creating lib/koko_web/templates/contract/show.html.eex
* creating lib/koko_web/views/contract_view.ex
* creating test/koko_web/controllers/contract_controller_test.exs
* creating lib/koko/vendor/contract.ex
* creating priv/repo/migrations/20190514112232_create_contracts.exs
* creating lib/koko/vendor.ex
* injecting lib/koko/vendor.ex
* creating test/koko/vendor_test.exs
* injecting test/koko/vendor_test.exs

Add the resource to your browser scope in lib/koko_web/router.ex:

    resources "/contracts", ContractController


Remember to update your repository by running migrations:

    $ mix ecto.migrate

You can see the list of newly created files.

My advice, delete those files and retype the command wthout typo :slight_smile:

1 Like
17

Just change the variable name at all place. It works no need to apply the last step you told. Thanks. Really Thanks