Multiple database in laravel project

This is covered in the documentation

https://laravel.com/docs/5.7/database#using-multiple-database-connections

Just configure each connection in your config/database.php with unique names then you can use DB::connection(connectionname)...

In my opinion, I would avoid unless you find a use case that demands this!

You can do it with eloquent as well by specifying the connection in the model.

class Flight extends Model
{
    /**
     * The connection name for the model.
     *
     * @var string
     */
    protected $connection = 'connection-name';
}

where connection-name is the name of the connection as defined in your /config/database.php file.

@D9705996 what about if we have 2 to 3 hundred of tables and have performance issue so what your's suggestion ?

Figure out why you're having performance issues? Examine the queries with explain and look for slow ones and/or enable slow query logging? Check that everything is indexed properly (not just the id field). Yes, if any of these servers are remote, they will take longer to query against, especially when using relationships as the queries are run individually, and then the data is assembled once that is complete (to add the results of the relating queries onto the main model object that you are querying against.)

@D9705996 @Cronix how to use primary key of table in default database as foreign key in another table of diffrent database in laravel. it will be very helpful thanks

You can prefix the column name with the db_name. e.g.

$table->foreign('user_id')->references('id')->on('otherdb.users');

@D9705996 Thanks. Its helpful for me..

how to make relations between two model which belongs to different databases?

@Cronix I have 350+ table in my database and I have 5 modules what do you Suggest me which is best approach to handle these tables with one or multiple databases.

@Aqeel94321 - Do you have a reason/problem that you believe you can solve by using multiple database?

If you describe that problem we might be able to give you better advice as at the moment this is a hypothetical discussion. Table count is not a good metric to benchmark performance or think about multiple databases.

For your model relationships, you simply need to specify the corresponding model as you would do for a single database setup. Eloquent will handle the SQL queries (assuming you have configured your model correctly).

@D9705996 I'm on initial stage so i just want to know that I've 350+ tables and want to separate each module with there separate DB to avoid confusion because each module can have same table attributes, Is this a good approach to get started or just create one DB?

Does a module relate to a table in your database? Are you able to share an example of what a couple of similar table structures look like? This will help identify if you concerns about confusion are warranted or there may be a better solution.

@D9705996 Like we have customer and employee main entities for database. customer have more than 100 tables as well as employee also. relationship between customer and employee is many to many relationship. i want to make separate customer db with name of dbcustomer and employee db with name of dbemloyee. if i don't make separate databases then there will be more than 300 tables in one database. so what is best approach to make all tables in on database or in different databases.

What you are describing is a multitenant application by the sounds of it as you want to use a single application but allow more than one customer to use the application as if it was their own.

There is a lengthy tutorial on medium that mihht give you some ideas.

https://medium.com/@ashokgelal/a-full-featured-multi-tenant-app-with-laravel-part-1-4049a3cc229d

Unfortunately telling you that one database is better than multi or vice versa is the best approach isn't possible as this isn't a one size fits all scenario.

My general rule of thumb would be, if you can manage on a single database then your overall application architecture will be simpler, no network dependencies, one place for all data, administrators can get an overall view of all customers without complex cross database considerations then you should.

Following the tutorial linked above will show that you will end up with a lot less tables than you currently have/anticipate.

If you do find you need to isolate your customers at the database level, like the scenario I found myself in, you will have to spend much more time diagnosing bugs, which may be harder to reproduce as your solution will have a lot of moving parts.

You will also have a overhead when adding a new customer as you will need to provision a new database/server instead of being able to just add a new customer in your existing database. This also means you will need to have a more complex backup process as you will have many different backups from various databases/servers that you need to manage and ensurd they arecworking correctly.

Notice though none of my comments reference database performance. Iv you follow ctonix advice if you have performance issues then the likelihood is the can be solved by either improving the queries you are running, reduce the number of queries (eager loading), increase hardware, especially memory, to allow more data go be loaded and indexed in memory, monitoring and tweaking you database configuration, cache queries that change infrequently in redis to avoid your database altogether.

I'm not saying don't ignore performance but it should not be the main driver in your design decisions.

For reference my current project has thousands of tables, some with tens of thousands of records and I have no bottlenecks with the database layer and I haven't spent much effort on optimising other than making sure my tables are joined correctly and indexed on all indexes and fields I search on. I don't even class this as a massive database.