Updated on 2023-10-23 GMT+08:00

Connecting to the Database

When you call the standard SQL API open of the Go language to create a database connection, a connected object is returned to transfer the driver name and description string.

Function Prototype

The Go driver provides the following method to generate a database connected 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.

      Example:

      Single IP address: host = 127.0.0.1 port = 1611 user = GaussDB password = GaussDB_Kernel dbname = postgres sslmode = disable connect_timeout = 100

      Multiple IP addresses: host = 127.0.0.1,127.0.0.2 port = 1611,1622 user = GaussDB password = GaussDB_Kernel dbname = postgres sslmode = disable connect_timeout = 100

    • 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[&...].

      Example:

      Single IP address: opengauss://user:password@127.0.0.1:1611/postgres?sslmode=disable&connect_timeout=100

      Multiple IP addresses: opengauss://user:password@127.0.0.1:1611,127.0.0.2:1622/postgres?sslmode=disable&connect_timeout=100

    • 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 result of host = 127.0.0.1, 127.0.0.2,127.0.0.3 port = 1611,1622 is 127.0.0.1:1611,127.0.0.2:1622,127.0.0.3:1611.
      • If the value of num(ip) is smaller than that of num(port), the extra port numbers are discarded. For example, the matching result of host = 127.0.0.1, 127.0.0.2,127.0.0.3 port = 1611,1622,1633,1644 is 127.0.0.1:1611,127.0.0.2:1622,127.0.0.3:1633.
    • 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@127.0.0.1:1611,127.0.0.2:1622,127.0.0.3:1611/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, with opengauss://user:password@127.0.0.1,127.0.0.2,127.0.0.3/postgres and the environment variable PGPORT set to 1611,1622, the matching result is 127.0.0.1:1611,127.0.0.2:1622,127.0.0.3:1611. If the environment variable is not set, the matching result is 127.0.0.1:5432,127.0.0.2:5432,127.0.0.3: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 DN in the URL connection string. If the primary DN cannot be found, an exception is thrown.
  • slave: attempts to connect to a standby DN in the URL connection string. If the standby DN 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 DN can be connected.
  • read-only: specifies that only the standby DN 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 not advised 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.

Example 1:

// The following code uses an ip:port as an example.
func main() {
 str := "host=127.0.0.1 port=1611 user=testuser password=Gauss_234 dbname=postgres sslmode=disable"   // DSN connection string
 // str := "opengauss://testuser:Gauss_234@127.0.0.1:1611/postgres?sslmode=disable"   // URL connection string
 db, err:= sql.Open("opengauss", str)
 if err != nil {
  log.Fatal(err)
	}
	defer db.Close()

	err = db.Ping()
	if err != nil {
		log.Fatal(err)
	}

	sqls := []string {
		"drop table if exists testExec",
		"create table testExec(f1 int, f2 varchar(20), f3 number, f4 timestamptz, f5 boolean)",
		"insert into testExec values(1, 'abcdefg', 123.3, '2022-02-08 10:30:43.31 +08', true)",
		"insert into testExec 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.Exec(s, inF1[i], intF2[i], intF3[i], intF4[i], intF5[i])
				if err != nil {
					log.Fatal(err)
				}
			}
		} else {
			_, err = db.Exec(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.QueryRow("select * from testExec").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.Query("select * from testExec 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)
		}
	}
}

Example 2:

// The following code uses multiple ip:port as an example.
func main() {
	ctx := context.Background()
	ctx2SecondTimeout, cancelFunc2SecondTimeout := context.WithTimeout(ctx, 2 * time.Second)
	defer cancelFunc2SecondTimeout()

	str := "host=127.0.0.1,127.0.0.2,127.0.0.3 port=1611,1622 user=testuser password=Gauss_234 dbname=postgres sslmode = disable"    // DSN connection string
	// str := "opengauss://testuser:Gauss_234@127.0.0.1:1611,127.0.0.2:1612,127.0.0.3/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)
		}
	}
}