Creating Database Connections with the Database Manager

A lot of new power comes with the new Database Manager in Wappler.
You can quickly connect your project to all kind of databases you want to use in it.

There is a wide range of databases that you can fully manage from within Wappler. You can start with simple local SQLite database and grow to more strong high availability database servers like MySQL, MariaDB, PostgreSQL and Microsoft SQL Server.

You can choose to run those servers on your local machine during development and also have them running on your hosting server for your live site.

You can also use separate database servers as offered by Amazon RDS or Digital Ocean Managed Databases for your enterprise grade database access with maximum performance, security and scalability. See: https://www.digitalocean.com/community/tutorials/understanding-managed-databases

Database Connections in Wappler

With the new Database Manager in Wappler setting up a database connection is really easy.
You can use multiple, unique database connections for your Wappler project, depending on your needs. Those will be the database you use.

Since you can work in multiple environments like local development or live production, the database connection settings are dependent per project target. So you for each target you have defined you will have separate options for the database connection.

Defining a Database Connection

To use a database in your Wappler project, you will have to define it first.

Note: If your project is using Docker - the connection called “db” is already available for you.
If you have already used Server Connect in that project, you will see all the database connection that you have created in Server Connect already listed.

Creating a new Database Connection is easy. Just select the “Database Connections” branch on top and then choose “Add Connection” from the context menu.

image

Then give it a name and proceed. The database will be available under that name for the rest of your project.

Tip: The database connection name will be available on all your project targets. You are working with the database and its settings as defined for the currently selected target. To specify different settings you will have to switch the active target first.

image

When you select the new database connection you will see its options:

image

Local Development

Default the connection will use local development server, so in case you are on Docker or use local WAMP/MAMP/XAMP server you don’t have to do anything else.

image

Server Model

If you use a Server Modal, like PHP, ASP or ASP.NET, you will be also using Server Connect and it’s Database Connector to allow you web server to connect to your database

Your server model is selected in the Project Options

Just make sure you define a Server Connect Connection so that your database can be accessed the same way as your local web server will access it. Again with Docker this will be defined automatically, but if you have your own local development server you have to enter it manually.

Note this is only for local development targets where you have and the web server and the database server running on the same local development machine.

Using Remote Database Servers

If you have a live database server and you want to be able to push changes to it or view its database structure, you will have to define a “Direct Connection” to it for Wappler.

So make sure you have selected your live target first, then select the database connection name and enable the “Use Direct Connection” option to allow Wappler to access your database directly.

Then a new button will appear “Direct Database Connect” that allows you to define that connection:

image

Click on it to see the Direct Database Connection options:

On this dialog you can choose the database type you are connecting to and enter all the needed credentials. The options vary per chosen database type.

Connecting to Digital Ocean Managed Database

Digital Ocean provides great service by offering you fully managed database. They will fully setup and backup the database for you so it is a very convenient and secure service. You can also easily choose the database type and scale your database server depending on your needs and growth.

You can setup such a managed database in a minute. Check for Introduction to Digital Ocean Managed Databases

In Wappler you can connect super easy to such remote dataabase. You can just import the database connection string from providers Digital Ocean and all the settings will be entered automatically:

image

image

Then you can just paste the connection string that you get with Digital Ocean:

image

Just choose the type of Connection String (1) and then show your passwork (2) and copy it to the clipboard (3)

Then you can paste it in the Wappler Import Connection String dialog and import it.
And there you go it is entered for you nicely:

Even SSL is automatically selected with the right Profile, so you don’t have to apply additional SSL options to connect to it.

You can test your connection to see if it is all working well:

When everything is fine and you see the Database Connection OK message you can save your connection. From now on Wappler will be able to access this database when needed and when the target is active.

Connecting to Amazon RDS

Amazon offer also a great service that offers you fully managed dedicated database servers available to you.

You can even run your database servers completely isolated in a VPC (Virtual Private Cloud) for maximum security. Then only your web server will have access to them and also Wappler with the right credentials. So this is really a high security setup.

To get started with such setup you can follow the Amazon tutorial Create a Web Server and an Amazon RDS Database

To connect to such isolated Amazon RDS instance, you will need the database instance name, SSL and special SSH tunneling options:

image

image

Test your connection and when everything is fine and you see the Database Connection OK message you can save your connection. From now on Wappler will be able to access this database when needed and when the target is active.

Conclusion

In this article you saw how easy it is to setup any kind of database connection in Wappler with the new Database Manager.

Wappler offers rich connectivity to wide range of database solutions, so it is up to your needs to choose the one that suits you best.

Wappler will connect then seamlessly and allows you to build the powerful solutions you want.

8 Likes

Attempted to implemented the Digital Ocean Managed DB steps for the new Database manager as per your instructions (new project and DB) but received the following error - error connecting: self signed certificate in certificate chain image
Any suggestions? =]

Try choosing the “default” profile instead of “Digital Ocean RDS”

1 Like

Thanks for the suggestion, but unfortunately obtained another error: image

1 Like

When connecting to Digital Ocean managed mysql 8, I’m also getting this error when following the guide.
image

And when choosing default profile, I see this

Thanks
ZubairLK

I am getting the same error
Any help?

Hi,
Same kinds of issues here:

DB is up and running and I can connect via a desktop client when using the ca-cert from digitalocean’s managed database admin panel.

Project is set up as a Server Connect connection, running under node.

If I disable SSL in Dappler, I get ER_NOT_SUPPORTED_AUTH_MODE.

If I enable SSL, using Digitalocean RDS option in the SSL settings, I get the “self-signed certificate in certificate chain” mentioned above.

If I choose the Custom profile for SSL, and try to select the folder where the cert is, I get “Please seect file inside your project path”. The folder I’m picking there is the one that is synced via git to my remote git repo (on D: drive)

If I just enter the relative path from the project root (/certs/ca-certificate.crt, after creating that folder manually), then try the refresh button in the Database Manager window, dappler hangs.

If I then try running the web app, it gets a 500 error from http://localhost:3000/api/data/(action name), with the response

{

“status”: “500”,
“code”: “HANDSHAKE_SSL_ERROR”,
“message”: “error:0909006C:PEM routines:get_name:no start line”,
“stack”: “Error: error:0909006C:PEM routines:get_name:no start line\n at Object.createSecureContext (_tls_common.js:131:17)\n at createSecureContext (D:\dev-yourideamachine\node_modules\mysql\lib\Connection.js:463:19)\n at Connection._startTLS (D:\dev-yourideamachine\node_modules\mysql\lib\Connection.js:279:5)\n at Handshake. (D:\dev-yourideamachine\node_modules\mysql\lib\protocol\Protocol.js:172:24)\n at Handshake.emit (events.js:196:13)\n at Handshake.HandshakeInitializationPacket (D:\dev-yourideamachine\node_modules\mysql\lib\protocol\sequences\Handshake.js:78:10)\n at Protocol._parsePacket (D:\dev-yourideamachine\node_modules\mysql\lib\protocol\Protocol.js:291:23)\n at Parser._parsePacket (D:\dev-yourideamachine\node_modules\mysql\lib\protocol\Parser.js:433:10)\n at Parser.write (D:\dev-yourideamachine\node_modules\mysql\lib\protocol\Parser.js:43:10)\n at Protocol.write (D:\dev-yourideamachine\node_modules\mysql\lib\protocol\Protocol.js:38:16)\n --------------------\n at Protocol._enqueue (D:\dev-yourideamachine\node_modules\mysql\lib\protocol\Protocol.js:144:48)\n at Protocol.handshake (D:\dev-yourideamachine\node_modules\mysql\lib\protocol\Protocol.js:51:23)\n at Connection.connect (D:\dev-yourideamachine\node_modules\mysql\lib\Connection.js:116:18)\n at D:\dev-yourideamachine\node_modules\knex\lib\dialects\mysql\index.js:68:18\n at new Promise ()\n at Client_MySQL.acquireRawConnection (D:\dev-yourideamachine\node_modules\knex\lib\dialects\mysql\index.js:63:12)\n at create (D:\dev-yourideamachine\node_modules\knex\lib\client.js:290:39)”
}

I’m now stuck.

Any ideas ?

Regards,
Andy

Hi! Soo I had the same problem. In my case connecting to the digital ocean database via string worked, when I choose custom profile and then just choose the correct ceritifcate, which was located on my desktop. Otherwise you can have a look on stackoverflow and check the docker file if everything is configured correctly. Let me know if this helped
Lars

Thanks Lars, I’m pretty sure that’s what I tried the first time with the certificate, but got the error I mentioned about it needing to be in the project folder. I’ll try again shortly and get back to the thread.
Andy

Hmm, that’s strange. I downloaded by cert and stuck it on my desktop, and the connection was saved, so that’s progress. Thanks!

However, I’m now getting timeout errors any time I try to do anything, but no issues with my desktop client.

Andy

Ah, and if I run the web app which calls the server API, I then see the 'self signed certificate in certificate chain" error in the browser console. So back to square one.

I was able to fix this same problem by going to the project settings and verifying the server model and handler were set properly, (in my case, node.js.) After that, connection was as smooth as butter.

1 Like

Great post! I’m having some trouble after creating a new non-docker proyect that was originally developed on docker.

This is the docker target working correctly:

And this is the non-docker development target:

Despite connecting correctly (on wappler), when loading I keep getting this error:

  1. code: “ENOTFOUND”
  2. message: “getaddrinfo ENOTFOUND db”
  3. stack: “Error: getaddrinfo ENOTFOUND db\n at GetAddrInfoReqWrap.onlookup [as oncomplete] (dns.js:67:26)”
  4. status: “500”

The same on wappler’s Local Web Server:

server-connect:server Got error? Error: getaddrinfo ENOTFOUND db
server-connect:server at GetAddrInfoReqWrap.onlookup [as oncomplete] (dns.js:67:26) {
server-connect:server errno: -3008,
server-connect:server code: ‘ENOTFOUND’,
server-connect:server syscall: ‘getaddrinfo’,
server-connect:server hostname: ‘db’,
server-connect:server fatal: true
server-connect:server } +0ms

Thanks to everyone who contributed to this thread. Lots of good information. I have been trying to figure out a connection issue for a few days. I have a hosted Postgresql database on Google Cloud using ssl and IP restriction enabled. I can connect fine with a GUI client (dbeaver and Navicat), however when I use the wappler direct database connection I get the following error:

Hostname/IP does not match certificate’s altnames: Host: localhost. is not cert’s CN: systems-dev:sxd

I read on a few boards that you can bypass the CN validation using variables to get around this in your code but there is no option in the wappler DDC to do this.

BTW - I am using MAC OS with Apple Silicone.

Appreciate any suggestions.

@George

Im trying to connect to a Managed Database on DO via the TARGET Settings

I have setup the database on DO.

I have accessed it via SQL Workbench via the details. And working.

But im getting a bit lost in the TARGET setup of the DB…
Is the TARGET DATABASE the place then add these login details ?

And then i get this error?

So the question is… for this to work… do i need the domain to be on SSL before it will work?
Or is there some funny trick… like making the username “db_user” instrad of “doadmin”

Im sure i have done something silly…

No, no the targets settings are for docker databases.

When using external database you have to choose “no database” there and just enter your database settings in the server connect global database connection for this target.

Do make sure the connection is called just “db”

1 Like

thank you @George