Managing database connections

This page provides best practices and language-specific code samples to help you create applications that use Cloud SQL database connections effectively.

These samples are excerpts from a complete App Engine application available to you on GitHub. Learn more.

Connection pools

A connection pool is a cache of database connections that are shared and reused to improve connection latency and performance. When your application needs a database connection, it borrows one from its pool temporarily; when the application is finished with the connection, it returns the connection to the pool, where it can be reused the next time the application needs a database connection.

Python

# 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_host = os.environ["DB_HOST"]

# Extract host and port from environment variable DB_HOST
host_args = db_host.split(":")
db_hostname, db_port = host_args[0], int(host_args[1])

# The SQLAlchemy engine will help manage interactions, including automatically
# managing a pool of connections to your database
pool = sqlalchemy.create_engine(
    # Equivalent URL:
    # mssql+pyodbc://<db_user>:<db_pass>@/<host>:<port>/<db_name>?driver=ODBC+Driver+17+for+SQL+Server
    sqlalchemy.engine.url.URL(
        "mssql+pyodbc",
        username=db_user,
        password=db_pass,
        database=db_name,
        host=db_hostname,
        port=db_port,
        query={"driver": "ODBC Driver 17 for SQL Server"},
    ),
    # ... Specify additional properties here.
    # ...
)

Node.js

const createPool = async () => {
  let config = {pool: {}};
  config.user = process.env.DB_USER; // e.g. 'my-db-user'
  config.password = process.env.DB_PASS; // e.g. 'my-db-password'
  config.database = process.env.DB_NAME; // e.g. 'my-database'
  // set the server to '172.17.0.1' when connecting from App Engine Flex
  config.server = process.env.DEPLOYED ? '172.17.0.1' : '127.0.0.1';
  config.port = 1433;

  // ...
  return await mssql.connect(config);
};

C#

var connectionString = new SqlConnectionStringBuilder(
    Configuration["CloudSql:ConnectionString"])
// ConnectionString set in appsetings.json formatted as:
// "User Id=sqlserver;Password=;Server=cloudsql;Database=votes;"
{
    // Connecting to a local proxy that does not support ssl.
    Encrypt = false,
};
connectionString.Pooling = true;
// ...
DbConnection connection =
    new SqlConnection(connectionString.ConnectionString);

Go

var (
	dbUser    = mustGetenv("DB_USER")
	dbPwd     = mustGetenv("DB_PASS")
	dbTcpHost = mustGetenv("DB_TCP_HOST")
	dbPort    = mustGetenv("DB_PORT")
	dbName    = mustGetenv("DB_NAME")
)

var dbURI string
dbURI = fmt.Sprintf("server=%s;user id=%s;password=%s;port=%s;database=%s;", dbTcpHost, dbUser, dbPwd, dbPort, dbName)

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

// ...

return dbPool, nil

Ruby

development:
  adapter: sqlserver
  # 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_development" } %>
  host: <%= ENV.fetch("DB_HOST") { "127.0.0.1" }%> # '172.17.0.1' if deployed to GAE Flex
  port: <%= ENV.fetch("DB_PORT") { 1433 }%> 

PHP

// // $username = 'your_db_user';
// // $password = 'yoursupersecretpassword';
// // $dbName = 'your_db_name';
// // $hostname = "127.0.0.1";

$dsn = sprintf('sqlsrv:server=%s;Database=%s', $hostname, $dbName);

// Connect to the database.
// Here we set the connection timeout to five seconds and ask PDO to
// throw an exception if any errors occur.
$conn = new PDO($dsn, $username, $password, [
    PDO::ATTR_TIMEOUT => 5,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
]);

Opening and closing connections

When you use a connection pool, you must open and close connections properly, so that your connections are always returned to the pool when you are done with them. Unreturned or "leaked" connections are not reused, which wastes resources and can cause performance bottlenecks for your application.

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

Node.js

try {
  const stmt = 'INSERT INTO votes (time_cast, candidate) VALUES (@timestamp, @team)';
  // Using a prepared statement protects against SQL injection attacks.
  // When prepare is called, a single connection is acquired from the connection pool 
  // and all subsequent executions are executed exclusively on this connection.
  const ps = new mssql.PreparedStatement(pool);
  ps.input('timestamp', mssql.DateTime)
  ps.input('team', mssql.VarChar(6))
  await ps.prepare(stmt)
  await ps.execute({
    timestamp: timestamp,
    team: team
  })
  await ps.unprepare();
} catch (err) {
  // If something goes wrong, handle the error in this section. This might
  // involve retrying or adjusting parameters depending on the situation.
  // ...
}

C#

insertTimestamp = DateTime.Now;
try
{
    // Insert a vote for SPACE or TAB with a timestamp.
    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 (?, GETDATE())"
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, vote_value) VALUES (GETDATE(), :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
    );
}

Connection count

Every database connection uses client and server-side resources. In addition, Cloud SQL imposes overall connection limits that cannot be exceeded. Creating and using fewer connections reduces overhead and helps you stay under the connection limit.

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.

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 maintained in the pool.
// Additional connections will be established to meet this value unless the pool is full.
config.pool.min = 1;

C#

// MaximumPoolSize sets maximum number of connections allowed in the pool.            
connectionString.MaxPoolSize = 5;
// MinimumPoolSize 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

PDO currently doesn't offer any functionality to configure connection limits.

Exponential backoff

If your application attempts to connect to the database and does not succeed, the database could be temporarily unavailable. In this case, sending too many simultaneous connection requests might waste additional database resources and increase the time needed to recover. Using exponential backoff prevents your application from sending an unhealthy number of connection requests when it can't connect to the database.

This retry only makes sense when first connecting, or when first grabbing a connection from the pool. If errors happen in the middle of a transaction, the application must do the retrying, and it must retry from the beginning of a transaction. So even if your pool is configured properly, the application might still see errors if connections are lost.

Python

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

Node.js

// The node-mssql module uses a built-in retry strategy which does not implement backoff.
// 'createRetryIntervalMillis' is the number of milliseconds to wait in between retries.
config.pool.createRetryIntervalMillis = 200;

C#

var connection = Policy
    .HandleResult<DbConnection>(conn => conn.State != ConnectionState.Open)
    .WaitAndRetry(new[]
    {
        TimeSpan.FromSeconds(1),
        TimeSpan.FromSeconds(2),
        TimeSpan.FromSeconds(5)
    }, (result, timeSpan, retryCount, context) =>
    {
        // Log any warnings here.
    })
    .Execute(() => NewSqlServerConnection());

Go

The database/sql package currently doesn't offer any functionality to configure exponential backoff.

Ruby

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

PHP

PDO currently doesn't offer any functionality to configure exponential backoff.

Connection timeout

There are many reasons why a connection attempt might not succeed. Network communication is never guaranteed, and the database might be temporarily unable to respond. Your application should handle broken or unsuccessful connections gracefully.

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

Node.js

// 'connectionTimeout` is the maximum number of milliseconds to wait trying to establish an
// initial connection. After the specified amount of time, an exception will be thrown.
config.connectionTimeout = 30000;
// 'acquireTimeoutMillis' is the number of milliseconds before a timeout occurs when acquiring a 
// connection from the pool.
config.pool.acquireTimeoutMillis = 30000;
// '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.pool.idleTimeoutMillis = 600000,

C#

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

Go

The database/sql package currently doesn't offer any functionality to configure connection timeout. Timeout is configured at the driver level.

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.
$conn = new PDO($dsn, $username, $password, [
    PDO::ATTR_TIMEOUT => 5,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
]);

Connection duration

Limiting a connection's lifetime can help prevent abandoned connections from accumulating. You can use the connection pool to limit your connection lifetimes.

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

Node.js

The 'node-mssql' Node.js library currently doesn't offer any functionality to control the duration of a connection.

C#

// ADO.NET connection pooler removes a connection
// from the pool after it's been idle for approximately
// 4-8 minutes, or if the pooler detects that the
// connection with the server no longer exists.

Go


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

Ruby

ActiveRecord currently doesn't offer any functionality to control the duration of a connection.

PHP

PDO currently doesn't offer any functionality to control the duration of a connection.

To see the complete application, click the link below.

Python

View the complete application for the Python programming language.

Node.js

View the complete application for the Node.js programming language.

C#

View the complete application for the C# programming language.

Go

View the complete application for the Go programming language.

PHP

View the complete application for the PHP programming language.

What's next