Google Cloud SQL connection to flutter

Question

I'm on the internet for 3 days trying to find something which would help me to set up PostreSQL server on Google Cloud and my flutter app. There is absolutely nothing neither in documentation or anywhere on the internet for flutter app how to connect it, set it up, or even do authentication without firebase. I tried to get help in console support page and it directed me here which I understand is not the best question for SO but I got no other option.. So can anyone help me with it or is flutter only design to work with firebase or is google cloud not ready for flutter yet?

Answer 1

I will give a more detailed explanation for those who are starting from scratch, like me, with these services.

First you have to know that Cloud Functions is a service provided by Google Cloud Platform to perform certain specific functions on its servers such as making an SQL request which we will call through API.

Cloud SQL is another Google Cloud Platform (GCP) service that allows us to create a database with PostgreSQL, MySQL and SQL Service.

To connect Flutter with the database hosted in Cloud SQL we will use Cloud Functions. We will make a Python function executed in Cloud Functions to connect to the database hosted in Cloud SQL and make requests to the database using this code, this function created with Cloud Functions gives us the Cloud Function API to make requests to the function, so we will configure this function to make all the queries we want to the database, these will not be made directly from Flutter. We will use this API in Flutter to make the queries we want indirectly. Here it is explained with a simple example, then it is easy to continue implementing it.

The step by step of how Cloud Functions works and what it is is super detailed in this documentation for those who want to get an idea of ​​what this service is about and do not like to follow steps without knowing what it does. Basically it is an environment of Google servers that executes code on their computers.

Since we need to connect the Cloud SQL database to Cloud Functions, to generate the API of the function that will actually make the requests to the database, we are going to make this super didactic step thanks to the colleague who made this video that explains to us how to create the database instance as cheaply as possible.

I will say two notes about the video that I have changed and it works for me:

1. I have used a way to connect the database to the Cloud Function with the library recommended by Google Cloud called "google.cloud.sql.connector". Therefore my code is as it is at the end of the answer. Please, do not make the same mistake as I did; db_name is not the name of the Cloud SQL instance, it is the name of one of the databases on the instance.

2. In the video he uses the 1st generation of Google Functions. If we use the 2nd generation, it is done exactly the same with the advantage that we have more servers to host the function.

Finally, to connect the API of the function (the generated URL) we simply use any Flutter library to call the API and get a response.

import os
import sqlalchemy
from google.cloud.sql.connector import Connector

# Function to connect to the database
def connect_to_db():
    # Create a Cloud SQL connector
    connector = Connector()
    
    # Cloud SQL instance connection name
    instance_connection_name = "project:region:instance"
    
    # Database credentials
    db_user = "User"
    db_pass = os.environ["DB_PASS"]
    db_name = "DataBaseName_notTheInstanceNamePlease"

    # Function to obtain the connection
    def getconn():
        # Connect to the Cloud SQL instance using the connector
        conn = connector.connect(
            instance_connection_name,
            "pymysql",
            user=db_user,
            password=db_pass,
            db=db_name
        )
        return conn

    # Create a connection pool using SQLAlchemy
    pool = sqlalchemy.create_engine(
        "mysql+pymysql://",
        creator=getconn,
    )
    return pool

# Main function to be executed in Cloud Functions
def execute_query(request):
    # Connect to the database
    pool = connect_to_db()
    
    # Execute a query on the database
    with pool.connect() as conn:
        result = conn.execute(sqlalchemy.text("SELECT * FROM supermarket;"))
        
        # Fetch all the results from the query
        rows = result.fetchall()
        
        # Return the results as a string
        return str(rows)

Remember to customize the code to the needs of your database, something explained in the video, since even if the code is different, the connection concept is the same.

The requirements.txt file looks like:

cloud-sql-python-connector
pymysql
sqlalchemy

Flutter example to connect to API:

import 'package:http/http.dart' as http; ///Realiza solicitudes HTTP
Future<Locations> getSupers() async {

  ///Petición a la base de datos de la lista de todos los supermercador. El numero de consulta correspondiente es el 1.
  ///
  ///_____________________________________________________________________________________
  ///
  const googleLocationsURL = 'Your URL API';
  try {
    final response = await http.post(
      Uri.parse(googleLocationsURL),
      headers: {'Content-Type': 'application/json'},
      body: json.encode({"enquiry": 1}),
    );

    if (response.statusCode == 200) {
      return Locations.fromJson(json.decode(response.body)); ///Pasa los datos descargados a una localizacion
    } else {
      throw Exception('Failed to load locations');
    }
  } catch (e) {
    print(e);
  }
  ///
  ///_____________________________________________________________________________________
  ///

  /// Fallback for when the above HTTP request fails.
  return Locations.fromJson(
    json.decode(
      await rootBundle.loadString('assets/locations.json'),
    ),
  );


}

Answer 2

Since SQL Databases should never be accessed over the internet directly, it would be a good idea to have a web endpoint which exposes a limited API, sending http requests for the operations wanted. One way to approach this would be to have your Flutter app trigger Cloud Functions, which would then connect to Cloud SQL (the managed PostgreSQL service on GCP).

Here is documentation on how to connect Cloud Functions to Cloud SQL. Finally, here is an external blog post on how to use Flutter with Cloud Functions. Please note that we cannot guarantee the accuracy of external information, and it should serve as a reference to get you started.