Question 267035 in Laravel Question posted in
The official Laravel Documentation can be found here.

Error when switching beetween mysql to postgresql connection dynamically in Laravel

I get this error when trying dynamically switch from mysql to postgresql:

SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "FROM" LINE 1: SHOW KEYS FROM wb_sales WHERE Key_name LIKE ‘%unique%’;

Error occuring when I’m trying to upsert a record.
I have this code to switch between databases(everything is ok with $database variable):

DB::purge($connection);

config([
    'database.connections.' . $connection . '.driver' => $database['driver'],
    'database.connections.' . $connection . '.host' => $database['host'],
    'database.connections.' . $connection . '.port' => $database['port'],
    'database.connections.' . $connection . '.database' => $database['database'],
    'database.connections.' . $connection . '.username' => $database['username'],
    'database.connections.' . $connection . '.password' => $database['password'],
]);
$sale = new WbSale(); 
$sale->setConnection($connection);
$sale->upsert($chunk);

Also, this code is working when I am switching to different mysql connection.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    The problem is solved. I discovered that with postgresql you should explicitly specify unique keys in array as second argument, while with mysql laravel can read unique keys from table. So I just changed last row:

    $sale->upsert($chunk,['sale_id']);

  2. 0

    Switching between MySQL and PostgreSQL database connections dynamically in Laravel can be a bit challenging but is possible. Here’s a general approach to handle this:

    1. Set Up Database Configurations:

      In your config/database.php file, define two separate database connections, one for MySQL and the other for PostgreSQL. Each connection should have its own set of credentials and configuration.

      'connections' => [
    'mysql' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', 'localhost'),
        'database' => env('DB_DATABASE', 'forge'),
        'username' => env('DB_USERNAME', 'forge'),
        'password' => env('DB_PASSWORD', ''),
        // ...
    ],
    'pgsql' => [
        'driver' => 'pgsql',
        'host' => env('PG_DB_HOST', 'localhost'),
        'database' => env('PG_DB_DATABASE', 'forge'),
        'username' => env('PG_DB_USERNAME', 'forge'),
        'password' => env('PG_DB_PASSWORD', ''),
        // ...
    ],
],

    2. Switching the Connection Dynamically:

      In your code, you can switch between the two connections dynamically based on your application’s requirements. You can use the DB::connection() method to change the active database connection.

      // To use the MySQL connection
DB::connection('mysql')->table('table_name')->get();

// To use the PostgreSQL connection
DB::connection('pgsql')->table('table_name')->get();

    3. Environment Variables:

      In your .env file, define the necessary environment variables for both MySQL and PostgreSQL connections.

      DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=your_mysql_db
DB_USERNAME=root
DB_PASSWORD=your_mysql_password

PG_DB_CONNECTION=pgsql
PG_DB_HOST=127.0.0.1
PG_DB_PORT=5432
PG_DB_DATABASE=your_pg_db
PG_DB_USERNAME=pg_user
PG_DB_PASSWORD=pg_password

    4. Dynamic Selection:

      Depending on your application’s logic, you can dynamically choose which database connection to use based on the current request or other conditions.

      $connection = $shouldUseMySQL ? 'mysql' : 'pgsql';
DB::connection($connection)->table('table_name')->get();

    By following these steps, you can seamlessly switch between MySQL and PostgreSQL database connections as needed within your Laravel application. Just ensure that the configuration and credentials for each database are correctly set up, and that you choose the appropriate connection at runtime based on your application’s logic.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search