![]() ![]() PostgreSQL is very strict with the expressions for generated columns. this.raw('ALTER TABLE services ADD INDEX (service_year)') - this.raw('ALTER TABLE services ADD INDEX (service_month)') - this.raw('ALTER TABLE services ADD INDEX (service_day)') + this.raw('CREATE INDEX IF NOT EXISTS services_service_year_idx ON services (service_year)') + this.raw('CREATE INDEX IF NOT EXISTS services_service_month_idx ON services (service_month)') + this.raw('CREATE INDEX IF NOT EXISTS services_service_day_idx ON services (service_day)') this.raw('ALTER TABLE user_profiles ADD FULLTEXT (full_name)') + this.raw(`CREATE INDEX user_profiles_full_name_fulltext_idx ON user_profiles USING GIN (to_tsvector('english', full_name)) `) PostgreSQL GIN index was used to replace MySQL FULLTEXT index. PostgreSQL does not support specifying the column before or after which to insert a column. No AFTER or BEFORE While Creating Columns Generally, the following changes were made to the table migration scripts: 1. However, there were some raw queries in the table migration scripts which needed to be adapted for PostgreSQL. Since AdonisJS uses Knex.js underneath the Lucid ORM, there was minimal refactoring of the migration scripts as Knex.js ensures compatibility with MySQL and PostgreSQL out of the box. The first step was refactoring the existing table migration scripts and making them compatible with PostgreSQL. Also, at the time of writing this, Gotedo is still using AdonisJS 4.x, so the syntax of the examples will be a bit different from that of AdonisJS 5.x. You can easily adapt them to suit your language/framework. We use AdonisJS for the Gotedo backend, so the examples will be highlighted with AdonisJS Lucid ORM and JavaScript language. The MySQL database had 114 tables with delicate foreign key relationships. It would also be easy to perform this kind of huge database engine migration now that the software has few active users. Other reasons included taking advantage of the advanced full-text search capabilities of PostgreSQL and positioning the software for future multi-tenancy requirements with PostgreSQL's multi-schema database design. ![]() Recently at the Gotedo Church Management Software, we were faced with the decision to continue using MySQL and implement hierarchical data completely from scratch or migrate to PostgreSQL and take advantage of the ltree data type for easy handling of hierarchical data. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |