Connecting to Cloud SQL from App Engine

This page contains information and examples for connecting to a Cloud SQL instance from a service running in App Engine.

Cloud SQL is a fully-managed database service that makes it easy to set up, maintain, manage, and administer your relational PostgreSQL and MySQL databases in the cloud.

App Engine is a fully managed, serverless platform for developing and hosting web applications at scale. You can choose from several popular languages, libraries, and frameworks to develop your apps, then let App Engine take care of provisioning servers and scaling your app instances based on demand.

Setting up a Cloud SQL instance

  1. Create a Cloud SQL for PostgreSQL instance.

  2. Find the INSTANCE_CONNECTION_NAME for the instance on the Instance details page. It uses the format PROJECT_ID:REGION:INSTANCE_ID, and is used to identify the Cloud SQL instance you are connecting to.

  3. Enable the Cloud SQL Admin API, if you haven't already done so:

    Enable the API

Configuring App Engine

Standard

App Engine does not require any special configuration beyond making sure the service account that you use has the correct permissions.

Flexible

Update your project's app.yaml file with the option that works best. You can use a comma-separated list of instances to specify multiple options at once.

Enabling a Unix domain socket

To enable a Unix domain socket, add the following to your project's app.yaml file:

beta_settings:
  cloud_sql_instances: <INSTANCE_CONNECTION_NAME>

Enabling a TCP Port

To enable a local TCP port, add the following to your project's app.yaml file:

beta_settings:
  cloud_sql_instances: <INSTANCE_CONNECTION_NAME>=tcp:<PORT>

App Engine uses a service account to authorize your connections to Cloud SQL. This service account must have the correct IAM permissions to successfully connect. Unless otherwise configured, the default service account is in the format service-PROJECT_ID@gae-api-prod.google.com.iam.gserviceaccount.com.

When connecting resources in two different projects, make sure that both projects have enabled the correct IAM roles and have given the service account the correct permissions.

Ensure that the service account for your service has one of the following IAM roles:

  • Cloud SQL Client (preferred)
  • Cloud SQL Editor
  • Cloud SQL Admin

Or, you can manually assign the following IAM permissions:

  • cloudsql.instances.connect
  • cloudsql.instances.get

For detailed instructions on adding IAM roles to a service account, see Granting Roles to Service Accounts.

Connecting to Cloud SQL

Once correctly configured, you can connect your service to your Cloud SQL instance using the Unix domain socket located at /cloudsql/INSTANCE_CONNECTION_NAME. These connections are automatically encrypted without any additional configuration.

App Engine flexible environment also supports connecting through TCP. If you have configured an instance with a TCP port, configure your application to connect to 172.17.0.1:PORT instead.

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.
    # ...
)
To see this snippet in the context of a web application, view the source code on GitHub.

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);
To see this snippet in the context of a web application, view the source code on GitHub.

PHP

// $username = 'your_db_user';
// $password = 'yoursupersecretpassword';
// $schema = 'your_db_name';
// $cloud_sql_connection_name = 'Your Cloud SQL Connection name';

if ($cloud_sql_connection_name) {
    // Connect using UNIX sockets
    $dsn = sprintf(
        'pgsql:dbname=%s;host=/cloudsql/%s',
        $schema,
        $cloud_sql_connection_name
    );
} else {
    // Connect using TCP
    // $hostname = '127.0.0.1';
    $dsn = sprintf('pgsql:dbname=%s;host=%s', $schema, $hostname);
}

return new PDO($dsn, $username, $password);
To see this snippet in the context of a web application, view the source code on GitHub.

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);
To see this snippet in the context of a web application, view the source code on GitHub.

Best Practices & Other Information

You can use the Cloud SQL proxy when testing your application locally. See the quickstart for using the proxy for local testing for detailed instructions.

Connection Pools

Connections to underlying databases may be dropped, either by the database server itself, or by the underlying infrastructure. To mitigate this, we recommend that you use a client library that supports connection pools and automatic reconnection.

For more detailed examples on how to use connection pools, see Managing database connections.

Connection Limits

Cloud SQL imposes a maximum limit on concurrent connections, and these limits may vary depending on the database engine chosen (see Cloud SQL Quotas and Limits).

App Engine has the ability to automatically create more intances as load increases, which may cause you to exceed these limits. To avoid this issue, limit the maximum number of App Engine instances. For more information, see Scaling elements.

You can limit the maximum number of connections used per instance by using a connection pool. For more detailed examples on how to limit the number of connections, see the Managing database connections page.

Оцените, насколько информация на этой странице была вам полезна:

Оставить отзыв о...

Текущей странице
Cloud SQL for PostgreSQL
Нужна помощь? Обратитесь в службу поддержки.