Do you have to run mix ecto.migrate when working with an existing db table?

Hello,

Is it necessary to run mix ecto.migrate? I created my schema and other files using mix phx.gen.json . I am working with an existing db table and the schema I created is based off that db table.

Thanks!

Yes, if you want to apply changes from the migrations to the DB then you need to run mix ecto.migrate.

1 Like

I am working with an existing db table and the schema

If table and db schema somehow existed before then you just need to appropriate Ecto.Schema structs to map your existing db table/schema.

Thanks for replying @hauleth .

However, I am trying not to change the existing table in the database. I just wanted to create a schema, view, controller, context module file for the API (file containing Repo) which are created via phx.gen.json

@RudManusachi
Does this mean I cannot utilize phx.gen.json, I will need to create everything from scratch?

https://hexdocs.pm/phoenix/Mix.Tasks.Phx.Gen.Json.html

well, if you create and modify your DB somehow differently than with Ecto migrations - I would say feel free to delete pregenerated migrations. But make sure your db schema is consistent across environments.

As for

Does this mean I cannot utilize phx.gen.json

On the contrary, those generators are just for convenience… like most probably you would have added those generated modules and functions manually. But since you have custom setup, feel free to adjust for your needs. For example, I usually remove scaffolded functions from context module. Phoenix does not limit, but helps us to build.

1 Like

Thank you for your explanation RudManusachi. When I did the phx.gen.json, I made sure the schema matches the database so the functions in the context module that queries from the database should work. For some reason I keep on getting the error:
[debug] "** (Ecto.NoResultsError) expected at least one result but got none in query:

This is when trying to get 1 record and the function in the context module that has Repo.get!(Item, id) is called.

You can delete the migrations as @RudManusachi suggests.

If you created the wrong schema you can modify it by hand to match your existing database table.

Furthermore, your schema fields do not have to match exactly the table columns. You can have in your schema only the required fields for your app.

Hello hectorsq,

Yes, I deleted the migrations and skipped that. I tried my best to match the schema with the database, I know I got the names right, but I am not 100% sure with the types. I posted my query, schema, and table structure. Please see (Ecto.NoResultsError) expected at least one result but got none in query:

And thank you for this information! Maybe I can remove the fields I suspect the data is not matching and test.

I want to fully migrate an application already done in PHP with an existing MariaDB: how do I create an Ecto.Schema from an existing Database?

To the best of my knowledge there is no automated way to create an Ecto Schema from existing database tables. The Ecto Schema definitions need to be manually created using the correct names from the database tables/columns/etc. I’m not using Ecto migrations, but am using Ecto… so I just end up coding the Schema definitions by hand.

So, just to be sure I get it right, you were building one by one every single table’s schema with every single relationship the DB already has, right ?

Of course.

Assuming such a reverse engineering tool existed (and a third party one may) my guess is, unless your database is pretty simple, the best you’d end up with a starting point that would need to be edited after generation.

For me, a reverse engineering tool makes less sense since I’m not working with a legacy database. My project is green field so I’m writing the database tables anyway (just not as Ecto migrations) so it’s a trivial step with a decent text editor to get to the Ecto Schema… at least to the point where a reverse engineering tool would get me.

I am definitely a beginner when it comes to Elixir and Phoenix since I have not yet grasped the real advantages of Ecto but definitely I dont like this missing tool

Our of curiosity, what is the corresponding tool in PHP? I’m interested to see how it works.

I have been developing with YII, a PHP framework , which has an integrated generator (named GII) which reads the DB and creates the corresponding model (relationships included) and no matter the changes you do in the DB table’s structure you can keep running that generator to update the corresponding model.

Such a tool, IMHO, would be a strong reason for Elixir adoption in situations like mine where a quite big DB has to be “recreated” as a bunch of models.

I dont have enough knowledge (and experience) in Elixir/Phoenix to dive into building one but would love to

I imagine someone could come up with such tooling for Ecto which could get you to at least that starting point for further editing.

Note that even the “Gii” tool you mention comes with these comments (Getting Started: Generating Code with Gii | The Definitive Guide to Yii 2.0 | Yii PHP Framework):

Note: The code generated by Gii is only a template that has to be adjusted to your needs. It is there to help you create new things quickly but it is not something that creates ready to use code. We often see people using the models generated by Gii without change and just extend them to adjust some parts of it. This is not how it is meant to be used. Code generated by Gii may be incomplete or incorrect and has to be changed to fit your needs before you can use it.

and

Tip: When using the Model Generator to update models after database change, you can copy the code from Gii preview and merge the changes with your own code. You can use IDE features like PHPStorms compare with clipboard, Aptana Studio or Eclipse based editor also allows compare with clipboard by using AnyEdit tools plugin for this, which allows you to merge in relevant changes and leave out others that may revert your own code.

Which is about the most I’d expect from such tooling given the general complexity of the problem.

I certainly don’t think having such a tool in Elixir for Ecto would be a bad thing at all and might be something I might hack together if I had a large legacy database to work with. I doubt I take that to the next step and make it general purpose though.

1 Like

Relationships would have to be edited for sure, because Ecto distinguishes between has_one and has_many. You wouldn’t be able to get that from the table definitions.

Maybe the output of such a tool could generate something like has_[one/many] because then it wouldn’t compile and would force the user to pick.

There’s no schema generation for ecto, because there’s no strong relationship between schemas and tables in ecto to begin with. There can be many schemas querying the same table and there can be schemas used with multiple tables. I can see the usefulness of generating code, but I don’t think it’s something that ecto itself should do.

1 Like

That takes care of a certain case, but don’t forget we need to get belongs_to, too. What to do for join tables? There’s a fair amount of complexity with just that topic… polymorphic associations using has_many :through (Polymorphic associations with many to many — Ecto v3.9.1) has its use cases, but would be a challenge to realize from a reverse engineering tool. On that same topic one technique dealing with avoiding multiple join tables that all are referenced from a common table (which I generally discourage) is to use two columns, one for the foreign record ID (e.g. record_id) and another describing which table that record lives in (e.g. record_table_name). Such a tool couldn’t really help with that since the relationship wouldn’t be explicit. Also, the common table in that scenario would look like it has a belongs_to relationship to all the other tables, but I’m not sure you could represent that with Ecto.

Insofar as these mapping tools (Ecto, etc) and the database have a non-overlapping Venn Diagram of functionality, a reverse engineering tool may save you some typing, but will end up very possibly giving you something substandard on its own.

@LostKobrakai also makes an excellent point. While Ecto Schemas often times mirror tables (assuming they represent database data at all), it’s not necessary and I could see these cases for using Ecto Schemas with different abstractions represented.

That is exactly how the scaffolding works for every other language, nothing new

I am not expecting Phoenix to create the whole thing for me but the DB is pretty important and very useful instead of staying hours rewriting it into “ecto syntax”

There is already a tool like this but seems to me abandoned