Questa pagina fornisce best practice ed esempi di codice specifici per i vari linguaggi per aiutarti a creare applicazioni che utilizzano le connessioni ai database Cloud SQL in modo efficace.
Questi esempi sono estratti di un'applicazione App Engine completa a tua disposizione su GitHub. Scopri di più.
Pool di connessione
Un pool di connessioni è una cache di connessioni al database che vengono condivise e riutilizzate per migliorare la latenza e le prestazioni della connessione. Quando l'applicazione ha bisogno di una connessione al database, ne prende in prestito temporaneamente una dal pool; al termine dell'applicazione, la connessione restituisce la connessione al pool, dove può essere riutilizzata alla successiva applicazione della connessione al database.
Connetti con TCP
Python
Per vedere questo snippet nel contesto di un'applicazione web, consulta il file README su GitHub.
import os
import sqlalchemy
# connect_tcp_socket initializes a TCP connection pool
# for a Cloud SQL instance of MySQL.
def connect_tcp_socket() -> sqlalchemy.engine.base.Engine:
# Note: Saving credentials in environment variables is convenient, but not
# secure - consider a more secure solution such as
# Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
# keep secrets safe.
db_host = os.environ["INSTANCE_HOST"] # e.g. '127.0.0.1' ('172.17.0.1' if deployed to GAE Flex)
db_user = os.environ["DB_USER"] # e.g. 'my-db-user'
db_pass = os.environ["DB_PASS"] # e.g. 'my-db-password'
db_name = os.environ["DB_NAME"] # e.g. 'my-database'
db_port = os.environ["DB_PORT"] # e.g. 3306
connect_args = {}
pool = sqlalchemy.create_engine(
# Equivalent URL:
# mysql+pymysql://<db_user>:<db_pass>@<db_host>:<db_port>/<db_name>
sqlalchemy.engine.url.URL.create(
drivername="mysql+pymysql",
username=db_user,
password=db_pass,
host=db_host,
port=db_port,
database=db_name,
),
connect_args=connect_args,
# ...
)
return pool
Java
Per vedere questo snippet nel contesto di un'applicazione web, visualizza il file README su GitHub.
Nota:
- INSTANCE_CONNECTION_NAME deve essere rappresentata come <MY-PROJECT>:<INSTANCE-REGION>:<INSTANCE-NAME>
- L'uso dell'argomento ipTypes=PRIVATE impone a SocketFactory di connettersi con un IP privato associato a un'istanza
- Consulta i requisiti di versione di fabbrica del socket JDBC per il file pom.xml qui.
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import javax.sql.DataSource;
public class TcpConnectionPoolFactory extends ConnectionPoolFactory {
// Saving credentials in environment variables is convenient, but not secure - consider a more
// secure solution such as https://cloud.google.com/secret-manager/ to help keep secrets safe.
private static final String DB_USER = System.getenv("DB_USER");
private static final String DB_PASS = System.getenv("DB_PASS");
private static final String DB_NAME = System.getenv("DB_NAME");
private static final String INSTANCE_HOST = System.getenv("INSTANCE_HOST");
private static final String DB_PORT = System.getenv("DB_PORT");
public static DataSource createConnectionPool() {
// 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:mysql://<INSTANCE_HOST>:<DB_PORT>/<DB_NAME>?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:mysql://%s:%s/%s", INSTANCE_HOST, DB_PORT, DB_NAME));
config.setUsername(DB_USER); // e.g. "root", "mysql"
config.setPassword(DB_PASS); // e.g. "my-password"
// ... Specify additional connection properties here.
// ...
// Initialize the connection pool using the configuration object.
return new HikariDataSource(config);
}
}
Node.js
Per vedere questo snippet nel contesto di un'applicazione web, consulta il file README su GitHub.
const mysql = require('promise-mysql');
const fs = require('fs');
// createTcpPool initializes a TCP connection pool for a Cloud SQL
// instance of MySQL.
const createTcpPool = async config => {
// Note: Saving credentials in environment variables is convenient, but not
// secure - consider a more secure solution such as
// Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
// keep secrets safe.
const dbConfig = {
host: process.env.INSTANCE_HOST, // e.g. '127.0.0.1'
port: process.env.DB_PORT, // e.g. '3306'
user: process.env.DB_USER, // e.g. 'my-db-user'
password: process.env.DB_PASS, // e.g. 'my-db-password'
database: process.env.DB_NAME, // e.g. 'my-database'
// ... Specify additional properties here.
...config,
};
// Establish a connection to the database.
return mysql.createPool(dbConfig);
};
Go
Per vedere questo snippet nel contesto di un'applicazione web, consulta il file README su GitHub.
package cloudsql
import (
"crypto/tls"
"crypto/x509"
"database/sql"
"errors"
"fmt"
"io/ioutil"
"log"
"os"
"github.com/go-sql-driver/mysql"
)
// connectTCPSocket initializes a TCP connection pool for a Cloud SQL
// instance of MySQL.
func connectTCPSocket() (*sql.DB, error) {
mustGetenv := func(k string) string {
v := os.Getenv(k)
if v == "" {
log.Fatalf("Warning: %s environment variable not set.", k)
}
return v
}
// Note: Saving credentials in environment variables is convenient, but not
// secure - consider a more secure solution such as
// Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
// keep secrets safe.
var (
dbUser = mustGetenv("DB_USER") // e.g. 'my-db-user'
dbPwd = mustGetenv("DB_PASS") // e.g. 'my-db-password'
dbName = mustGetenv("DB_NAME") // e.g. 'my-database'
dbPort = mustGetenv("DB_PORT") // e.g. '3306'
dbTCPHost = mustGetenv("INSTANCE_HOST") // e.g. '127.0.0.1' ('172.17.0.1' if deployed to GAE Flex)
)
dbURI := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?parseTime=true",
dbUser, dbPwd, dbTCPHost, dbPort, dbName)
// dbPool is the pool of database connections.
dbPool, err := sql.Open("mysql", dbURI)
if err != nil {
return nil, fmt.Errorf("sql.Open: %v", err)
}
// ...
return dbPool, nil
}
C#
Per vedere questo snippet nel contesto di un'applicazione web, consulta il file README su GitHub.
using MySql.Data.MySqlClient;
using System;
namespace CloudSql
{
public class MySqlTcp
{
public static MySqlConnectionStringBuilder NewMysqlTCPConnectionString()
{
// Equivalent connection string:
// "Uid=<DB_USER>;Pwd=<DB_PASS>;Host=<INSTANCE_HOST>;Database=<DB_NAME>;"
var connectionString = new MySqlConnectionStringBuilder()
{
// The Cloud SQL proxy provides encryption between the proxy and instance.
SslMode = MySqlSslMode.None,
// Note: Saving credentials in environment variables is convenient, but not
// secure - consider a more secure solution such as
// Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
// keep secrets safe.
Server = Environment.GetEnvironmentVariable("INSTANCE_HOST"), // e.g. '127.0.0.1'
// Set Host to 'cloudsql' when deploying to App Engine Flexible environment
UserID = 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;
}
}
}
Ruby
Per vedere questo snippet nel contesto di un'applicazione web, consulta il file README su GitHub.
tcp: &tcp
adapter: mysql2
# Configure additional properties here
# Note: Saving credentials in environment variables is convenient, but not
# secure - consider a more secure solution such as
# Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
# keep secrets safe.
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("INSTANCE_HOST") { "127.0.0.1" }%>" # '172.17.0.1' if deployed to GAE Flex
port: <%= ENV.fetch("DB_PORT") { 3306 }%>
PHP
Per vedere questo snippet nel contesto di un'applicazione web, consulta il file README su GitHub.
namespace Google\Cloud\Samples\CloudSQL\MySQL;
use PDO;
use PDOException;
use RuntimeException;
use TypeError;
class DatabaseTcp
{
public static function initTcpDatabaseConnection(): PDO
{
try {
// Note: Saving credentials in environment variables is convenient, but not
// secure - consider a more secure solution such as
// Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
// keep secrets safe.
$username = getenv('DB_USER'); // e.g. 'your_db_user'
$password = getenv('DB_PASS'); // e.g. 'your_db_password'
$dbName = getenv('DB_NAME'); // e.g. 'your_db_name'
$instanceHost = getenv('INSTANCE_HOST'); // e.g. '127.0.0.1' ('172.17.0.1' for GAE Flex)
// Connect using TCP
$dsn = sprintf('mysql:dbname=%s;host=%s', $dbName, $instanceHost);
// Connect to the database
$conn = new PDO(
$dsn,
$username,
$password,
# ...
);
} catch (TypeError $e) {
throw new RuntimeException(
sprintf(
'Invalid or missing configuration! Make sure you have set ' .
'$username, $password, $dbName, and $instanceHost (for TCP mode). ' .
'The PHP error was %s',
$e->getMessage()
),
$e->getCode(),
$e
);
} catch (PDOException $e) {
throw new RuntimeException(
sprintf(
'Could not connect to the Cloud SQL Database. Check that ' .
'your username and password are correct, that the Cloud SQL ' .
'proxy is running, and that the database exists and is ready ' .
'for use. For more assistance, refer to %s. The PDO error was %s',
'https://cloud.google.com/sql/docs/mysql/connect-external-app',
$e->getMessage()
),
$e->getCode(),
$e
);
}
return $conn;
}
}
Connetti con prese Unix
Python
Per vedere questo snippet nel contesto di un'applicazione web, consulta il file README su GitHub.
import os
import sqlalchemy
# connect_unix_socket initializes a Unix socket connection pool for
# a Cloud SQL instance of MySQL.
def connect_unix_socket() -> sqlalchemy.engine.base.Engine:
# Note: Saving credentials in environment variables is convenient, but not
# secure - consider a more secure solution such as
# Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
# keep secrets safe.
db_user = os.environ["DB_USER"] # e.g. 'my-database-user'
db_pass = os.environ["DB_PASS"] # e.g. 'my-database-password'
db_name = os.environ["DB_NAME"] # e.g. 'my-database'
unix_socket_path = os.environ["INSTANCE_UNIX_SOCKET"] # e.g. '/cloudsql/project:region:instance'
pool = sqlalchemy.create_engine(
# Equivalent URL:
# mysql+pymysql://<db_user>:<db_pass>@/<db_name>?unix_socket=<socket_path>/<cloud_sql_instance_name>
sqlalchemy.engine.url.URL.create(
drivername="mysql+pymysql",
username=db_user,
password=db_pass,
database=db_name,
query={"unix_socket": unix_socket_path},
),
# ...
)
return pool
Java
Per vedere questo snippet nel contesto di un'applicazione web, consulta il file README su GitHub.
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import javax.sql.DataSource;
public class ConnectorConnectionPoolFactory extends ConnectionPoolFactory {
// Note: Saving credentials in environment variables is convenient, but not
// secure - consider a more secure solution such as
// Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
// keep secrets safe.
private static final String INSTANCE_CONNECTION_NAME =
System.getenv("INSTANCE_CONNECTION_NAME");
private static final String INSTANCE_UNIX_SOCKET = System.getenv("INSTANCE_UNIX_SOCKET");
private static final String DB_USER = System.getenv("DB_USER");
private static final String DB_PASS = System.getenv("DB_PASS");
private static final String DB_NAME = System.getenv("DB_NAME");
public static DataSource createConnectionPool() {
// 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:mysql:///<DB_NAME>?cloudSqlInstance=<INSTANCE_CONNECTION_NAME>&
// socketFactory=com.google.cloud.sql.mysql.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:mysql:///%s", DB_NAME));
config.setUsername(DB_USER); // e.g. "root", "mysql"
config.setPassword(DB_PASS); // e.g. "my-password"
config.addDataSourceProperty("socketFactory", "com.google.cloud.sql.mysql.SocketFactory");
config.addDataSourceProperty("cloudSqlInstance", INSTANCE_CONNECTION_NAME);
// Unix sockets are not natively supported in Java, so it is necessary to use the Cloud SQL
// Java Connector to connect. When setting INSTANCE_UNIX_SOCKET, the connector will
// call an external package that will enable Unix socket connections.
// Note: For Java users, the Cloud SQL Java Connector can provide authenticated connections
// which is usually preferable to using the Cloud SQL Proxy with Unix sockets.
// See https://github.com/GoogleCloudPlatform/cloud-sql-jdbc-socket-factory for details.
if (INSTANCE_UNIX_SOCKET != null) {
config.addDataSourceProperty("unixSocketPath", INSTANCE_UNIX_SOCKET);
}
// ... Specify additional connection properties here.
// ...
// Initialize the connection pool using the configuration object.
return new HikariDataSource(config);
}
}
Node.js
Per vedere questo snippet nel contesto di un'applicazione web, consulta il file README su GitHub.
const mysql = require('promise-mysql');
// createUnixSocketPool initializes a Unix socket connection pool for
// a Cloud SQL instance of MySQL.
const createUnixSocketPool = async config => {
// Note: Saving credentials in environment variables is convenient, but not
// secure - consider a more secure solution such as
// Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
// keep secrets safe.
return mysql.createPool({
user: process.env.DB_USER, // e.g. 'my-db-user'
password: process.env.DB_PASS, // e.g. 'my-db-password'
database: process.env.DB_NAME, // e.g. 'my-database'
socketPath: process.env.INSTANCE_UNIX_SOCKET, // e.g. '/cloudsql/project:region:instance'
// Specify additional properties here.
...config,
});
};
C#
Per vedere questo snippet nel contesto di un'applicazione web, consulta il file README su GitHub.
using MySql.Data.MySqlClient;
using System;
namespace CloudSql
{
public class MySqlUnix
{
public static MySqlConnectionStringBuilder NewMysqlUnixSocketConnectionString()
{
// Equivalent connection string:
// "Server=<INSTANCE_UNIX_SOCKET>;Uid=<DB_USER>;Pwd=<DB_PASS>;Database=<DB_NAME>;Protocol=unix"
var connectionString = new MySqlConnectionStringBuilder()
{
// The Cloud SQL proxy provides encryption between the proxy and instance.
SslMode = MySqlSslMode.None,
// Note: Saving credentials in environment variables is convenient, but not
// secure - consider a more secure solution such as
// Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
// keep secrets safe.
Server = Environment.GetEnvironmentVariable("INSTANCE_UNIX_SOCKET"), // e.g. '/cloudsql/project:region:instance'
UserID = 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'
ConnectionProtocol = MySqlConnectionProtocol.UnixSocket
};
connectionString.Pooling = true;
// Specify additional properties here.
return connectionString;
}
}
}
Go
Per vedere questo snippet nel contesto di un'applicazione web, consulta il file README su GitHub.
package cloudsql
import (
"database/sql"
"fmt"
"log"
"os"
_ "github.com/go-sql-driver/mysql"
)
// connectUnixSocket initializes a Unix socket connection pool for
// a Cloud SQL instance of MySQL.
func connectUnixSocket() (*sql.DB, error) {
mustGetenv := func(k string) string {
v := os.Getenv(k)
if v == "" {
log.Fatalf("Warning: %s environment variable not set.", k)
}
return v
}
// Note: Saving credentials in environment variables is convenient, but not
// secure - consider a more secure solution such as
// Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
// keep secrets safe.
var (
dbUser = mustGetenv("DB_USER") // e.g. 'my-db-user'
dbPwd = mustGetenv("DB_PASS") // e.g. 'my-db-password'
dbName = mustGetenv("DB_NAME") // e.g. 'my-database'
unixSocketPath = mustGetenv("INSTANCE_UNIX_SOCKET") // e.g. '/cloudsql/project:region:instance'
)
dbURI := fmt.Sprintf("%s:%s@unix(/%s)/%s?parseTime=true",
dbUser, dbPwd, unixSocketPath, dbName)
// dbPool is the pool of database connections.
dbPool, err := sql.Open("mysql", dbURI)
if err != nil {
return nil, fmt.Errorf("sql.Open: %v", err)
}
// ...
return dbPool, nil
}
Ruby
Per vedere questo snippet nel contesto di un'applicazione web, consulta il file README su GitHub.
unix: &unix
adapter: mysql2
# Configure additional properties here.
# Note: Saving credentials in environment variables is convenient, but not
# secure - consider a more secure solution such as
# Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
# keep secrets safe.
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" } %>
# Specify the Unix socket path as host
socket: "<%= ENV["INSTANCE_UNIX_SOCKET"] %>"
PHP
Per vedere questo snippet nel contesto di un'applicazione web, consulta il file README su GitHub.
namespace Google\Cloud\Samples\CloudSQL\MySQL;
use PDO;
use PDOException;
use RuntimeException;
use TypeError;
class DatabaseUnix
{
public static function initUnixDatabaseConnection(): PDO
{
try {
// Note: Saving credentials in environment variables is convenient, but not
// secure - consider a more secure solution such as
// Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
// keep secrets safe.
$username = getenv('DB_USER'); // e.g. 'your_db_user'
$password = getenv('DB_PASS'); // e.g. 'your_db_password'
$dbName = getenv('DB_NAME'); // e.g. 'your_db_name'
$instanceUnixSocket = getenv('INSTANCE_UNIX_SOCKET'); // e.g. '/cloudsql/project:region:instance'
// Connect using UNIX sockets
$dsn = sprintf(
'mysql:dbname=%s;unix_socket=%s',
$dbName,
$instanceUnixSocket
);
// Connect to the database.
$conn = new PDO(
$dsn,
$username,
$password,
# ...
);
} catch (TypeError $e) {
throw new RuntimeException(
sprintf(
'Invalid or missing configuration! Make sure you have set ' .
'$username, $password, $dbName, ' .
'and $instanceUnixSocket (for UNIX socket mode). ' .
'The PHP error was %s',
$e->getMessage()
),
(int) $e->getCode(),
$e
);
} catch (PDOException $e) {
throw new RuntimeException(
sprintf(
'Could not connect to the Cloud SQL Database. Check that ' .
'your username and password are correct, that the Cloud SQL ' .
'proxy is running, and that the database exists and is ready ' .
'for use. For more assistance, refer to %s. The PDO error was %s',
'https://cloud.google.com/sql/docs/mysql/connect-external-app',
$e->getMessage()
),
(int) $e->getCode(),
$e
);
}
return $conn;
}
}
Aprire e chiudere le connessioni
Quando utilizzi un pool di connessioni, devi aprire e chiudere correttamente le connessioni, in modo che queste vengano sempre restituite al pool quando hai terminato. Le connessioni non restituite o "con perdite di dati" non vengono riutilizzate, il che spreca risorse e può causare colli di bottiglia delle prestazioni per la tua applicazione.
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
try {
const stmt = 'INSERT INTO votes (time_cast, candidate) VALUES (?, ?)';
// Pool.query automatically checks out, uses, and releases a connection
// back into the pool, ensuring it is always returned successfully.
await pool.query(stmt, [timestamp, team]);
} catch (err) {
// If something goes wrong, handle the error in this section. This might
// involve retrying or adjusting parameters depending on the situation.
// ...
}
C#
using MySql.Data.MySqlClient;
using System;
namespace CloudSql
{
public class MySqlTcp
{
public static MySqlConnectionStringBuilder NewMysqlTCPConnectionString()
{
// Equivalent connection string:
// "Uid=<DB_USER>;Pwd=<DB_PASS>;Host=<INSTANCE_HOST>;Database=<DB_NAME>;"
var connectionString = new MySqlConnectionStringBuilder()
{
// The Cloud SQL proxy provides encryption between the proxy and instance.
SslMode = MySqlSslMode.None,
// Note: Saving credentials in environment variables is convenient, but not
// secure - consider a more secure solution such as
// Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
// keep secrets safe.
Server = Environment.GetEnvironmentVariable("INSTANCE_HOST"), // e.g. '127.0.0.1'
// Set Host to 'cloudsql' when deploying to App Engine Flexible environment
UserID = 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
insertVote := "INSERT INTO votes(candidate, created_at) VALUES(?, NOW())"
_, err := db.Exec(insertVote, team)
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
);
}
Conteggio connessioni
Ogni connessione al database utilizza risorse client e lato server. Inoltre, Cloud SQL impone limiti di connessione complessivi che non possono essere superati. La creazione e l'utilizzo di un minor numero di connessioni riduce i costi generali e ti aiuta a rimanere al di sotto del limite di connessioni.
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
// 'connectionLimit' is the maximum number of connections the pool is allowed
// to keep at once.
connectionLimit: 5,
C#
// MaximumPoolSize sets maximum number of connections allowed in the pool.
connectionString.MaximumPoolSize = 5;
// MinimumPoolSize sets the minimum number of connections in the pool.
connectionString.MinimumPoolSize = 0;
Go
// Set maximum number of connections in idle connection pool.
db.SetMaxIdleConns(5)
// Set maximum number of open connections to the database.
db.SetMaxOpenConns(7)
Ruby
# 'pool' is the maximum number of permanent connections to keep.
pool: 5
PHP
Al momento PDO non offre funzionalità per configurare i limiti di connessioni.
Backoff esponenziale
Se la tua applicazione tenta di connettersi al database e non riesce, il database potrebbe essere temporaneamente non disponibile. In questo caso, l'invio di troppe richieste di connessione simultanee potrebbe comportare lo spreco di risorse di database aggiuntive e l'aumento del tempo necessario per il ripristino. L'utilizzo di un backoff esponenziale impedisce all'applicazione di inviare un numero non reattivo di richieste di connessione quando non può connettersi al database.
Questo nuovo tentativo ha senso solo quando si connette per la prima volta o quando si acquisisce una connessione dal pool. Se si verificano errori durante una transazione, l'applicazione deve ripetere l'operazione e deve ripetere dall'inizio di una transazione. Quindi, anche se il pool è configurato correttamente, l'applicazione potrebbe comunque visualizzare errori in caso di perdita delle connessioni.
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
// The mysql module automatically uses exponential delays between failed
// connection attempts.
C#
Policy
.Handle<MySqlException>()
.WaitAndRetry(new[]
{
TimeSpan.FromSeconds(1),
TimeSpan.FromSeconds(2),
TimeSpan.FromSeconds(5)
})
.Execute(() => connection.Open());
Go
Il pacchetto database/sql non offre attualmente alcuna funzionalità per configurare il backoff esponenziale.
Ruby
# ActiveRecord automatically uses delays between failed connection attempts,
# but provides no arguments for configuration.
PHP
Al momento PDO non offre funzionalità per configurare il backoff esponenziale.
Timeout connessione
Esistono molti motivi per cui un tentativo di connessione potrebbe non riuscire. Le comunicazioni di rete non sono mai garantite e il database potrebbe essere temporaneamente incapace di rispondere. Assicurati che la tua applicazione gestisca le connessioni interrotte o non riuscite correttamente.
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
// 'connectTimeout' is the maximum number of milliseconds before a timeout
// occurs during the initial connection to the database.
connectTimeout: 10000, // 10 seconds
// 'acquireTimeout' is the maximum number of milliseconds to wait when
// checking out a connection from the pool before a timeout error occurs.
acquireTimeout: 10000, // 10 seconds
// 'waitForConnections' determines the pool's action when no connections are
// free. If true, the request will queued and a connection will be presented
// when ready. If false, the pool will call back with an error.
waitForConnections: true, // Default: true
// 'queueLimit' is the maximum number of requests for connections the pool
// will queue at once before returning an error. If 0, there is no limit.
queueLimit: 0, // Default: 0
C#
// ConnectionTimeout sets the time to wait (in seconds) while
// trying to establish a connection before terminating the attempt.
connectionString.ConnectionTimeout = 15;
Go
Il pacchetto database/sql non offre attualmente alcuna funzionalità per configurare il timeout della connessione. Il timeout è configurato a livello di 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.
[
PDO::ATTR_TIMEOUT => 5,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]
Terminare una connessione
Un utente mysql con il privilegio PROCESS
in Cloud SQL
è in grado di eseguire un'istruzione KILL
su una connessione di qualsiasi altro utente mysql
(tranne gli utenti amministrativi di Cloud SQL).
Puoi elencare le connessioni a un'istanza utilizzando il client mysql ed eseguendo il comando SHOW PROCESSLIST
. Usa Id
per terminare la connessione.
Ad esempio:
mysql> SHOW PROCESSLIST;
mysql> KILL 6;
Durata connessione
Limitare la durata di una connessione può contribuire a impedire l'accumulo di connessioni abbandonate. Puoi utilizzare il pool di connessioni per limitare la durata della connessione.
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
# re-established
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
La libreria 'mysql' Node.js al momento non offre alcuna funzionalità per controllare la durata di una connessione.
C#
// ConnectionLifeTime sets the lifetime of a pooled connection
// (in seconds) that a connection lives before it is destroyed
// and recreated. Connections that are returned to the pool are
// destroyed if it's been more than the number of seconds
// specified by ConnectionLifeTime since the connection was
// created. The default value is zero (0) which means the
// connection always returns to pool.
connectionString.ConnectionLifeTime = 1800; // 30 minutes
Go
// Set Maximum time (in seconds) that a connection can remain open.
db.SetConnMaxLifetime(1800 * time.Second)
Ruby
Attualmente ActiveRecord non offre alcuna funzionalità per controllare la durata di una connessione.
PHP
Al momento PDO non offre funzionalità per controllare la durata di una connessione.
Visualizza la domanda completa
Per visualizzare la domanda completa, fai clic sul link di seguito.
Python
Visualizza l'applicazione completa per il linguaggio di programmazione Python.
Java
Visualizza l'applicazione completa per il linguaggio di programmazione Java.
Node.js
Visualizza l'applicazione completa per il linguaggio di programmazione Node.js.
C#
Visualizza l'applicazione completa per il linguaggio di programmazione C#.
Go
Visualizza l'applicazione completa per il linguaggio di programmazione Go.
Ruby
Visualizza l'applicazione completa per il linguaggio di programmazione Ruby.
PHP
Visualizza l'applicazione completa per il linguaggio di programmazione PHP.
Passaggi successivi
- Scopri di più sull'IP privato.
- Scopri le quote e i limiti per Cloud SQL e App Engine.
- Scopri le best practice per l'uso di Cloud SQL.
- Scopri di più sulla connessione da un'applicazione esterna.