Using Frappe / ERPNext with any DBaaS

Hosting your database files to a different server from the one hosting your software files is most of the time the best approach to ensuring safety and security of your data. This is because you can emphasize security on the different layers as required.

In this article, I will walk you through the process of using Frappe / ERPNext with any DBaaS. This tutorial is universal and will work with any trusted DBaas whether you are on Amazon RDS, MariaDB, Azure or any other DBaaS you prefer.

ERPNext allows you to specify the host of your database in the common_site_config.json file. This file is located in your sites directory.

Setup an RDS Instance on AWS

In this tutorial, we will use Amazon RDS.

Log in to your Amazon RDS and follow the below steps:

1. Click the “Create database” button.
2. Choose “Standard Create” and select “MariaDB” as the database engine.
3. Choose a supported MariaDB Version (at the time of writing this article it is 10.3)
4. Set the “Master username” to “root” and choose a password. This is the password that bench wants to know when it asks for the “root password”.
5. Choose the size of the DB instance (at least “t2.small”).
6. Optional: Enable Multi-AZ deployment for redundancy.
7. Choose the same VPC that contains your frappe server EC2 instance.
8. Select or create a parameter group that matches the below settings (for MariaDB 10.3).

character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
default-character-set = utf8mb4

9. Create the database and wait until it’s ready
10. Note down the “Endpoint” for that instance (for example, karani.rds.amazonaws.com)

Setup Frappe

This is done on the server where ERPNext files will be hosted. It could be an EC2 instance or a VPS running on another provider like Digital Ocean, Google Cloud Platform or any other VPS service you prefer.

Do the following:

First, test whether you can connect to the database by running the following command and provide the root password you set up earlier.

mysql -h karani.rds.amazonaws.com -u root -p

Open sites/common_site_config.json with an editor of choice.

Add a key db_host and set it to the endpoint you just noted down. Add a key rds_db and set it to 1. Below is a sample of the lines you need to add:

{
        "db_host": "karani.rds.amazonaws.com",
        "rds_db": 1
}

You can not create a new site with the bench new-site command.

Move an existing database to RDS

1. Take a database backup of your site with bench backup.
2. Change the configuration as described in “Setup Frappe”.
3. Create a new site and restore the backup into it.