Right approach for creating a migration to alter a table by adding a not-null column #2070
Replies: 7 comments 1 reply
-
|
A lot of this stems from thinking there is a lot more magic going in Sequel. Sequel is mostly mapping SQL concepts to Ruby, as directly as possible without breaking either idiom. If you see an The create column, set not null, set default are treated as three operations. You can create a column as not-null-with-a-default, ie This is the source of some of your issues above: You are adding a column, then altering it twice. You should set the default and not-null as part of the create, ie
What's the default value of a user's required email address, or the default value of a
Jeremy would need to confirm but this may be a bug/edge case. You can/should use
This is not a multiple migrations approach. Many migrations require multiple steps. For example:
Similarly, in the case of a state machine 'status' column:
|
Beta Was this translation helpful? Give feedback.
-
|
You should set the default and NOT NULL when creating the column (you can also create an index at the same time): alter_table(:my_table_name) do
add_foreign_key :my_new_column, :other_table_name, :default=>0, :null=>false, :index=>true
end |
Beta Was this translation helpful? Give feedback.
-
|
@rgalanakis, @jeremyevans Thanks a lot for your prompt response and esp Thanks to @rgalanakis for your elaborate reply and clarifications. Those clears up a lot of things for me. Considering the elaborate details shared I think I was wrong somehow in my understanding about the If you will look at the documentation for Frankly speaking after this discussion what I feel is the documentation lacks adequate/clear examples for the usage of various options available for the methods available inside the Hoping that this feedback will be considered on a positive note. Thanks. |
Beta Was this translation helpful? Give feedback.
-
|
To preface this, I'm not a maintainer, but I've used Sequel across a half-dozen companies and dozen+ projects since 2014, when I started Ruby.
That is probably true, even without looking at the docs to verify. For better and worse, Sequel conceptually leans pretty heavily on existing SQL knowledge as a baseline. It extensively documents things like its associations, which map foreign keys to the model system; but it doesn't, for example, automatically add indices to foreign keys, or other things many ORMs do because they think it's helpful. It is an 'SQL toolkit' (by far the best I've ever used across any language) which doesn't try to hide the database. Conceptually then for things like My opinion is this makes Sequel have a steeper initial learning curve, because it doesn't defacto need to hold your hand as closely, since the concepts aren't out of the Jeremy's head (who is not the original author) but are mostly found in SQL. But the learning curve is much more gentle after that, because it maps closely to SQL/your database's capabilities, and it's just a question of expressing a database idea in Sequel, rather than having to redesign your database idea so it works in an ORM. I also can't verify this but I think the community skews to be more experienced, probably because we appreciate Sequel being such a good partner with the database, rather than competing with it. All that said, I'm sure Jeremy would appreciate doc contributions to make them more friendly to beginners! |
Beta Was this translation helpful? Give feedback.
-
There are definitely reasons you would want to issue the commands separately. On some databases (older PostgreSQL IIRC), adding a column with a default value can lock the entire table while the whole table is rewritten, whereas adding a column without a default value does not. As @rgalanakis mentioned, Sequel's documentation assumes you know SQL fairly well in many cases. This is great if do know SQL fairly well, but definitely suboptimal if you don't. There is no difference between using
I'm open to additional documentation examples. However, I don't think it is feasible to document the recommended way to handle every possible case, especially since recommended handling is situation-dependent in many cases.
Definitely. Thank you for providing feedback. If you have ideas for specific documentation improvements, submission of documentation pull requests is welcomed. |
Beta Was this translation helpful? Give feedback.
-
|
@jeremyevans to following
I would like to clarify here that when I referred to "documentation" in my comments I was referring to following doc https://github.com/jeremyevans/sequel/blob/master/doc/schema_modification.rdoc in repo but based on your reply you referred to the docs at http://sequel.jeremyevans.net/rdoc/classes/Sequel/Schema/AlterTableGenerator.html#method-i-add_foreign_key and latter does reference the supported options. So somewhere the discussion was not going on the same page. From the day I have started working with Sequel migrations I am following the https://github.com/jeremyevans/sequel/blob/master/doc/schema_modification.rdoc because searching through Google for sequel migrations I have always got that document in top-search results (please refer attached screenshot)
and if you will refer to the documentation at https://github.com/jeremyevans/sequel/blob/master/doc/schema_modification.rdoc#label-add_foreign_key you should be able to understand the source of my confusion. So I would recommend that anybody ending up reading this discussion should refer to the docs pointed by @jeremyevans instead of what I was referring to. @jeremyevans my apologies if this source of confusion caused any inconvenience but still multiple perspectives and facts were shared so definitely the discussion cannot be considered a waste. Thanks. |
Beta Was this translation helpful? Give feedback.
-
|
@jeremyevans Thanks for your elaborate reply.
That’s fair. But I would like to share here my subjective feelings regarding the need for documentation to be clear/comprehensive in its approach irrespective of that user be experienced or novice with Sequel. Below are my thoughts: For developers, whose mindset is habitual to using ORMs for working with databases, the assumption
can turn out contrary. And a reason behind my saying so is following: Majority of the popular frameworks facilitating working with databases through ORMs is in vogue and from past so many years working in the industry I constantly feel that ORMs have contributed towards detaching developers from working with raw SQL. And to extend my feeling to the extreme I would say that if a survey be done with majority of the programmers initiating their career into programming through frameworks like Rails and if they are asked how do you work with database the answer should be ActiveRecord ORM. And no wonder that who purely relies on working with ORMs contributes towards more performance issues from DB-front in the application because majority of them would not be even aware about the queries that should be firing at DB-level. From our discussion in previous comments what I have understood is that Sequel is not inclined towards being an ORM in literal terms but a high-level interface towards working with DB and still remaining in touch with the fundamentals of SQL and facilitating the performance gains of working with raw SQL. For a developer switching from his existing language domain (like Java, Python etc) to Ruby language domain, because of the common psychology of people following the trends, Rails is the framework on which such developers end-up. And Rails by default supports ActiveRecord ORM. So if such developers who have worked with Rails for quite sometime and for any reason they need to switch to or explore another ORM, then the preferred option is found to be Sequel and recently a still high-level interface is being developed by rom-rb community with the focus being resolving the performance issues involved with ActiveRecord. Now for working effectively with with rom-rb Sequel’s knowledge is an expectation. Now if such a developer (and that includes me as well) ends up on Sequel’s documentation (with a novice-mindset to Sequel), obviously they would look at it through a biased mindset wherein they should be assuming that they are switching to Sequel in lieu of another ORM, like ActiveRecord they are familiar with, and hence their learning curve should be fairly smooth but the Sequel’s community will only know that that assumption is wrong. And the more experienced such developer would be and who has became fairly familiar with fundamentals of working with Sequel, the more he would be targeting directly the API documentation for his advanced needs. But since the API documentation is not comprehensive or directly indicative at various places (for e.g the Please note that what all I have written in this comment or in previous comments has no intention of getting into any sort of debates and argumentation. I have simply and frankly shared my feelings about the need for improving Sequel’s documentation so that a mere skimmer also can become aware about what all a particular API has to offer. Lastly I would like to say that: It’s our common experience that without comprehensive and clear manuals we cannot take full advantage of what a great utility thing or a mighty gadget has to offer and because of lack of proper usage instructions be whatever potential a thing has, it cannot be leveraged. Similar thought I have about the open-source tools APIs. But in Sequel case a great effort has been put into drafting documentation as is evident from the website but there still is need is for making it clear at various places by elaborating with more examples for various options an API has to offer or adding at-least references to all options it provides and where the examples for those options usage can be found. Finally Thank you for taking the time to respond to my queries with as much elaboration as possible. It has definitely helped in looking at Sequel with right perspective. And I highly appreciate your prompt responses to the queries for the open-source tools you have offered to the community. I can recollect the same feeling when I posted queries about Roda’s usage. You simply are awesome! Thanks. |
Beta Was this translation helpful? Give feedback.
-
Certainly. But documentation cannot optimize for all possible audiences. If you optimize the documentation for people who don't know SQL, you end up making the documentation worse for users who know SQL, and vice-versa. Sequel's documentation deliberate chooses to optimize documentation for users that know SQL. I realize that makes the documentation suboptimal for users that do not know SQL, but I would rather the documentation be more useful for users who know SQL.
I would say that is not a good thing. While Sequel does not encourage the use of raw SQL fragments (as that often can lead to SQL injections), I would say it encourages the full use of SQL concepts.
I would assume so as well. However, I would also assume that Rails developers who know and understand SQL are probably far more productive than Rails developers who do not.
Which I think is a problem. IMO, developers should be aware of what queries are being used. ORMs should be used to make things easier for the user to interact with a database, not as a black box.
Correct. Sequel itself is not an ORM, it is a database access layer. Sequel::Model is an ORM that is built on top of that database access layer. ROM is another ORM built on top of that database access layer.
I suppose that may be true for some experienced developers, but certainly not for all. You should not project that other users will generally have the same experience you had, because they come from different backgrounds, and especially not in the case where the hypothetical user would have more knowledge than you have. A experienced SQL user would likely know the difference between separate queries to add a column and set a default, versus a single query to add a column with a default. And the documentation for the method does link to the list of comprehensive options, even if no example of those options is given.
That's fine. I also am not attempting to debate, merely offering my perspective on the situation.
As pointed out earlier, the documentation in question does reference all options. It doesn't specifically give an example of those options. Again, I'm open to additional examples.
Thank you very much! |
Beta Was this translation helpful? Give feedback.
-
I am using rom-rb which internally uses Sequel and my DB is Postgres. Following are the gem versions being used.
I created following DB migration to alter an existing table by adding a not-null column with a default value:
But running that migration causes following error:
Initially I thought may be the default 0 value for a foreign key is causing problem but even after changing it to a valid value the error persisted. So to get past the error I took following approach:
my_new_columntomy_table_name(please see Migration-1 ahead).my_table_name'smy_new_columnto correct values.my_table_name'smy_new_columnnot-null (please see Migration-2 ahead)..Migration-1
Migration-2
But somehow I am not finding this multiple migrations approach correct and esp running the migrations on different development envs already containing existing tables can become annoyance. So that makes me ask the question what is the right approach in Sequel to create a migration for altering an existing table by adding a not-null column with a default value?
Also a subjective thought which came to my mind that when using
set_column_not_null :my_new_columnshouldn't it support option for specifying a default value? I mean without a default value adding a not-null column to an existing table doesn't make sense.There is one more weird issue I encountered related to a migration which alters a table by adding a null column. Following is a
uppart of the migration:When ran the migration I encountered following error:
However when I modified the
uppart in following manner the migration executed successfully:I found that behaviour weird.
Please share thoughts on the correct approaches to be employed if I am wrong anywhere in my usage of the Migration API.
Thanks.
Beta Was this translation helpful? Give feedback.
All reactions