How can I migrate MySQL Code to PostgreSQL

Question

I deployed my laravel app on Heroku for the first time which uses the MySQL database. Though when I tried to add my credit card to enable me to use MySQL addons, I get an error saying

Error
Unable to verify your card. Please try later or contact your financial institution for help. 

And I tried using different cards, and I still get the same error. So I noticed that I can use Heroku Postgres addons without adding a credit card. And that is what I used, though I have no experience using Postgres.

When I tried accessing some pages that have to fetch data or interact with my database, I get an error like this:

SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: character varying = bigint
LINE 1: ...rals" inner join "users" on "referrals"."user_id" = "users"....
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts. (SQL: select * from "referrals" inner join "users" on "referrals"."user_id" = "users"."id" where "referrals"."referral_id" = gqFApQsz055fafcab840daf39379e35001d9d426) (View: /app/resources/views/User/profile.blade.php)

I know the cause of the error is the change of database type. So my main question is, is there a way for me to migrate the MySQL code automatically to PostgreSQL or is there any suggestion for me to use a free remote MySQL database and connect to my Heroku application.

Answer 1

https://laracasts.com/discuss/channels/laravel/laravel-migration-with-postgresql?page=1&replyId=147211

Either rename or make a copy of the existing ".env.example" to ".env"

Note! The values contained in the .env file is prepared for the Homestead box and should work with little modification if at all. However, if not using the Homestead VM but a Wamp or Xamp for example, you will need to provide the values specific to your environment.

There you will have the variable names actually used through out the framework. For example;

# Add this to change driver, the default is mysql
DB_CONNECTION=pgsql  # or sqlite

# Add this if you are not using Homestead and you know the default pgsql port has not changed.
DB_PORT=5432 

# Change the values to suit your environment if not on Homestead
DB_HOST=127.0.0.1
DB_DATABASE=homestead  # or mainpagedb
DB_USERNAME=homestead     # or postgres 
DB_PASSWORD=secret   # or postgres

# For the Schema you can add the following 
DB_PGSQL_SCHEMA=fes

# then edit in 'config/database.php
'pgsql' => [
            'driver'   => 'pgsql',
            'host'     => env('DB_HOST', 'localhost'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset'  => 'utf8',
            'prefix'   => '',
        // Notice the following has been modified
            'schema'   => env('DB_PGSQL_SCHEMA','public'),
        ],

You can create new environment variables (Key=>value) pairs and then call them elsewhere with env('Key', 'some default if key not set')

The easiest way to get started with Laravel is to use the Homestead Virtual Machine. However, using sqlite is equally as flexible to start. You can later switch to a different provider. For sqlite, just create an empty 'database/database.sqlite' file in the folder indicated.