Migrations: Use raw SQL to extend our text fields

[ErisDS]

Since forever, we've been wanting to perform some more complex migrations - particularly around increasing the length of text fields.

Unfortunately, knex doesn't provide a way to do this. I'd love to get these features added to knex, but I've not figured out how to move forward within the refactors and planned internal APIs of knex.

For most projects, a missing migration isn't too tricky, as they can just write SQL. For us it poses a problem as we have to be able to perform the migration on 3 different types of database (mysql, sqlite3, postgres).

By focusing in on the need to increase some field lengths, I think we can hack this into Ghost itself.

There are two main "types" of text field that need to have their lengths increased: text and string. In our schema, both have a maxlength and text fields also have a fieldtype (usually set to medium but as of #6302 will also have long).

Data types

Below are the details of how these translate into SQLite, MySQL and postgres:

SQLite:

• treats both text and string exactly the same, ignores fieldtype on text fields, and does not use the maxlength. (maxlength is used in server-side validations which prevent data loss). The default maximum length of any field in SQLite is 1billion (1,000,000,000) or ~1GB: https://www.sqlite.org/limits.html#max_length

MySQL:

• text fields can have one of four sizes, of which mediumtext and longtext are the two we use. mediumtext is a maximum of 16,777,215, we use this to set the maxlength so server-side validations prevent data loss / database errors. longtext is a maximum of 4,294,967,295, however this is 4x longer than sqlite & postgres can handle under normal circumstances, so we use maxlength to restrict it to 1billion.
• string fields are called varchar in MySQL (and postgres). They have a very specific length, and so maxlength from our schema is used to set a hard limit in the database as well as in the server-side validations. The maximum the limit on a varchar can be used to be 255 (same as tinytext) and is now 65,535 same as text.

Postgres:

• text has no types and no specific limit, although postgres' field limit is very similar to sqlite3: https://wiki.postgresql.org/wiki/FAQ#What_is_the_maximum_size_for_a_row.2C_a_table.2C_and_a_database.3F
• string fields are varchars and again have a specific hard limit. The maximum that limit can be is 1GB again.

How to alter data types

Because of the funny rules between the specific databases, we end up with the following rules for altering data types:

1. To change the length of a text field, we only need to run an alter statement in MySQL. E.g. to increase the size of a posts html field:

ALTER TABLE posts MODIFY COLUMN html longtext;

Simply increasing hte maxlength property in the schema will increase the size of data that can be sent to postgres and sqlite3. The only thing to do is test what happens at the boundaries, and try to make sure that our maxlength prevents us from getting database errors.

2. To change the length of a string field, we need to run an alter statement in both MySQL and Postgres, and the statements are, I believe, almost the same, E.g. to increase the posts meta_description to 1000 characters:

MYSQL: ALTER TABLE posts MODIFY COLUMN meta_description varchar(1000);
PG: ALTER TABLE posts ALTER COLUMN meta_description TYPE varchar(1000);

3. To change from a string, to a text type uses the same commands, and again only needs to be done for MySQL and Postgres.

Given that SQlite3 is usually a nightmare for implementing column modifications, this makes the work much easier, as for SQLite3 the migration will be a noop.

Implementing Migrations

With all of this knowledge, I believe that we can implement a new migration for changing the data type on text based columns only.

The migration should detect:

• changes in type from string to text
• changes in maxlength
• changes in fieldtype

It should then be able to enumerate the change as being one of either:

1. Increasing the length of a text field.
2. Increasing the length of a string field.
3. Changing a string field to be a text field.

If the change is a decrease in length, or changing from text to string it should output an error message, as these migrations could result in data loss.

It should then be possible to detect which database is currently configured, and to determine the correct raw SQL statements to run (if any).

Getting it done

I'm more than happy to look into the work needed here, but I would love someone with more postgres experience to help.

I'd also love to have a volunteer for testing the maximum lengths of a field in postgres & sqlite3 to see if it's safe to set these to 1000000000 or if the maximum should be smaller than this.

[ErisDS]

As part of Ghost 1.0.0 we're going to audit and change the max lengths across the whole database as covered by #4134. We may need some of this at a later date, but for now we don't need to do this. Closing with the later label for now.