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

# The SQLAlchemy engine will help manage interactions, including automatically
# managing a pool of connections to your database
db = sqlalchemy.create_engine(
    # Equivalent URL:
    # postgres+pg8000://<db_user>:<db_pass>@/<db_name>?unix_sock=/cloudsql/<cloud_sql_instance_name>/.s.PGSQL.5432
    sqlalchemy.engine.url.URL(
        drivername='postgres+pg8000',
        username=db_user,
        password=db_pass,
        database=db_name,
        query={
            'unix_sock': '/cloudsql/{}/.s.PGSQL.5432'.format(
                cloud_sql_connection_name)
        }
    ),
    # ... Specify additional properties here.
    # ...
)

Java

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

// 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"

// For Java users, the Cloud SQL JDBC Socket Factory can provide authenticated connections.
// See https://github.com/GoogleCloudPlatform/cloud-sql-jdbc-socket-factory for details.
config.addDataSourceProperty("socketFactory", "com.google.cloud.sql.postgres.SocketFactory");
config.addDataSourceProperty("cloudSqlInstance", CLOUD_SQL_CONNECTION_NAME);

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

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

Node.js

// Initialize Knex, a Node.js SQL query builder library with built-in connection pooling.
const connect = () => {
  // Configure which instance and what database user to connect with.
  // Remember - storing secrets in plaintext is potentially unsafe. Consider using
  // something like https://cloud.google.com/kms/ to help keep secrets secret.
  const config = {
    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'
  };

  config.host = `/cloudsql/${process.env.CLOUD_SQL_CONNECTION_NAME}`;

  // Establish a connection to the database
  const knex = Knex({
    client: 'pg',
    connection: config,
  });

  // ... Specify additional properties here.
  // ...
  return knex;
};

const knex = connect();

C#

var connectionString = new NpgsqlConnectionStringBuilder(
    Configuration["CloudSql:ConnectionString"])
// ConnectionString is set in appsettings.json formatted as follows
// depending upon where your app is running:
// Running Locally ConnectionString: 
// "Uid=aspnetuser;Pwd=;Host=127.0.0.1;Database=votes"
// Cloud Run ConnectionString: 
// "Server=/cloudsql/your-project-id:us-central1:instance-name;Uid=aspnetuser;Pwd=;Database=votes"
// App Engine ConnectionString: 
// "Uid=aspnetuser;Pwd=;Host=cloudsql;Database=votes"
{
    // Connecting to a local proxy that does not support ssl.
    SslMode = SslMode.Disable
};
connectionString.Pooling = true;
// ...
NpgsqlConnection connection =
    new NpgsqlConnection(connectionString.ConnectionString);

Ruby

development:
  adapter: postgresql
  # ...
  username: <%= ENV["DB_USER"] %>
  password: <%= ENV["DB_PASS"] %>
  database: <%= ENV.fetch("DB_NAME") { "vote_development" } %>
  # If connecting via unix domain socket, specify the socket path as host
  host:  '/cloudsql/<%= ENV["INSTANCE_CONNECTION_NAME"] %>'
  # If connecting via TCP, enter the IP and port instead
  # host: '127.0.0.1' # or '172.17.0.1' if deployed to GAE Flex
  # port: 5432

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

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} knex The Knex connection object.
 * @param {object} vote The vote record to insert.
 * @returns {Promise}
 */
const insertVote = async (knex, vote) => {
  try {
    return await knex('votes').insert(vote);
  } catch (err) {
    throw Error(err);
  }
};

C#

insertTimestamp = DateTime.Now;
try
{
    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);
}

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

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.

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.
knex.client.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.
knex.client.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;

Ruby

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

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.

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
knex.client.pool.createRetryIntervalMillis = 200; // 0.2 seconds

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(() => NewPostgreSqlConnection());

Ruby

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

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

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
knex.client.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.
knex.client.pool.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.
knex.client.pool.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;

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

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

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

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

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;

Ruby

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

Java

View the complete application for the Java programming language.

Node.js

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

C#

View the complete application for the C# programming language.

Ruby

View the complete application for the Ruby programming language.

What's next