Updated on 2024-06-03 GMT+08:00

Connecting to a Database

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

Function Prototype

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 gaussdb.
  • 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]

      driverName indicates the driver name. The database driver name is gaussdb.

      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.
      • If the value of num(ip) is smaller than that of num(port), the extra port numbers are discarded. For example, the matching condition 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: gaussdb://user:password@ip1:port1, ip2:port2, ip3:port3/gaussdb.
      • 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 gaussdb://user:password@ip1, ip2, ip3/gaussdb, ,if the environment variable PGPORT is set to "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

Value of the GUC parameter of the set type that is run by default when a session is connected, for example, search_path, application_name, and 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.

enable_ce

Encrypted database function. enable_ce=1 indicates that the Go driver supports the basic capability of encrypted equality query. enable_ce=3 indicates that the Go driver supports the fully-encrypted mode with software and hardware integrated. If this parameter is not set or set to another value, the encrypted database is disabled.

key_info

This parameter is used together with enable_ce to set parameters for accessing an external key manager in an encrypted database.

auto_sendtoken

Automatic key transmission function. auto_sendtoken=yes indicates that the key transmission is automatically triggered when the connection of the database instance connection pool is initialized. If this parameter is set to no or left empty, this mode is disabled. Note that this function cannot ensure real-time update of key information. In addition, this function can be enabled only when enable_ce is set to 3.

tcp_syn_retries

Number of TCP connection retry times. If this parameter is not set, the default number of retry times on the device is used. You are advised to set this parameter to a value less than the value of connect_timeout.

socketTimeout

Read/Write timeout threshold. If the time of executing service statements or reading data streams from the network exceeds the threshold (that is, when the statement execution time exceeds the specified threshold and no data is returned), the connection is interrupted.

NOTE:

This parameter specifies the maximum execution time of a single SQL statement. If the execution time of a single SQL statement exceeds the value of this parameter, the statement is interrupted and reconnected. You are advised to set this parameter based on service characteristics. If this parameter is not set, the default value 0 is used, indicating that the execution of SQL statement does not time out.