Connecting to a Database

The Go driver provides the following method to generate a database connection object:

func Open(driverName, dataSourceName string) (*DB, error)

Parameter description:

driverName indicates the driver name. The database driver name is opengauss, which is compatible with Postgres.
dataSourceName indicates the data source to be connected. The value can be in DSN or URL format.
- DSN format: key1 = value1 key2 = value2... Different groups of keywords are separated by space. The space on the left and right of the equal sign (=) is optional.
- URL format: driverName://[userspec@][hostspec][/dbname][?paramspec]
  In the preceding information, driverName indicates the driver name. The database driver name is "opengauss", which is compatible with "postgres" and "postgresql".
  
  userspec indicates user[:password]. When a URL is used for connection, the password cannot contain separators in the URL string. If the password contains separators, the DSN format is recommended.
```
hostspec indicates [host][:port][, ...].
```
  dbname indicates the database name. Note: The initial user cannot be used for remote login. paramspec indicates name=value[&...].
- In the DSN format, if there are multiple IP addresses:
  - When the value of num(ip) is the same as that of num(port), the IP address matches the port number.
  - When the value of num(ip) is greater than that of num(port), the IP address that cannot match the port number matches the first port number. For example, the matching condition of host = ip1, ip2, ip3 port = port1, port2 is ip1:port1, ip2:port2, ip3:port1.
  - When the value of num(ip) is smaller than that of num(port), the extra port numbers are discarded. For example, the mapping result of host = ip1, ip2, ip3 port = port1, port2, port3, port4 is ip1:port1, ip2:port2, ip3:port3.
- In the URL format, if there are multiple IP addresses:
  - In the URL, ip:port must appear in pairs, that is, the value of num(ip) is the same as that of num(port). Use commas (,) to separate multiple pairs. Example: opengauss://user:password@ip1:port1, ip2:port2, ip3:port3/postgres.
  - The URL contains only multiple IP addresses. The port number is specified by the environment variable or uses the default value 5432. For example, in the case of opengauss://user:password@ip1, ip2, ip3/postgres, if the environment variable is set as PGPORT = "port1, port2", the mapping is ip1:port1, ip2:port2, ip3:port1. If the environment variable is not set, the mapping is ip1:5432,ip2:5432,ip3:5432.

Parameters

**Table 1** Database connection parameters
Parameter	Description
host	IP address of the host server, which can also be specified by the environment variable ${PGHOST}
port	Port number of the host server, which can also be specified by the environment variable ${PGPORT}
dbname	Database name, which can also be specified by the environment variable ${PGDATABASE}
user	Username to be connected, which can also be specified by the environment variable ${PGUSER}
password	Password of the user to be connected
connect_timeout	Timeout interval for connecting to the server, which can also be specified by the environment variable ${PGCONNECT_TIMEOUT}
sslmode	SSL encryption mode, which can also be specified by the environment variable ${PGSSLMODE} Value range: disable: SSL connection is disabled. allow: If the database server requires SSL connection, SSL connection can be enabled. However, authenticity of the database server will not be verified. prefer: If the database supports SSL connection, SSL connection is preferred. However, authenticity of the database server will not be verified. require: SSL connection is required and data is encrypted. However, authenticity of the database server will not be verified. verify-ca: SSL connection is required, and whether the server certificate is issued by a trusted CA is verified. verify-full: SSL connection is required, and whether the server certificate is issued by a trusted CA and whether the host name of the server is the same as that in the certificate are verified.
sslkey	Key location of the client certificate. If SSL connection is required and this parameter is not specified, you can set the environment variable ${PGSSLKEY} to specify the location.
sslcert	File name of the client SSL certificate, which can also be specified by the environment variable ${PGSSLCERT}
sslrootcert	Name of the file that contains the SSL CA certificate, which can also be specified by the environment variable ${PGSSLROOTCERT}
sslcrl	File name of the SSL CRL. If a certificate listed in this file exists, the server certificate authentication will be rejected and the connection will fail. The value can also be specified by the environment variable ${PGSSLCRL}.
sslpassword	Passphrase used to decrypt a key into plaintext. If this parameter is specified, the SSL key is an encrypted file. Currently, the SSL key supports DES encryption and AES encryption. NOTE: The DES encryption algorithm has lower security and poses security risks. Therefore, you are advised to use a more secure encryption algorithm.
disable_prepared_binary_result	The value of this parameter is a string. If it is set to yes, the connection should not use the binary format when the query results are received from prepared statements. This parameter is used only for debugging. Value range: yes and no.
binary_parameters	Specifies whether []byte is always sent in binary format. The value is a string. Value range: yes and no. If this parameter is set to yes, you are advised to bind parameters based on []byte to reduce internal type conversion.
target_session_attrs	Connection type of the database, which can also be specified by the environment variable ${PGTARGETSESSIONATTRS}. This parameter is used to identify the primary and standby nodes. There are six value options, namely, any, master, slave, preferSlave, read-write, and read-only. The default value is any. any: attempts to connect to any DN in the URL connection string. master: attempts to connect to a primary node in the URL connection string. If the primary node cannot be found, an exception is thrown. slave: attempts to connect to a standby node in the URL connection string. If the standby node cannot be found, an exception is thrown. preferSlave: attempts to connect to a standby DN (if available) in the URL connection string. Otherwise, it connects to the primary DN. read-write: specifies that only the primary node can be connected. read-only: specifies that only the standby node can be connected.
loggerLevel	Log level, which is used to print debugging information. The value can also be specified by the environment variable ${PGLOGGERLEVEL}. The value can be trace, debug, info, warn, error, or none, in descending order of priority.
application_name	Name of the Go driver that is being connected. The default value is go-driver. You are advised not to configure this parameter.
RuntimeParams	Runtime parameter to be set to the session default value on a connection, for example, search_path, application_name, or timezone. For details about the parameters, see the default settings of the client connection. You can run the SHOW command to check whether the parameters are set successfully.
autoBalance	Character string type. Use this parameter to enable load balancing connections in the distributed environment. The value can be true, balance, roundrobin, shuffle, priorityn, or false. The default value is false. If this parameter is set to true, balance, or roundrobin, the Go SQL load balancing function is enabled to balance multiple connections of an application to each CN available in the cluster. Example: gauss://user:password@host1:port1,host2:port2/database?autoBalance=true The driver periodically obtains the list of available CNs in the entire cluster. For example, the obtained list is host1:port1,host2:port2,host3:port3,host4:port4. The refreshCNIpsTime parameter specifies the interval for obtaining the list, and the default value is 10s. When autoBalance is enabled on host1 and host2, HA is implemented only for the first connection. The driver will select available CNs from host1, host2, host3, and host4 in sequence to update the available CN list and Connector.Connect will be called on host1, host2, host3, and host4 using the RoundRobin algorithm. priorityn indicates that the driver priority-based load balancing function is enabled. Multiple connections of an application are balanced to the first n available CNs configured in the URL. When the first n CNs are unavailable, connections are randomly allocated to other available CNs in the database cluster. n is a number not less than 0 and less than the number of CNs configured in the URL. Example: gauss://user:password@host1:port1,host2:port2,host3:port3/database?autoBalance=priority2 The driver periodically obtains the list of available CNs in the entire cluster (defined by refreshCNIpsTime). For example, the obtained list is host1:port1,host2:port2,host3:port3,host4:port4,host5:port5,host6:port6, where host1 and host2 are in AZ1, and host3 and host4 are in AZ2. The driver preferentially selects host1 and host2 for load balancing. If both host1 and host2 are unavailable, the driver randomly selects a CN from host3, host4, host5, and host6 for connection. If this parameter is set to shuffle, the driver random load balancing is enabled to randomly and evenly distribute multiple connections of the application to available CNs in the database cluster. Example: gauss://user:password@host1:port1,host2:port2,host3:port3/database?autoBalance=shuffle The driver periodically obtains the list of available CNs in the entire cluster. For example, the obtained list is host1:port1,host2:port2,host3:port3,host4:port4. The refreshCNIpsTime parameter specifies the interval for obtaining the list, and the default value is 10s. For the first connection, host1:port1,host2:port2,host3:port3 is used for HA. For subsequent connections, the shuffle algorithm is used to randomly select a CN from the refreshed CN list. If this parameter is set to false in the centralized scenarios, the driver load balancing and priority-based load balancing functions are disabled. The default value is false. CAUTION: Load balancing is based on the connection level rather than the transaction level. If the connection is persistent and the load on the connection is unbalanced, the load on the CN may be unbalanced. Load balancing can be used only in distributed scenarios and cannot be used in centralized scenarios. NOTE: Load balancing is based on the connection level rather than the transaction level. If the connection is persistent and the load on the connection is unbalanced, the load on the CN may be unbalanced. Load balancing can be used only in distributed scenarios and cannot be used in centralized scenarios.
recheckTime	Integer type. Use this parameter to specify the interval at which the driver periodically checks the status of CNs in the database cluster and obtains the IP address list of available CNs. The value ranges from 5s to 60s and the default value is 10s.
usingEip	Boolean type. This value specifies whether to use an elastic IP address for load balancing. The default value is true, indicating that an elastic IP address is used for load balancing. The value false indicates that a data IP address is used for load balancing.

Example 1:

// Multiple IP addresses and ports (ip:port) are used as an example. In this example, the username and password are stored in environment variables. Before running this example, set environment variables in the local environment (set the environment variable name based on the actual situation).
func main() {
 ctx := context.Background()
 ctx2SecondTimeout, cancelFunc2SecondTimeout := context.WithTimeout(ctx, 2 * time.Second)
 defer cancelFunc2SecondTimeout()
 
 hostip1 := os.Getenv("GOHOSTIP1")   // GOHOSTIP1 indicates the IP address written into the environment variable.
 hostip2 := os.Getenv("GOHOSTIP2")   // GOHOSTIP2 indicates the IP address written into the environment variable.
 hostip3 := os.Getenv("GOHOSTIP3")   // GOHOSTIP3 indicates the IP address written into the environment variable.
 port1 := os.Getenv("GOPORT1")       // GOPORT1 indicates the port number written into the environment variable.
 port2 := os.Getenv("GOPORT2")       // GOPORT2 indicates the port number written into the environment variable.
 usrname := os.Getenv("GOUSRNAME") // GOUSRNAME indicates the username written into the environment variable.
 passwd := os.Getenv("GOPASSWD")   // GOPASSWDW indicates the user password written into the environment variable.

 str := "host="+hostip1+","+hostip2+","+hostip3+" port="+port1+","+port2+" user="+usrname+" password="+passwd+" dbname=postgres sslmode=disable" // DSN connection string.
 //str := "opengauss://"+usrname+":"+passwd+"@"+hostip1+":"+port1+","+hostip2+":"+port2+","+hostip3+"/postgres?sslmode=disable"  // URL connection string.
 db, err:= sql.Open("opengauss", str)
 if err != nil {
  log.Fatal(err)
 }
 defer db.Close()

 // Ping database connection with 2 second timeout
 err = db.PingContext(ctx2SecondTimeout)
 if err != nil {
  log.Fatal(err)
 }

 sqls := []string {
  "drop table if exists testExecContext",
  "create table testExecContext(f1 int, f2 varchar(20), f3 number, f4 timestamptz, f5 boolean)",
  "insert into testExecContext values(1, 'abcdefg', 123.3, '2022-02-08 10:30:43.31 +08', true)",
  "insert into testExecContext values(:f1, :f2, :f3, :f4, :f5)",
 }

 inF1 := []int{2, 3, 4, 5, 6}
 intF2 := []string{"hello world", "Huawei", "Beijing 2022 Winter Olympics", "nanjing", "Research Center"}
 intF3 := []float64{641.43, 431.54, 5423.52, 665537.63, 6503.1}
 intF4 := []time.Time{
  time.Date(2022, 2, 8, 10, 35, 43, 623431, time.Local),
  time.Date(2022, 2, 10, 19, 11, 54, 353431, time.Local),
  time.Date(2022, 2, 12, 6, 11, 15, 636431, time.Local),
  time.Date(2022, 2, 14, 4, 51, 22, 747653, time.Local),
  time.Date(2022, 2, 16, 13, 45, 55, 674636, time.Local),
 }
 intF5 := []bool{false, true, false, true, true}

 for _, s := range sqls {
  if strings.Contains(s, ":f") {
   for i, _ := range inF1 {
    _, err := db.ExecContext(ctx2SecondTimeout, s, inF1[i], intF2[i], intF3[i], intF4[i], intF5[i])
    if err != nil {
     log.Fatal(err)
    }
   }
  } else {
   _, err = db.ExecContext(ctx2SecondTimeout, s)
   if err != nil {
    log.Fatal(err)
   }
  }
 }

 var f1 int
 var f2 string
 var f3 float64
 var f4 time.Time
 var f5 bool
 err = db.QueryRowContext(ctx2SecondTimeout, "select * from testExecContext").Scan(&f1, &f2, &f3, &f4, &f5)
 if err != nil {
  log.Fatal(err)
 } else {
  fmt.Printf("f1:%v, f2:%v, f3:%v, f4:%v, f5:%v\n", f1, f2, f3, f4, f5)
 }

 row, err :=db.QueryContext(ctx2SecondTimeout, "select * from testExecContext where f1 > :1", 1)
 if err != nil {
		log.Fatal(err)
	}
	defer row.Close()

	for row.Next() {
		err = row.Scan(&f1, &f2, &f3, &f4, &f5)
		if err != nil {
			log.Fatal(err)
		} else {
			fmt.Printf("f1:%v, f2:%v, f3:%v, f4:%v, f5:%v\n", f1, f2, f3, f4, f5)
  }
 }
}

Parent topic: Development Based on the Go Driver

Previous topic: Development Process

Next topic: Connecting to a Database (Using SSL)