I want to scale out my MySQL database into few servers using horizontal sharding. Let's imagine that I have 5 database servers (not replicas) and I want to distribute the data from users table between 5 database servers:
Shard 1 (192.168.1.1)
Shard 2 (192.168.1.2)
Shard 3 (192.168.1.3)
Shard 4 (192.168.1.4)
Shard 5 (192.168.1.5)
Now I want to connect to one of them depending on user_id (server_id = user_id % 5). I will do this on each API request from users in my Go application.
I'm using go-sql-driver and standard database/sql package.
import "database/sql"
import _ "github.com/go-sql-driver/mysql"
shard1, _ := sql.Open("mysql", "user:password@tcp(192.168.1.1:3306)/dbname")
shard2, _ := sql.Open("mysql", "user:password@tcp(192.168.1.2:3306)/dbname")
shard3, _ := sql.Open("mysql", "user:password@tcp(192.168.1.3:3306)/dbname")
...
There is a basic connection pool in the database/sql package, but there is not a lot of control on it. There are also few methods: SetMaxIdleConns, SetMaxOpenConns, SetConnMaxLifetime, but it looks that they work only with single database server at once.
The question is how to properly handle and pool database connections in my Golang application? How to work with multiple database servers in Golang?
Should I create a singleton object with connection map with *DB values, store all connections there and use them across the whole application? For example:
connections := make(map[string]interface{})
connections["shard1"] = shard1
connections["shard2"] = shard2
...
How to close connections or don't close them after SQL query execution?