[FEATURE]: Support Comments for Column definitions and introspections

[danicruz0415]

Describe what you want

MySQL has a feature where you can add comments to the column definitions. This is useful when exploring the database from a Database manager as the comment adds information for what the column is for, if the name is not suffitiencly explicit.

(screenshot of phpMyAdmin, a mySQL db manager as an example of how comments are displayed)

I think is a good feature as it allows the dabase to have better documentation. I imagine it as follows:
The following code

export const User = mysqlTable("user", {
    id: int("id").primaryKey().autoincrement().notNull(),
    email: varchar("email", { length: 255 }).notNull().unique().comment("email of the user"), // comment
});

would generate the following table

CREATE TABLE `user` (
    `id` int AUTO_INCREMENT NOT NULL,
    `email` varchar(255) NOT NULL COMMENT 'email of the user', -- comment
    CONSTRAINT `user_id` PRIMARY KEY(`id`),
    CONSTRAINT `user_email_unique` UNIQUE(`email`)
);

To update a column, alter statements allow to add comments as described here.
Also, for the introspect tool, current comments can be read as explained here.

PS: sorry if this was proposed before, I did not find any issue relating column comments
Thanks!

[HarrisonJackson]

Another use case...
This would make it easier to go from drizzle --> drizzle-zod --> zod-to-json-schema with better json schema output. This will make it easier to shovel AI output into my DB 😬

Drizzle-zod would need to read these fields, too, for that to work. First this though 🙏

[busy-mango]

Very much looking forward to this feature!

[xegulon]

Yeah, I'd love to see it too. For self-documenting the code, it's great.

@AndriiSherman maybe you could show me which files to modify so that I can make a PR?

[AdamVerner]

Postgre also has Comments, although it has slightly different syntax from mysql.
SQLite does not have official support for comments, but in some cases comments are presereved in the DDL statements.

It would be lovely to see this feature!

[callumgare]

Not sure about MySQL but I believe postgres also allows you to add comments to a bunch of different objects, not just columns. Adding comments for tables and relationships would also be quite handy I think.