Gerenciar conexões de banco de dados

Veja nesta página práticas recomendadas e amostras de código específicas de cada linguagem para ajudar você a criar aplicativos que usam conexões de bancos de dados SQL de modo eficiente.

Essas amostras são trechos de um aplicativo completo do App Engine disponível para você no GitHub. Saiba mais.

Pools de conexões

Um pool de conexões é um cache de conexões de banco de dados que são compartilhadas e reutilizadas para melhorar a latência e o desempenho da conexão. Quando seu aplicativo precisa de uma conexão de banco de dados, ele pega uma emprestada do pool temporariamente. Assim que ele termina de usar a conexão, ela é devolvida ao pool para ser reutilizada da próxima vez em que o aplicativo precisar de uma conexão de banco de dados.

Como se conectar com soquetes Unix

Python

Para ver esse snippet no contexto de um aplicativo da Web, consulte o README no GitHub (em inglês).

# Remember - storing secrets in plaintext is potentially unsafe. Consider using
# something like https://cloud.google.com/secret-manager/docs/overview to help keep
# secrets secret.
db_user = os.environ["DB_USER"]
db_pass = os.environ["DB_PASS"]
db_name = os.environ["DB_NAME"]
db_socket_dir = os.environ.get("DB_SOCKET_DIR", "/cloudsql")
cloud_sql_connection_name = os.environ["CLOUD_SQL_CONNECTION_NAME"]

pool = sqlalchemy.create_engine(

    # Equivalent URL:
    # postgres+pg8000://<db_user>:<db_pass>@/<db_name>
    #                         ?unix_sock=<socket_path>/<cloud_sql_instance_name>/.s.PGSQL.5432
    sqlalchemy.engine.url.URL(
        drivername="postgresql+pg8000",
        username=db_user,  # e.g. "my-database-user"
        password=db_pass,  # e.g. "my-database-password"
        database=db_name,  # e.g. "my-database-name"
        query={
            "unix_sock": "{}/{}/.s.PGSQL.5432".format(
                db_socket_dir,  # e.g. "/cloudsql"
                cloud_sql_connection_name)  # i.e "<PROJECT-NAME>:<INSTANCE-REGION>:<INSTANCE-NAME>"
        }
    ),
    **db_config
)

Java

Para ver esse snippet no contexto de um aplicativo da Web, consulte o README no GitHub (em inglês).

// Note: For Java users, the Cloud SQL JDBC Socket Factory can provide authenticated connections
// which is preferred to using the Cloud SQL Proxy with Unix sockets.
// See https://github.com/GoogleCloudPlatform/cloud-sql-jdbc-socket-factory for details.

// The configuration object specifies behaviors for the connection pool.
HikariConfig config = new HikariConfig();

// The following URL is equivalent to setting the config options below:
// jdbc:postgresql:///<DB_NAME>?cloudSqlInstance=<CLOUD_SQL_CONNECTION_NAME>&
// socketFactory=com.google.cloud.sql.postgres.SocketFactory&user=<DB_USER>&password=<DB_PASS>
// See the link below for more info on building a JDBC URL for the Cloud SQL JDBC Socket Factory
// https://github.com/GoogleCloudPlatform/cloud-sql-jdbc-socket-factory#creating-the-jdbc-url

// Configure which instance and what database user to connect with.
config.setJdbcUrl(String.format("jdbc:postgresql:///%s", DB_NAME));
config.setUsername(DB_USER); // e.g. "root", "postgres"
config.setPassword(DB_PASS); // e.g. "my-password"

config.addDataSourceProperty("socketFactory", "com.google.cloud.sql.postgres.SocketFactory");
config.addDataSourceProperty("cloudSqlInstance", CLOUD_SQL_CONNECTION_NAME);

// The ipTypes argument can be used to specify a comma delimited list of preferred IP types
// for connecting to a Cloud SQL instance. The argument ipTypes=PRIVATE will force the
// SocketFactory to connect with an instance's associated private IP.
config.addDataSourceProperty("ipTypes", "PUBLIC,PRIVATE");

// ... Specify additional connection properties here.
// ...

// Initialize the connection pool using the configuration object.
DataSource pool = new HikariDataSource(config);

Node.js

Para ver esse snippet no contexto de um aplicativo da Web, consulte o README no GitHub (em inglês).

const createUnixSocketPool = config => {
  const dbSocketPath = process.env.DB_SOCKET_PATH || '/cloudsql';

  // Establish a connection to the database
  return Knex({
    client: 'pg',
    connection: {
      user: process.env.DB_USER, // e.g. 'my-user'
      password: process.env.DB_PASS, // e.g. 'my-user-password'
      database: process.env.DB_NAME, // e.g. 'my-database'
      host: `${dbSocketPath}/${process.env.CLOUD_SQL_CONNECTION_NAME}`,
    },
    // ... Specify additional properties here.
    ...config,
  });
};

C#

Para ver esse snippet no contexto de um aplicativo da Web, consulte o README no GitHub (em inglês).

// Equivalent connection string:
// "Server=<dbSocketDir>/<INSTANCE_CONNECTION_NAME>;Uid=<DB_USER>;Pwd=<DB_PASS>;Database=<DB_NAME>"
String dbSocketDir = Environment.GetEnvironmentVariable("DB_SOCKET_PATH") ?? "/cloudsql";
String instanceConnectionName = Environment.GetEnvironmentVariable("INSTANCE_CONNECTION_NAME");
var connectionString = new NpgsqlConnectionStringBuilder()
{
    // The Cloud SQL proxy provides encryption between the proxy and instance.
    SslMode = SslMode.Disable,
    // Remember - storing secrets in plain text is potentially unsafe. Consider using
    // something like https://cloud.google.com/secret-manager/docs/overview to help keep
    // secrets secret.
    Host = String.Format("{0}/{1}", dbSocketDir, instanceConnectionName),
    Username = Environment.GetEnvironmentVariable("DB_USER"), // e.g. 'my-db-user
    Password = Environment.GetEnvironmentVariable("DB_PASS"), // e.g. 'my-db-password'
    Database = Environment.GetEnvironmentVariable("DB_NAME"), // e.g. 'my-database'
};
connectionString.Pooling = true;
// Specify additional properties here.
return connectionString;

Go

Para ver esse snippet no contexto de um aplicativo da Web, consulte o README no GitHub (em inglês).

var (
	dbUser                 = mustGetenv("DB_USER")                  // e.g. 'my-db-user'
	dbPwd                  = mustGetenv("DB_PASS")                  // e.g. 'my-db-password'
	instanceConnectionName = mustGetenv("INSTANCE_CONNECTION_NAME") // e.g. 'project:region:instance'
	dbName                 = mustGetenv("DB_NAME")                  // e.g. 'my-database'
)

socketDir, isSet := os.LookupEnv("DB_SOCKET_DIR")
if !isSet {
	socketDir = "/cloudsql"
}

var dbURI string
dbURI = fmt.Sprintf("user=%s password=%s database=%s host=%s/%s", dbUser, dbPwd, dbName, socketDir, instanceConnectionName)

// dbPool is the pool of database connections.
dbPool, err := sql.Open("pgx", dbURI)
if err != nil {
	return nil, fmt.Errorf("sql.Open: %v", err)
}

// ...

return dbPool, nil

Ruby

Para ver esse snippet no contexto de um aplicativo da Web, consulte o README no GitHub (em inglês).

production:
  adapter: postgresql
  # Configure additional properties here.
  username: <%= ENV["DB_USER"] %>  # e.g. "my-database-user"
  password: <%= ENV["DB_PASS"] %> # e.g. "my-database-password"
  database: <%= ENV.fetch("DB_NAME") { "vote_production" } %>
  # If connecting via unix domain socket, specify the socket path as host
  host:  "<%= ENV.fetch("DB_SOCKET_DIR") { '/cloudsql' } %>/<%= ENV["INSTANCE_CONNECTION_NAME"] %>"

PHP

Para ver esse snippet no contexto de um aplicativo da Web, consulte o README no GitHub (em inglês).

// $username = 'your_db_user';
// $password = 'yoursupersecretpassword';
// $dbName = 'your_db_name';
// $connectionName = getenv("CLOUD_SQL_CONNECTION_NAME");
// $socketDir = getenv('DB_SOCKET_DIR') ?: '/cloudsql';

// Connect using UNIX sockets
$dsn = sprintf(
    'pgsql:dbname=%s;host=%s/%s',
    $dbName,
    $socketDir,
    $connectionName
);

// Connect to the database.
$conn = new PDO($dsn, $username, $password, $connConfig);

Como iniciar e encerrar conexões

Ao usar um pool de conexões, é necessário iniciá-las e encerrá-las corretamente. Assim, as conexões sempre serão retornadas ao pool quando você terminar de usá-las. Conexões não retornadas ou "vazadas" não são reutilizadas, o que gera desperdício de recursos e potenciais gargalos de desempenho para seu aplicativo.

Python

# Preparing a statement before hand can help protect against injections.
stmt = sqlalchemy.text(
    "INSERT INTO votes (time_cast, candidate)"
    " VALUES (:time_cast, :candidate)"
)
try:
    # Using a with statement ensures that the connection is always released
    # back into the pool at the end of statement (even if an error occurs)
    with db.connect() as conn:
        conn.execute(stmt, time_cast=time_cast, candidate=team)
except Exception as e:
    # If something goes wrong, handle the error in this section. This might
    # involve retrying or adjusting parameters depending on the situation.
    # ...

Java

// Using a try-with-resources statement ensures that the connection is always released back
// into the pool at the end of the statement (even if an error occurs)
try (Connection conn = pool.getConnection()) {

  // PreparedStatements can be more efficient and project against injections.
  String stmt = "INSERT INTO votes (time_cast, candidate) VALUES (?, ?);";
  try (PreparedStatement voteStmt = conn.prepareStatement(stmt);) {
    voteStmt.setTimestamp(1, now);
    voteStmt.setString(2, team);

    // Finally, execute the statement. If it fails, an error will be thrown.
    voteStmt.execute();
  }
} catch (SQLException ex) {
  // If something goes wrong, handle the error in this section. This might involve retrying or
  // adjusting parameters depending on the situation.
  // ...
}

Node.js

/**
 * Insert a vote record into the database.
 *
 * @param {object} pool The Knex connection object.
 * @param {object} vote The vote record to insert.
 * @returns {Promise}
 */
const insertVote = async (pool, vote) => {
  try {
    return await pool('votes').insert(vote);
  } catch (err) {
    throw Error(err);
  }
};

C#

insertTimestamp = DateTime.UtcNow;
try
{
    using(var connection = new NpgsqlConnection(_connectionString.ConnectionString))
    {
        connection.OpenWithRetry();
        using (var insertVoteCommand = connection.CreateCommand())
        {
            insertVoteCommand.CommandText =
                @"INSERT INTO votes (candidate, time_cast) VALUES (@candidate, @time_cast)";
            var candidate = insertVoteCommand.CreateParameter();
            candidate.ParameterName = "@candidate";
            candidate.DbType = DbType.String;
            candidate.Value = team;
            insertVoteCommand.Parameters.Add(candidate);
            var timeCast = insertVoteCommand.CreateParameter();
            timeCast.ParameterName = "@time_cast";
            timeCast.DbType = DbType.DateTime;
            timeCast.Value = insertTimestamp;
            insertVoteCommand.Parameters.Add(timeCast);
            await insertVoteCommand.ExecuteNonQueryAsync();
        }
    }
    return Content($"Vote successfully cast for '{team}' at time {insertTimestamp}!");
}
catch (Exception ex)
{
    // If something goes wrong, handle the error in this
    // section. This might involve retrying or adjusting
    // parameters depending on the situation.
    return StatusCode((int)HttpStatusCode.InternalServerError, ex);
}

Go

sqlInsert := "INSERT INTO votes(candidate, time_cast) VALUES($1, NOW())"
if team == "TABS" || team == "SPACES" {
	if _, err := app.db.Exec(sqlInsert, team); err != nil {
		fmt.Fprintf(w, "unable to save vote: %s", err)
		return fmt.Errorf("DB.Exec: %v", err)
	} else {
		fmt.Fprintf(w, "Vote successfully cast for %s!\n", team)
	}
}
return nil

Ruby

@vote = Vote.new candidate: candidate

# ActiveRecord creates and executes your SQL and automatically
# handles the opening and closing of the database connection.
if @vote.save
  render json: "Vote successfully cast for \"#{@vote.candidate}\" at #{@vote.time_cast} PST!"
else
  render json: @vote.errors, status: :unprocessable_entity
end

PHP

// Use prepared statements to guard against SQL injection.
$sql = "INSERT INTO votes (time_cast, candidate) VALUES (NOW(), :voteValue)";

try {
    $statement = $conn->prepare($sql);
    $statement->bindParam('voteValue', $value);

    $res = $statement->execute();
} catch (PDOException $e) {
    throw new RuntimeException(
        "Could not insert vote into database. The PDO exception was " .
        $e->getMessage(),
        $e->getCode(),
        $e
    );
}

Número de conexões

Cada conexão de banco de dados usa recursos do lado do cliente e do servidor. Além disso, o Cloud SQL aplica limites de conexão gerais que não podem ser excedidos. Criar e usar menos conexões reduz a sobrecarga e ajuda você a permanecer abaixo do limite de conexões.

Python

# Pool size is the maximum number of permanent connections to keep.
"pool_size": 5,
# Temporarily exceeds the set pool_size if no connections are available.
"max_overflow": 2,
# The total number of concurrent connections for your application will be
# a total of pool_size and max_overflow.

Java

// maximumPoolSize limits the total number of concurrent connections this pool will keep. Ideal
// values for this setting are highly variable on app design, infrastructure, and database.
config.setMaximumPoolSize(5);
// minimumIdle is the minimum number of idle connections Hikari maintains in the pool.
// Additional connections will be established to meet this value unless the pool is full.
config.setMinimumIdle(5);

Node.js

// 'max' limits the total number of concurrent connections this pool will keep. Ideal
// values for this setting are highly variable on app design, infrastructure, and database.
config.pool.max = 5;
// 'min' is the minimum number of idle connections Knex maintains in the pool.
// Additional connections will be established to meet this value unless the pool is full.
config.pool.min = 5;

C#

// MaxPoolSize sets maximum number of connections allowed in the pool.
connectionString.MaxPoolSize = 5;
// MinPoolSize sets the minimum number of connections in the pool.
connectionString.MinPoolSize = 0;

Go


// Set maximum number of connections in idle connection pool.
dbPool.SetMaxIdleConns(5)

// Set maximum number of open connections to the database.
dbPool.SetMaxOpenConns(7)

Ruby

# 'pool' is the maximum number of permanent connections to keep.
pool: 5

PHP

No momento, o PDO não oferece nenhuma funcionalidade para configurar limites de conexão.

Espera exponencial

Se o aplicativo tentar se conectar ao banco de dados e não conseguir, o banco de dados poderá estar temporariamente indisponível. Nesse caso, enviar muitas solicitações de conexão simultâneas pode desperdiçar recursos adicionais do banco de dados e aumentar o tempo necessário para a recuperação. O uso da espera exponencial impede que o aplicativo envie um número sem resposta de solicitações de conexão quando não puder se conectar ao banco de dados.

Essa nova tentativa só faz sentido em uma primeira conexão ou ao capturar uma conexão do pool pela primeira vez. Se ocorrerem erros no meio de uma transação, o aplicativo precisará tentar de novo e repetir a transação desde o início Portanto, mesmo que seu pool esteja configurado corretamente, o aplicativo ainda poderá ver erros se as conexões forem perdidas.

Python

# SQLAlchemy automatically uses delays between failed connection attempts,
# but provides no arguments for configuration.

Java

// Hikari automatically delays between failed connection attempts, eventually reaching a
// maximum delay of `connectionTimeout / 2` between attempts.

Node.js

// 'knex' uses a built-in retry strategy which does not implement backoff.
// 'createRetryIntervalMillis' is how long to idle after failed connection creation before trying again
config.createRetryIntervalMillis = 200; // 0.2 seconds

C#

Policy
    .Handle<NpgsqlException>()
    .WaitAndRetry(new[]
    {
        TimeSpan.FromSeconds(1),
        TimeSpan.FromSeconds(2),
        TimeSpan.FromSeconds(5)
    })
    .Execute(() => connection.Open());

Go

No momento, o pacote database/sql não oferece nenhuma funcionalidade para configurar a espera exponencial.

Ruby

# ActiveRecord automatically uses delays between failed connection attempts,
# but provides no arguments for configuration.

PHP

No momento, o PDO não oferece nenhuma funcionalidade para configurar a espera exponencial.

Tempo limite de conexão

Existem muitas razões para a falha de uma tentativa de conexão. A comunicação de rede nunca é garantida, e pode ser que o banco de dados esteja temporariamente indisponível. Verifique se o aplicativo é capaz de lidar com conexões interrompidas ou malsucedidas sem gerar erros.

Python

# 'pool_timeout' is the maximum number of seconds to wait when retrieving a
# new connection from the pool. After the specified amount of time, an
# exception will be thrown.
"pool_timeout": 30,  # 30 seconds

Java

// setConnectionTimeout is the maximum number of milliseconds to wait for a connection checkout.
// Any attempt to retrieve a connection from this pool that exceeds the set limit will throw an
// SQLException.
config.setConnectionTimeout(10000); // 10 seconds
// idleTimeout is the maximum amount of time a connection can sit in the pool. Connections that
// sit idle for this many milliseconds are retried if minimumIdle is exceeded.
config.setIdleTimeout(600000); // 10 minutes

Node.js

// 'acquireTimeoutMillis' is the number of milliseconds before a timeout occurs when acquiring a
// connection from the pool. This is slightly different from connectionTimeout, because acquiring
// a pool connection does not always involve making a new connection, and may include multiple retries.
// when making a connection
config.pool.acquireTimeoutMillis = 60000; // 60 seconds
// 'createTimeoutMillis` is the maximum number of milliseconds to wait trying to establish an
// initial connection before retrying.
// After acquireTimeoutMillis has passed, a timeout exception will be thrown.
config.createTimeoutMillis = 30000; // 30 seconds
// 'idleTimeoutMillis' is the number of milliseconds a connection must sit idle in the pool
// and not be checked out before it is automatically closed.
config.idleTimeoutMillis = 600000; // 10 minutes

C#

// Timeout sets the time to wait (in seconds) while
// trying to establish a connection before terminating the attempt.
connectionString.Timeout = 15;

Go

No momento, o pacote database/sql não oferece nenhuma função para configurar o tempo limite de conexão. O tempo limite é configurado no nível do driver.

Ruby

# 'timeout' is the maximum number of seconds to wait when retrieving a
# new connection from the pool. After the specified amount of time, an
# ActiveRecord::ConnectionTimeoutError will be raised.
timeout: 5000

PHP

// Here we set the connection timeout to five seconds and ask PDO to
// throw an exception if any errors occur.
$connConfig = [
    PDO::ATTR_TIMEOUT => 5,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
];

Duração da conexão

Limitar o tempo de vida de uma conexão pode ajudar a impedir o acúmulo de conexões abandonadas. Use o pool de conexões para limitar a vida útil da sua conexão.

Python

# 'pool_recycle' is the maximum number of seconds a connection can persist.
# Connections that live longer than the specified amount of time will be
# reestablished
"pool_recycle": 1800,  # 30 minutes

Java

// maxLifetime is the maximum possible lifetime of a connection in the pool. Connections that
// live longer than this many milliseconds will be closed and reestablished between uses. This
// value should be several minutes shorter than the database's timeout value to avoid unexpected
// terminations.
config.setMaxLifetime(1800000); // 30 minutes

Node.js

Atualmente, a biblioteca 'knex' do Node.js não oferece nenhuma funcionalidade para controlar a duração de uma conexão.

C#

// ConnectionIdleLifetime sets the time (in seconds) to wait before
// closing idle connections in the pool if the count of all
// connections exceeds MinPoolSize.
connectionString.ConnectionIdleLifetime = 300;

Go


// Set Maximum time (in seconds) that a connection can remain open.
dbPool.SetConnMaxLifetime(1800)

Ruby

No momento, o ActiveRecord não oferece nenhuma função para controlar a duração de uma conexão.

PHP

No momento, o PDO não oferece nenhuma função para controlar a duração de uma conexão.

Para ver o aplicativo completo, clique no link abaixo.

Python

Veja o aplicativo completo da linguagem de programação Python.

Java

Ver o aplicativo completo da linguagem de programação Java.

Node.js

Veja o aplicativo completo da linguagem de programação Node.js.

C#

Veja o aplicativo completo da linguagem de programação C#.

Go

Veja o aplicativo completo da linguagem de programação Go.

Ruby

Veja o aplicativo completoda linguagem de programação Ruby.

PHP

Veja o aplicativo completo da linguagem de programação PHP.

A seguir