CODEWITHSUNDEEP
mysqlnode.jsknex.js
Nico Enking
Nico Enking

Reputation: 49

KnexJS Character Encoding

my question is if it is somehow possible to change the character encoding of KnexJS to utf8mb4. It seems to default to utf8.

When I try to create a table on the current KnexJS Version inside on a NodeJS Server via

db.schema.createTable('testtable', table => {
  table.increments('id');
  table.string('foo').unique();
});

KnexJS throws an error, saying that the specified key was too long. This happens due to KnexJS's varchar length defaulting to 255. The maximum length for a utf8mb4 field is 191 characters.

Is there an option to tell KnexJS what character encoding to use (thus changing the length of varchar fields to 191) or do I have to set the length of every varchar field to 191 myself by passing it as the second parameter on table.string(..., 191)?

The error message:

Error creating testtable table { Error: Specified key was too long; max key length is 767 bytes
at Packet.asError (C:\Users\nicoe\WebstormProjects\worktrackr\backend\node_modules\mysql2\lib\packets\packet.js:713:13)
at Query.Command.execute (C:\Users\nicoe\WebstormProjects\worktrackr\backend\node_modules\mysql2\lib\commands\command.js:28:22)
at Connection.handlePacket (C:\Users\nicoe\WebstormProjects\worktrackr\backend\node_modules\mysql2\lib\connection.js:515:28)
at PacketParser.onPacket (C:\Users\nicoe\WebstormProjects\worktrackr\backend\node_modules\mysql2\lib\connection.js:94:16)
at PacketParser.executeStart (C:\Users\nicoe\WebstormProjects\worktrackr\backend\node_modules\mysql2\lib\packet_parser.js:77:14)
at Socket.<anonymous> (C:\Users\nicoe\WebstormProjects\worktrackr\backend\node_modules\mysql2\lib\connection.js:102:29)
at Socket.emit (events.js:159:13)
at addChunk (_stream_readable.js:265:12)
at readableAddChunk (_stream_readable.js:252:11)
at Socket.Readable.push (_stream_readable.js:209:10)
at TCP.onread (net.js:598:20)


code: 'ER_TOO_LONG_KEY',
  errno: 1071,
  sqlState: '42000',
  sqlMessage: 'Specified key was too long; max key length is 767 bytes' }

Upvotes: 1

Views: 2397

Answers (1)

Abhyudit Jain
Abhyudit Jain

Reputation: 3748

It's not the fault with knex that you are getting that error. It's that you are creating the string column of length 255, which is the default if you don't pass any parameters.

You can change the default length of the column by:

db.schema.createTable('testtable', table => {
  table.increments('id');
  table.string('foo', 300).unique(); // 300 length
});

Or if you want to store really large text, you can use:

db.schema.createTable('testtable', table => {
  table.increments('id');
  table.text('foo').unique();
});

Upvotes: 1

Related Questions