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 set in appsetings.json formatted as:
// "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);

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.
  PreparedStatement voteStmt = conn.prepareStatement(
      "INSERT INTO votes (time_cast, candidate) VALUES (?, ?);");
  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);
}

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;

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

// '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());

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 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.
knex.client.pool.createTimeoutMillis = 30000; // 30 seconds
// 'idleTimeoutMillis' is the maximum amount of time a connection can sit in the pool. Connections that
// sit idle for this many milliseconds are retried if idleTimeoutMillis is exceeded.
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;

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

  // 'acquireTimeoutMillis' 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.
  knex.client.pool.acquireTimeoutMillis = 600000; // 10 minutes

  return knex;
};

const knex = connect();

/**
 * 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);
  }
};

/**
 * Retrieve the latest 5 vote records from the database.
 *
 * @param {object} knex The Knex connection object.
 * @returns {Promise}
 */
const getVotes = async (knex) => {
  return await knex
    .select('candidate', 'time_cast')
    .from('votes')
    .orderBy('time_cast', 'desc')
    .limit(5);
};

/**
 * Retrieve the total count of records for a given candidate
 * from the database.
 *
 * @param {object} knex The Knex connection object.
 * @param {object} candidate The candidate for which to get the total vote count
 * @returns {Promise}
 */
const getVoteCount = async (knex, candidate) => {
  return await knex('votes').count('vote_id').where('candidate', candidate);
};

app.get('/', (req, res) => {
  (async function () {
    // Query the total count of "TABS" from the database.
    const tabsResult = await getVoteCount(knex, 'TABS');
    const tabsTotalVotes = parseInt(tabsResult[0].count);
    // Query the total count of "SPACES" from the database.
    const spacesResult = await getVoteCount(knex, 'SPACES');
    const spacesTotalVotes = parseInt(spacesResult[0].count);
    // Query the last 5 votes from the database.
    const votes = await getVotes(knex);
    // Calculate and set leader values.
    let leadTeam = '';
    let voteDiff = 0;
    let leaderMessage = '';
    if (tabsTotalVotes !== spacesTotalVotes) {
      if (tabsTotalVotes > spacesTotalVotes) {
        leadTeam = 'TABS';
        voteDiff = tabsTotalVotes - spacesTotalVotes;
      } else {
        leadTeam = 'SPACES';
        voteDiff = spacesTotalVotes - tabsTotalVotes;
      }
      leaderMessage = `${leadTeam} are winning by ${voteDiff} vote${
        voteDiff > 1 ? 's' : ''
      }.`;
    } else {
      leaderMessage = 'TABS and SPACES are evenly matched!';
    }
    res.render('index.pug', {
      votes: votes,
      tabsCount: tabsTotalVotes,
      spacesCount: spacesTotalVotes,
      leadTeam: leadTeam,
      voteDiff: voteDiff,
      leaderMessage: leaderMessage,
    });
  })();
});

app.post('/', async (req, res) => {
  // Get the team from the request and record the time of the vote.
  const {team} = req.body;
  const timestamp = new Date();

  if (!team || (team !== 'TABS' && team !== 'SPACES')) {
    res.status(400).send('Invalid team specified.').end();
    return;
  }

  // Create a vote record to be stored in the database.
  const vote = {
    candidate: team,
    time_cast: timestamp,
  };

  // Save the data to the database.
  try {
    await insertVote(knex, vote);
  } catch (err) {
    logger.error(`Error while attempting to submit vote:${err}`);
    res
      .status(500)
      .send('Unable to cast vote; see logs for more details.')
      .end();
    return;
  }
  res.status(200).send(`Successfully voted for ${team} at ${timestamp}`).end();
});

const PORT = process.env.PORT || 8080;
const server = app.listen(PORT, () => {
  console.log(`App listening on port ${PORT}`);
  console.log('Press Ctrl+C to quit.');
});

module.exports = server;

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;

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.

What's next