英文:
Go is querying from the wrong database when using multiple databases with godotenv
问题
我正在尝试从多个数据库查询。每个数据库都使用以下函数连接:
func connectDB(dbEnv str) *sql.DB{
// 从 local.env 文件加载环境变量
err1 := godotenv.Load(dbEnv)
if err1 != nil {
log.Fatalf("发生了一些错误。错误信息:%s", err1)
}
dialect := os.Getenv("DIALECT")
host := os.Getenv("HOST")
dbPort := os.Getenv("DBPORT")
user := os.Getenv("USER")
dbName := os.Getenv("NAME")
password := os.Getenv("PASSWORD")
// 数据库连接字符串
dbURI := fmt.Sprintf("port=%s host=%s user=%s "+"password=%s dbname=%s sslmode=disable", dbPort, host, user, password, dbName)
// 创建数据库对象
db, err := sql.Open(dialect,dbURI)
if err != nil {
log.Fatal(err)
}
return db
}
type order struct{
OrderID string `json:"orderID"`
Name string `json:"name"`
}
type book struct{
OrderID string `json:"orderID"`
Name string `json:"name"`
}
func getOrders(db *sql.DB) []order {
var (
orderID string
name string
)
var allRows = []order{}
query := `
SELECT orderID, name
FROM orders.orders;
`
// 使用查询获取行
rows, err := db.Query(query)
if err != nil { // 如果出错,记录日志
log.Fatal(err)
}
defer rows.Close()
// 将每行添加到 "allRows" 切片中
for rows.Next() {
err := rows.Scan(&orderID, &name)
if err != nil {
log.Fatal(err)
}
// 使用接收到的数据创建新的 order 结构体
row := order{
OrderID: orderID,
Name: name,
}
allRows = append(allRows, row)
}
// 如果出错,记录日志
err = rows.Err()
if err != nil {
log.Fatal(err)
}
return allRows
}
func getBooks(db *sql.DB) []book{
var (
bookID string
name string
)
var allRows = []book{}
query := `
SELECT bookID, name
FROM books.books;
`
// 使用查询获取行
rows, err := db.Query(query)
if err != nil { // 如果出错,记录日志
log.Fatal(err)
}
defer rows.Close()
// 将每行添加到 "allRows" 切片中
for rows.Next() {
err := rows.Scan(&bookID, &name)
if err != nil {
log.Fatal(err)
}
// 使用接收到的数据创建新的 book 结构体
row := book{
BookID: bookID,
Name: name,
}
allRows = append(allRows, row)
}
// 如果出错,记录日志
err = rows.Err()
if err != nil {
log.Fatal(err)
}
return allRows
}
func main() {
ordersDB:= connectDB("ordersDB.env")
booksDB:= connectDB("booksDB.env")
orders := getOrders(ordersDB)
books := getBooks(booksDB)
}
问题是,当我先使用 ordersDB 时,程序只能识别 ordersDB 中的表。当我先使用 booksDB 时,程序只能识别 booksDB 中的表。
当我尝试在使用 ordersDB 后查询 booksDB 中的表时,它会给出"relation 'books.books' does not exist"的错误。当我尝试在使用 booksDB 后查询 ordersDB 中的表时,它会给出"relation 'orders.orders' does not exist"的错误。
有没有更好的方法来连接多个数据库?
英文:
I'm trying to query from multiple databases. Each database is connected using the following function:
func connectDB(dbEnv str) *sql.DB{
// Loading environment variables from local.env file
err1 := godotenv.Load(dbEnv)
if err1 != nil {
log.Fatalf("Some error occured. Err: %s", err1)
}
dialect := os.Getenv("DIALECT")
host := os.Getenv("HOST")
dbPort := os.Getenv("DBPORT")
user := os.Getenv("USER")
dbName := os.Getenv("NAME")
password := os.Getenv("PASSWORD")
// Database connection string
dbURI := fmt.Sprintf("port=%s host=%s user=%s "+"password=%s dbname=%s sslmode=disable", dbPort, host, user, password, dbName)
// Create database object
db, err := sql.Open(dialect,dbURI)
if err != nil {
log.Fatal(err)
}
return db
}
type order struct{
OrderID string `json:"orderID"`
Name string `json:"name"`
}
type book struct{
OrderID string `json:"orderID"`
Name string `json:"name"`
}
func getOrders(db *sql.DB) []order {
var (
orderID string
name string
)
var allRows = []order{}
query := `
SELECT orderID, name
FROM orders.orders;
`
//Get rows using the query
rows, err := db.Query(query)
if err != nil { //Log if error
log.Fatal(err)
}
defer rows.Close()
// Add each row into the "allRows" slice
for rows.Next() {
err := rows.Scan(&orderID, &name, &date)
if err != nil {
log.Fatal(err)
}
//Create new order struct with the received data
row := order{
OrderID: orderID,
Name: name,
}
allRows = append(allRows, row)
}
//Log if error
err = rows.Err()
if err != nil {
log.Fatal(err)
}
return allRows
}
func getBooks(db *sql.DB) []book{
var (
bookID string
name string
)
var allRows = []book{}
query := `
SELECT bookID, name
FROM books.books;
`
//Get rows using the query
rows, err := db.Query(query)
if err != nil { //Log if error
log.Fatal(err)
}
defer rows.Close()
// Add each row into the "allRows" slice
for rows.Next() {
err := rows.Scan(&bookID, &name)
if err != nil {
log.Fatal(err)
}
//Create new book struct with the received data
row := book{
BookID: bookID,
Name: name,
}
allRows = append(allRows, row)
}
//Log if error
err = rows.Err()
if err != nil {
log.Fatal(err)
}
return allRows
}
func main() {
ordersDB:= connectDB("ordersDB.env")
booksDB:= connectDB("booksDB.env")
orders := getOrders(ordersDB)
books := getBooks(booksDB)
}
The issue is that when I use ordersDB first, the program only recognizes the table in ordersDB. And when I use booksDB first, the program only recognizes the table in booksDB.
When I try to query a table in booksDB after using ordersDB, it is giving me "relation "books.books" does not exist" error. When I try to query a table in ordersDB after using booksDB, it gives "relation "orders.orders" does not exist"
Is there a better way to connect to multiple databases?
答案1
得分: 2
你正在使用github.com/joho/godotenv
从环境中加载数据库配置。简而言之(省略了很多细节),你所做的是:
godotenv.Load("ordersDB.env")
host := os.Getenv("HOST")
// 连接到数据库
godotenv.Load("booksDB.env")
host := os.Getenv("HOST")
// 连接到第二个数据库
然而,正如文档中所述,“已存在的环境变量优先于后加载的环境变量”。这在这里也更清楚地说明了:“重要的是要注意,它不会覆盖已经存在的环境变量”。
因此,你的代码将加载第一个.env
文件,填充环境变量,并连接到数据库。然后,你将加载第二个.env
文件,但由于环境变量已经设置,它们不会被更改,因此你将第二次连接到同一个数据库。
作为解决方法,你可以使用Overload。然而,最好重新考虑你对环境变量的使用(也许为第二个连接使用不同的变量)。
英文:
You are using github.com/joho/godotenv
to load the database configuration from the environment. Summarising (and cutting out a lot of detail) what you are doing is:
godotenv.Load("ordersDB.env")
host := os.Getenv("HOST")
// Connect to DB
godotenv.Load("booksDB.env")
host := os.Getenv("HOST")
// Connect to DB 2
However as stated in the docs "Existing envs take precedence of envs that are loaded later". This is also stated more clearly here "It's important to note that it WILL NOT OVERRIDE an env variable that already exists".
So your code will load in the first .env
file, populate the environment variables, and connect to the database. You will then load the second .env
file but, because the environmental variables are already set, they will not be changed and you will connect to the same database a second time.
As a work around you could use Overload. However it's probably better to reconsider your use of environmental variables (and perhaps use different variables for the second connection).
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论