STRING, STRING(255) or VARCHAR (255)?

Question

I use knex and have a MySql db table that was created with the following:

exports.up = function (knex, Promise) {
    return knex.schema.table("members", (table) => {
        table.string("member_activation_hash", 255);
        table.dateTime("member_activation_sent_at");
    });
};

Now I would like to change (only) the name of both columns, and was thinking of the following:

exports.up = function(knex) {
    return knex.schema.raw(
        "ALTER TABLE `members` CHANGE `member_activation_hash` `user_activation_hash` STRING DEFAULT NULL",
        "ALTER TABLE `members` CHANGE `member_activation_sent_at` `user_activation_sent_at` datetime DEFAULT NULL"
    );
};

But I'm not sure about STRING on line 3. When it was created it included 255 but I don't think STRING(255) is an option and VARCHAR(255) would change the data type (which I don't want). What would be the correct way to only change the name of member_activation_hash?

Update: So I now have:

exports.up = function(knex) {
    return knex.schema.raw(
        "ALTER TABLE `members` RENAME COLUMN `member_activation_hash` TO `user_activation_hash`",
        "ALTER TABLE `members` RENAME COLUMN `member_activation_sent_at` TO `user_activation_sent_at`"
    );
};

But this produces the error:

migration failed with error: Expected 1 bindings, saw 0 Error: Expected 1 bindings, saw 0 at replaceRawArrBindings (C:\Users\xxx\node_modules\knex\lib\raw.js:141:11) at Raw.toSQL (C:\Users\xxx\node_modules\knex\lib\raw.js:78:13)

There's no column type in MySQL called `string`. That type belongs to Knex and gets translated to the appropriate type supported by the DBMS. — Álvaro González, Dec 17 '20 at 14:31
Does this answer your question? [Rename a column in MySQL](https://stackoverflow.com/questions/30290880/rename-a-column-in-mysql) — Nico Haase, Dec 17 '20 at 14:31

score 1 · Accepted Answer · answered Dec 17 '20 at 14:24

1

Just rename the column:

ALTER TABLE `members` RENAME COLUMN `member_activation_hash` TO `user_activation_hash`

See the documentation here

answered Dec 17 '20 at 14:24

Bob Jarvis - Слава Україні

48,992
9
77
110

Thanks, I've tried it (please see the update to my post) but this produces an error. – Nick Dec 17 '20 at 14:32
1

Sounds like an issue with `knex`, which I am not familiar with. However, `ALTER TABLE...RENAME COLUMN...` is how you rename a column in MySQL. Getting it to work with a third-party query builder is something you'll have to work through. But [looking online](https://devhints.io/knex) I see that you might want to look at `knex.schema.table.renameColumn`. Best of luck. – Bob Jarvis - Слава Україні Dec 17 '20 at 14:42
Thanks, got it to work with: `exports.up = function(knex) { return knex.schema.table('members', table => { table.renameColumn('member_activation_hash', 'user_activation_hash'); table.renameColumn('member_activation_sent_at', 'user_activation_sent_at'); }); };` – Nick Dec 17 '20 at 14:51

STRING, STRING(255) or VARCHAR (255)?

1 Answers1