当使用godotenv和多个数据库时,Go在查询时从错误的数据库中查询。

huangapple go评论85阅读模式
英文:

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).

huangapple
  • 本文由 发表于 2022年6月9日 11:13:05
  • 转载请务必保留本文链接:https://go.coder-hub.com/72554331.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定