Introduction
In this post, you’ll learn to build a Java based Cloud Function that will connect to a Cloud SQL for SQL Server database using the Cloud SQL Connector for Java. This solution will help you learn to build event driven lightweight solutions for any stand-alone functionality with Cloud SQL database that respond to Cloud events without needing to manage a server or runtime environment.
What is Cloud SQL?
Cloud SQL is a fully-managed database service that makes it easy to set up, maintain, manage, and administer your relational databases on Google Cloud Platform. It has 99.95% availability and supports up to 64 TB of storage available, with the ability to automatically increase storage size as needed. For a list of detailed features, refer to the documentation here.
What is Cloud Functions?
Cloud Functions is a lightweight serverless compute solution for developers to create single-purpose, stand-alone functions that respond to Cloud events without needing to manage a server or runtime environment.
What you’ll build
You’ll write a Cloud Function in Java. The function:
Creates a Cloud SQL for SQL Server instanceConnects to a Cloud SQL for SQL Server Database instance using a Cloud SQL Connector methodCreates a table in the database
What you’ll learn
How to create a Cloud SQL for SQL Server instanceHow to access the Cloud Functions web UI in the Google Cloud ConsoleHow to create a Cloud FunctionHow to test a Cloud FunctionHow to connect to a Cloud SQL database instance (SQL Server) using JavaHow to run DDL operations on a Cloud SQL database using Java and Cloud SQL Connector method
Requirements
A browser, such as Chrome or FirefoxA Google Cloud Platform project that contains your Cloud SQL instanceThe next section has the list of steps to create a Cloud SQL for SQL Server instance
1. Create the Cloud SQL – SQL Server instance
From Google Cloud console, go to Cloud SQL Select SQL Server, and choose the SQL Server 2019 Standard as the Database version Choose from Development instance with default compute size and memory (you can change it later if needed) and make sure you leave the Public IP in the Networking section enabled
Go to Databases on the left side menu once the instance is created and create a database
Go to Users on the left side menu and configure a new user account (with a username and password) for the instance. You can alternatively use the default user as well.
2. Prepare code and create Cloud Function
Prepare Code
The Cloud Function code for connecting to a Cloud SQL database is available below. Some variable values depend on your Cloud SQL database configuration, and depend on your own database information. The function connects to SQL Server database using Cloud SQL Connector JDBC for SQL Server and creates a table in the database.
The Cloud Functions UI in the Cloud Console includes a text editor. You can copy/paste and edit the code there, or edit the code locally first, and then copy/paste it into the UI.
/pom.xml
You can find the pom.xml file in the repository: https://github.com/AbiramiSukumaran/CloudFunctions_CloudSQL/blob/main/pom.xml
The Java class for implementing the functionality:
https://github.com/AbiramiSukumaran/CloudFunctions_CloudSQL/blob/main/src/main/java/com/example/Example.java
/src/main/java/com/example/Example.java
3. Create Cloud Function
Create the function
1. In a browser, go to the Google Cloud Platform Console UI
2. Select Cloud Functions from the Navigation menu
3. Click CREATE FUNCTION on the button bar
4. Enter a name for the function
5. Select the HTTP trigger. (Make a note of the URL displayed beneath the trigger item. It will be in this format: https://REGION-PROJECT_ID.cloudfunctions.net/FUNCTION_NAME)
6. Under Authentication, select Allow unauthenticated invocations to make the function public in this example, to make it accessible to test from the browser by clicking the URL in Trigger tab of the Functions console
7. Expand the Runtime, Build and Connections Settings In Runtime service account, select a service account that has the Cloud SQL Client role
8. Click the NEXT button
9. Select Runtime : Java 11 for the runtime option
10. Select Inline editor for the source code option
11. Select the source code editor windows, delete the existing content for both pom.xml and Example.java, and replace them with your edited versions of the code above
12. Enter “ CF-CloudSQL” as the name of the Entry point
13. Click Deploy and wait while the function is created. The spinner stops spinning and a green check appears on the subsequent page when the function is ready to use
14. Add permission for “allUsers” principal to “Cloud Functions Invoker” role
4. Test the function
1. Click on the name of the function you created in the above steps
2. Select the TESTING link in the middle of the page
3. Select TEST THE FUNCTION
4. The result should appear. If the test fails, you’ll see a stack trace to help with debugging
5. In a browser, go to the URL that you saved earlier, when you created the function. If you forgot to save the URL, you can get it from the TRIGGER link
6. The ok result should appear in the browser as well
7. You can check for details in the “DETAILS” tab
8. You can check for log and console information in the “LOGS” tab
5. Clean up
To avoid incurring charges to your Google Cloud account for the resources used in this post, follow these steps.
Delete the Cloud SQL instance
1. Go to the Cloud SQL Instances page in the Google Cloud Console
2. Select the instance you created to open the Instance details page
3. In the icon bar at the top of the page, click Delete
4. In the Delete instance window, type the name of your instance, then click Delete to delete the instance. You cannot reuse an instance name for about 7 days after an instance is deleted
Delete the Cloud Function
1. Go to the Cloud Functions page in the Google Cloud Console
2. Select the three dots under Actions for your function and choose Delete
3. Confirm deletion by clicking the DELETE button
6. Congratulations
Congratulations, you’ve successfully created Cloud SQL – SQL Server instance and built a Cloud Function that works with Cloud SQL.
Specifically, you’ve created a Java Cloud Function that connects to and creates a table in a Cloud SQLfor SQL Server database instance using Cloud SQL connector method. This kind of function helps you build stand alone compute solutions for event-driven cloud use cases using Cloud SQL data that is handled by other enterprise applications. Now go ahead and extend this learning to implement an update triggered calculation in an existing table assuming that the data in the table is getting transactionally updated from multiple sources.
7. Before you go…
Check out some of these codelabs and resourceful reads.
Connecting to Cloud SQL: Compute Engine, Private IP and Cloud SQL ProxyIntroduction to Cloud SQL Insightshttps://codelabs.developers.google.com/codelabs/connecting-to-cloud-sql-with-cloud-functions#0Cloud Functions documentationCloud SQL documentation
https://cloud.google.com/sql/docs/sqlserver/connect-connectors
Cloud BlogRead More