[MUSIC PLAYING]
CHRISTIAN SCHALK: Hello, and welcome to "The Building
with AppSheet Series," where I discuss
building apps with AppSheet and Google technologies.
I'm Christian Schalk, a Google Developer advocate,
and in this episode, we'll show how
to use Cloud SQL in AppSheet.
Let's get started.
What is Cloud SQL?
Google Cloud SQL is a fully managed relational database
service in the Cloud.
Cloud SQL offers the leading database engines,
including MySQL, Postgres SQL, and SQL Server.
It also features automated provisioning,
backups, replication, encryption,
as well as 99.95% availability.
In addition to high reliability, Cloud SQL is also fully secure
and standards compliant.
As expected, it comes with tight integration with Google Cloud
and Workspace services, as well as
support for all standard connectors and drivers.
Accessing Cloud SQL is done via the Cloud Console
UI for full admin access and control of Cloud SQL instances,
a Cloud Shell terminal for command line
SQL access, and database connectors,
including JDBC, ODBC, and so on for the three
supported database engines.
The MySQL database Engine is also
compatible with the third party MySQL Workbench tool.
AppSheet can access Cloud SQL with its own connector
feature, which provides full read/write access to Cloud SQL
database tables and views.
To prepare Cloud SQL for use with AppSheet,
you'll do the following.
One, create or locate a MySQL 5.7 instance.
Ensure that its public IP address is set.
Then add authorized networks for AppSheet.
This will allow AppSheet to connect to your instance.
The AppSheet IP addresses are posted on the AppSheet site
and the link will also be provided in this presentation.
Two, create or locator a database user account.
Use the Allow Any Host option when creating a new account.
Three, create or locate a database
on your Cloud SQL instance.
When creating accept the default UTF 8 character set.
Here's a demo of how to set up Cloud SQL for use
with AppSheet.
OK.
So we're here in the Google Cloud platform console,
and I'm looking at the SQL Instances page here.
And you can see that I have two instances here,
but if I was starting from scratch
I would actually click on the Create Instance button,
and I would select the My SQL Database Engine.
And then here's where I can specify
the instance ID and password.
I would also make sure to select the MySQL 5.7 version,
and then everything else can be default.
Instead of creating one now, I'm going
to go ahead and hit Cancel.
And let's jump into this existing instance
that I've created before.
So here's the Overview page.
As you can see, the usage is reported here at the top.
But most importantly, you have my public IP address
that's set for this particular instance.
In order for it to actually work with AppSheet,
I would need to set up the authorized networks,
and so I've actually done that.
I've actually set up all the different IP addresses
that AppSheet provides to connect to third party
services.
So as you can see here, there is actually a published page here,
which provides all of the IP addresses that AppSheet has.
So I've gone through and added each one
of these as authorized networks to connect to my Cloud SQL
instance.
After that, I went in and I created a new user account
for AppSheet.
But typically you could just click Add New User Account
and set the username and accept all the defaults.
And then also, more importantly, I
would need to create a database.
So I have an existing database ready to go.
But I could also create one from scratch.
So I specified the database name,
and I can accept the remaining defaults.
All right, and so let's go back over to the Overview page
there.
And as I mentioned before, there is this Cloud Shell
that I can use to connect to the actual SQL command line.
So here we go.
We have our shell that's opening up,
and this usually takes a minute or two to get set up.
OK, so now all I have to do is provide the password
and then we're in.
So now we can use just good old fashioned SQL.
So I just need to specify my database that I've set up.
Here we go.
And then now we can say show tables,
and I've actually set up some tables prior to the demo here.
And so I have two tables.
I have Employees and Projects.
And this is a classical master detail relationship
between employees and projects.
That means for each employee, there are a number of projects.
So let's take a look at one of the tables here.
And there we go.
You can see that I actually have data in this table here.
All right.
Incidentally, as I mentioned before,
there is a MySQL Workbench tool that if I
want to make a connection to and manage my database,
I can easily do that.
So I've done that for this particular database.
And as you can see, I have my two tables
and I can even query some of the data from it.
There you go, so I can see the same data as before.
So as you can see, it's relatively straightforward
to set up a Cloud SQL instance to be connected from AppSheet.
When Cloud SQL is configured for AppSheet,
you can then set up AppSheet to connect to your Cloud SQL
instance.
In AppSheet's My Account Page, click New Data Source.
Name the new data source whatever you like,
and select Cloud Database as the data source type.
Then select MySQL as the database type
and set the following.
The server, which is the public IP
address of your instance, the database name, the username,
and password.
Also set the SSL mode to Required.
Once all the values are entered, click Test, and then click
Authorize Access.
Here's the demo of how to set up AppSheet to use Cloud SQL.
OK.
To set up AppSheet to use Cloud SQL,
I go to the My Account page, and I
would click on New Data Source.
And here's where I can specify the name,
and I would also select the Cloud Database option.
Now, since I already have one set up,
let me just jump to that one really quickly.
Here is my AppSheet Cloud SQL database,
or data source, that I've set up,
and here I can just set the-- or I can go into the same options
that I set up before, and I will type in a password,
set the SSL node to Required.
I'll test the connection, and we're good to go.
So I'll click Authorize Access.
Once the Cloud SQL data source has been added to your AppSheet
account, you can start building apps with the following steps.
First, create a new app and select
Start with your own data.
Then set the app name and click Choose Your Data.
Then select the data source that you set up before
for Cloud SQL.
You'll then select a table or view
from your Cloud SQL database.
And that's it.
AppSheet will generate an app for you
based on your chosen Cloud SQL table or view.
OK.
Returning back to AppSheet, let's go ahead
and create a new app based off of the data source
that we set up.
I'll start with my own data, and let's
name this app "Projects Apps."
And I'll click Choose Your Data, and I'll
specify the AppSheet, Cloud SQL, data source,
and let's open up the tables and we
can see that we have our tables that we set up before.
So I'll click on Employees, and that
will generate a new app based off of that single table.
OK, so here's our app just for the single table.
But what we're going to do is now
go back and add another table.
So I'll click on New Table.
And again, I specified the same data source.
And I select Projects, and let's add the table.
So now I have the two tables, but they're not yet linked.
So to do that, let's open up the columns of the Projects table,
and I can see this Employee ID column here
is just a typed number.
I'm going to change that over to type ref,
and then we can specify that this
is a reference to the Employees table.
And that's it.
So I'll click Done.
And I'll click Save.
And so now the two tables are linked.
So if I click on any one of these records,
or the employee records that is, I
can see that there are related projects
for each one of these employees.
All right?
So pretty straightforward.
So as you can see, it was pretty easy
once we set up our data source to connect to the back end
Cloud SQL database.
This concludes the "Using Google Cloud SQL in AppSheet" episode.
Stay tuned for more coverage of this important capability
in future episodes, and for more info on AppSheet,
Google Cloud, and Google Workspace,
check out the links in the description below.
And for more videos like this, make
sure you subscribe to this series so you don't miss out.
As always, thanks for watching.
[MUSIC PLAYING]