One of the most popular Xojo Cloud feature requests was to add database support. On Tuesday Feb 10th, we added MySQL and Postgres databases for Xojo Cloud. In addition to having a database server right on your Xojo Cloud server, we have also enabled the creation of SSH Tunnels so you can connect directly to the databases using a 3rd party management tool.
Adding a Database
Adding a database server to your Xojo Cloud server is easy! Just go to your Xojo Cloud control panel and click “Enable MySQL” or “Enable PgSQL.” In a moment, you will be presented with a username and password. That’s it!
Connecting to a Database from a Xojo Cloud app:
Connecting to the database server is just as easy:
Dim db as new PostgreSQLDatabase // (or MySQLDatabase) db.username = "username" db.password = "password" db.host = "localhost" // Use "127.0.0.1" for MySQLDatabase db.port = 5432 // 3306 for MySQL db.databasename = "your database name" if db.connect then // You're connected! end if
Note the entry for Host. Your database is only accessible from the server itself. Even if you open a port in the firewall, neither of the database engines (MySQL or PostgreSQL) are listening on the addresses for the server’s IP addresses.
Connecting to a Database from Somewhere Else
Up to now, everything’s been very straightforward, but what happens if you need to access the database from your local machine for testing? What if you want to use a database management tool like pgAdmin, MySQL Workbench or any of the many commercial management tools out there? This is where the power of SSH tunnels come into play.
Before I show you how to make an SSH tunnel, lets talk a little about what they are. An SSH tunnel is a secure connection, between your computer and another (in this case your Xojo Cloud server) which is used for transmitting data on a particular port. In essence, it allows us to create a private connection through the internet which allows you to see into your server on the other end and connect to a particular service there.
To create a tunnel between your computer and your Xojo Cloud server, you first need to turn on the tunnel capability on your server. Go to your control panel and click the “Enable Tunnel” button. You will be presented with a username and a password which you will use later for establishing the connection.
Connecting a Database Management Tool
Most of the database server management tools have the capability to connect using an SSH tunnel. In the dialog used to set up the connection, look for something that says SSH or SSH Tunnel. In most cases, you will need to fill out four fields:
- Host Name or IP Address – Enter the IP address for your Xojo Cloud server
- Port – Enter 22. This is the SSH port number.
- User Name – Enter the user name that was provided to you in the Xojo Cloud control panel when setting up the tunnel.
- Password – Enter the password that was provided to you in the Xojo Cloud control panel when setting up the tunnel.
Now, as far as setting up the connection to the database itself, you will have another set of fields to fill out:
- Host Name or IP Address – Enter “localhost”*
- Port – 5432 for PostgreSQL, 3306 for MySQL
- Database – Enter the user name that was provided to you in the Xojo Cloud control panel when setting up the database server.
- User Name – Enter the user name that was provided to you in the Xojo Cloud control panel when setting up the database server.
- Password – Enter the password that was provided to you in the Xojo Cloud control panel when setting up the database server.
* The reason you enter localhost for the server name is that you want to connect to your side of the tunnel. SSH will do the job of hooking things up on the other end for you.
Voila! You can now connect to your database server to create and edit users, databases, tables, etc.
Manually Creating a Tunnel
Sometimes you need to create a tunnel outside of a management app. This is a great way of testing your app locally while still having it connected to the actual database server. If you are using OS X or Linux, you can just open a Terminal window:
On OS X and Linux the command is quite simple:
ssh -L 5432:localhost:5432 dbadmin@ipaddress -N
If you want to see more information about the tunnel and when it is in use, add -v at the end of that command to use it in verbose mode.
If you are using Windows, I suggest using a program like PuTTY. There are a number of tutorials available on the internet, just put putty ssh tunnel into your favorite search engine. [Xojo Blog Post: Setting up puTTY to create a SSH tunnel from Windows to Xojo Cloud]
That’s it! Connecting a web app to a database server has never been easier and we hope this will encourage you to build bigger and better web apps!
For more information, you can also refer to the Xojo Cloud Databases topic in the docs.