Fetching tables' and columns' comments

[Bahanix]

Hello!

I have comments in my MySQL DB, and I can find them this way:

Sequel.version
# "5.86.0"

db = Sequel.connect(ENV["DATABASE_URL"])
db.fetch("SHOW CREATE TABLE users").first[:'Create Table']
# CREATE TABLE `users` (
#   `id` int NOT NULL AUTO_INCREMENT,
#   `first_name` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT 'this is a comment example',
#   ...

I would like to get these comments in a more high-level way, eg.:

db.schema("users").to_h[:first_name]
# It returns:
# {:primary_key=>false, :generated=>false, :allow_null=>true, :default=>nil, :db_type=>"varchar(45)", :type=>:string, :extra=>"", :ruby_default=>nil, :max_length=>45}
# But I would like:
# {:primary_key=>false, comment: "this is a comment example", :generated=>false, :allow_null=>true, :default=>nil, :db_type=>"varchar(45)", :type=>:string, :extra=>"", :ruby_default=>nil, :max_length=>45}
#                       ^

I tried to binding.irb within def schema_parse_table(table_name, opts) (link) to investigate but I could not manage to find the comment (eg. inspecting row)

Am I missing something that already exists, and if it doesn't, do you have any hint about how to do that? If yes, would a PR be appreciated? I'd gladly contribute.

Thank you very much 🙏

[jeremyevans]

Sequel doesn't query for the comments. Looks like it may be possible using SHOW FULL COLUMNS FROM table instead of DESCRIBE table. However, I don't think most Sequel users need the comments, and we don't retrieve comments when parsing schema for any other databases, so doing it just for MySQL doesn't make sense.

There is an external Sequel extension for PostgreSQL comments: https://github.com/mpalmer/sequel-pg-comment . You may consider building one for MySQL comments.

[Bahanix]

Thank you for your prompt answer! I share here what I tried:

• MySQL and Postgres schema: retrieve columns' comments #2249

But the extension may still be a better option, your call