Skip to content
Learni
View all tutorials
Google Cloud Platform

How to Create a Cloud SQL Instance in 2026

12 minBEGINNER
PostgreSQLPythonGoogle Cloudgcloud CLICloud SQL
Lire en français

Introduction

Cloud SQL is Google Cloud Platform's (GCP) managed relational database service, supporting PostgreSQL, MySQL, and SQL Server. Unlike self-hosted databases, Cloud SQL automatically handles daily backups, security updates, high availability, and vertical/horizontal scaling, freeing developers from admin tasks. In 2026, with the latest AI optimizations for queries and native integration with AlloyDB, it's the ideal tool for scalable apps like Next.js APIs or analytics dashboards.

This beginner tutorial guides you step by step: creating a PostgreSQL instance via gcloud CLI, secure configuration (controlled IP access, dedicated user), and Python connection for basic CRUD. At the end, you'll have a production-ready DB in 15 minutes. Why is this crucial? 80% of DB breaches come from faulty configs – here, we prioritize security from the start.

Prerequisites

  • A free Google Cloud account (with $300 credit offered).
  • gcloud CLI installed (version 480+ recommended in 2026).
  • Python 3.12+ installed locally.
  • An existing GCP project (or create one at console.cloud.google.com).
  • Basic terminal and SQL knowledge (SELECT/INSERT).

1. Authenticate gcloud and set the project

setup-gcloud.sh
# Replace 'my-project-id' with your GCP Project ID
gcloud auth login

gcloud config set project my-project-id

gcloud services enable sqladmin.googleapis.com

gcloud sql instances list

This sequence authenticates your GCP account, enables the Cloud SQL API, and lists existing instances (should be empty). Use your real Project ID from console.cloud.google.com. Common pitfall: Forgetting to enable the API causes 'PERMISSION_DENIED' – always check with gcloud services list afterward.

Creating the PostgreSQL Instance

We'll create a PostgreSQL 16 instance (stable in 2026), with 1 vCPU, 4 GB RAM, and a 10 GB disk – perfect for getting started. The instance is private by default; we'll open it publicly for this local example (in production, use VPC/Private Service Connect).

2. Create the Cloud SQL Instance

create-instance.sh
gcloud sql instances create ma-instance-sql \
    --database-version=POSTGRES_16 \
    --cpu=1 \
    --memory=4GiB \
    --region=us-central1 \
    --zone=us-central1-a

INSTANCE_IP=$(gcloud sql instances describe ma-instance-sql --format='value(ipAddresses[0].ipAddress)')
echo "Public IP of the instance: $INSTANCE_IP"

This command deploys an instance named 'ma-instance-sql' in 2-5 minutes. We capture the public IP for later use. Analogy: Like renting a dedicated DB server without installing PostgreSQL. Avoid distant regions to minimize latency; test with gcloud sql instances describe ma-instance-sql.

3. Authorize IP access and create DB/user

configure-access.sh
# WARNING: 0.0.0.0/0 opens to all internet – in prod, replace with YOUR_IP/32
gcloud sql instances patch ma-instance-sql --authorized-networks=0.0.0.0/0

gcloud sql databases create ma-base-test --instance=ma-instance-sql

gcloud sql users create monuser --instance=ma-instance-sql --password=MonMotDePasseSecure123!

This opens public access (temporarily), creates the 'ma-base-test' DB, and the 'monuser' user with a strong password. Change the authorized IP using curl ifconfig.me for security. Pitfall: Weak passwords cause 30% of hacks – use at least 12 alphanumeric characters.

Connecting and Testing from Python

Now, let's connect locally using psycopg2, a lightweight PostgreSQL library. The following script is complete and copy-paste ready: it creates a users table, inserts data, and queries for verification. Replace the IP, user, and password with your values.

4. Install psycopg2

install-deps.sh
pip install psycopg2-binary

psycopg2-binary includes compiled binaries, avoiding C dependency errors on Windows/Mac. No local PostgreSQL needed. Verify with pip list | grep psycopg.

5. Complete Python Script: CRUD on Cloud SQL

connect_cloudsql.py
import psycopg2
from psycopg2 import sql

# Replace with your values
DB_HOST = 'YOUR_PUBLIC_IP'  # Ex: 34.123.45.67
dbname = 'ma-base-test'
username = 'monuser'
password = 'MonMotDePasseSecure123!'

try:
    conn = psycopg2.connect(
        host=DB_HOST,
        database=dbname,
        user=username,
        password=password
    )
    cur = conn.cursor()

    # Create table
    cur.execute('''
        CREATE TABLE IF NOT EXISTS users (
            id SERIAL PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            email VARCHAR(100) UNIQUE NOT NULL
        )
    ''')

    # Insert data
    cur.execute(
        "INSERT INTO users (name, email) VALUES (%s, %s) RETURNING id",
        ('Alice', 'alice@example.com')
    )
    user_id = cur.fetchone()[0]

    # Query
    cur.execute("SELECT * FROM users WHERE id = %s", (user_id,))
    print("User created:", cur.fetchone())

    conn.commit()
except Exception as e:
    print(f"Error: {e}")
finally:
    if 'conn' in locals():
        cur.close()
        conn.close()
        print("Connection closed.")

This script handles connection, table creation, INSERT with RETURNING (for auto ID), parameterized SELECT (anti-SQL injection), and commit. Try/finally ensures clean closure. Analogy: Like a safe – locks (params) protect against injections. Test: python connect_cloudsql.py should display the user.

6. Cleanup: Delete the Instance (Optional)

cleanup.sh
gcloud sql instances delete ma-instance-sql --quiet

Deletes the instance and all associated costs (billed by the second). --quiet skips confirmation. Always clean up after tests to avoid surprise bills – GCP charges ~$0.10/hour for this config.

Best Practices

  • Use Private IP: Avoid public IP in production; set up VPC peering or Cloud SQL Auth Proxy for secure internal connections.
  • Secrets Management: Store passwords in Secret Manager, not hardcoded (inject via env vars).
  • Automated Backups: Enable PITR (Point-In-Time Recovery) on creation with --backup-start-time.
  • Monitoring: Integrate Cloud Monitoring for alerts on CPU >80% or slow connections.
  • Scaling: Start small, upgrade CPU/memory via gcloud sql instances patch --cpu=2 with no downtime.

Common Errors to Avoid

  • Unauthorized IP: 'Connection refused'? Check authorized-networks with gcloud sql instances describe and add your public IP.
  • API Not Enabled: 'Quota exceeded' for no reason? Run gcloud services enable sqladmin.googleapis.com.
  • Weak Password: Cloud SQL rejects <8 chars; use generators like pwgen 16 1.
  • No finally() in Python: Orphaned connections exhaust quotas (max 100 per instance).

Next Steps

Master Cloud SQL for production with our GCP DevOps training. Resources: Cloud SQL Docs, Cloud SQL Proxy, AlloyDB tutorial for AI workloads. Integrate with Kubernetes via official operators.