Local and remote Database connection

Hi Everyone,

For my application I use MySQL in localhost (LAMP) to manage data, input by users.

In the same application, I have a requirement to fetch data from a remote SQLsrv too.

Kindly assist me to achieve it

.env file configuration

DB_CONNECTION=mysql
DB_HOST=localhost
DB_PORT=3306
DB_DATABASE=DB
DB_USERNAME=user1
DB_PASSWORD=

Configured my config/database.php with

    'default' => env('DB_CONNECTION', 'mysql'),

        'sqlsrv' => [
            'driver' => 'sqlsrv',
            'host' => env('DB_HOST', '10.10.90.1'),
            'port' => env('DB_PORT', '1433'),
            'database' => env('DB_DATABASE', 'project1'),
            'username' => env('DB_USERNAME', 'user1'),
            'password' => env('DB_PASSWORD', 'psswd'),
            'charset' => 'utf8',
            'prefix' => '',
        ],

    ],

ctroms

8 years ago

Level 15

You'll have to define env variables for both connections and add both connections to your config/database.php configuration file. You might consider defining a prefix for each group of environment variable by connection type. Something like MYSQL_ mysql connection and SQLSVR_ for your sql server connection environment variables. Then you can use the `connection() method to select which connection you want to use for your query.

DB::connection('foo')->select(...);

Here is a Docs Reference for multiple database connections.

You'll also want to include the sqlsrv connection parameters in your code sample in the connections array of your database.php config file.

venkat1806

8 years ago

Level 1

Thanks for your reply.

What about the default value

'default' => env('DB_CONNECTION', 'mysql'),

Should be set to mysql or sqlsvr. I use mysql for read & write but Sqlsvr only to read.

ctroms

8 years ago

Level 15

If you don't specify a connection, the connection specified here will be used. If you are always going to specify a connection then this definition has less value.

You might want the default to reference whichever connection you use the most. Given how you describes your setup, I would probably leave mysql for the default since that is where all of your user input goes.

Please or to participate in this conversation.