search

directus-labs / guest-authoring

A repo for our guest authors to work on content
11 stars 40 forks source link

How to Use Directus with MySQL Database on AWS RDS #254

Closed Deepak-Vohra closed 3 months ago

Deepak-Vohra commented 3 months ago

What is your idea?

How to Use Directus with MySQL Database on AWS RDS

Key Takeaways

Directus is a one-of-a-kind Content Management System (CMS), and Data Backend as a Service (BaaS) built on top of your data model, to provide seamless integration with your data. You have the choice to manage your data directly from the Directus web console to perform tasks such as creating a data collection, adding items to a collection, importing/exporting data from CSV and JSON, and interacting with your data using GraphQL and REST APIs. Directus manages the ancillary tasks of authentication, visualizing data on dashboards, and editing & caching data. It supports DevOps operations such as syncing your data over WebSockets, and automating complex workflows.

With a relational database, Directus can be used as an abstraction layer over the SQL data model. In this article I’ll discuss how to use Directus with a MySQL instance hosted on Amazon Relational Database Service. You’ll create a self-hosted Directus project using Docker Compose.

Prerequisite Setup

  1. On a Linux platform (Ubuntu used in article) install Docker
  2. Create an Amazon Web Services (AWS) account - Free Tier to start with
  3. Set up AWS Networking - Create a VPC including other resources such as Subnets, Internet Gateway, and Routing Table. When creating/editing your VPC set DNS to enable DNS Hostnames, and DNS Resolution as shown in Figure 1.

Figure 1. DNS Settings for AWS VPC Figure 1. DNS Settings for AWS VPC

Creating an AWS RDS Instance for MySQL

To create a MySQL Database as a Service (DbaaS) Service, login to the web console and select the RDS service. Select Databases in the navigation margin and click on Create database as shown in Figure 2.

Figure 2. RDS>Databases>Create database Figure 2. RDS>Databases>Create database

In the Create database window choose a database creation method. Select Easy create if not familiar with RDS as shown in Figure 3. In Configuration select MySQL.

Figure 3. Create database >Easy create>MySQL Figure 3. Create database >Easy create>MySQL

Select Edition as MySQL Community>Free Tier as shown in Figure 4. Specify a DB instance identifier (mysqldb). Specify Master username as admin, and select the Self managed option for Credentials management.

Figure 4. Specifying MySQL Database Identifier and Credentials Figure 4. Specifying MySQL Database Identifier and Credentials

The default settings for the Easy create option method can be viewed as shown in Figure 5. This may help you in determining whether the Easy create is the right option for you based on which of the settings are editable after an instance is created.

Figure 5. Easy create default settings Figure 5. Easy create default settings

Specify a Master password and make a note of it along with the Master username because you’ll need these credentials when creating a Docker Compose-based platform for Directus. Click on Create database as shown in Figure 6.

Figure 6. Create database button Figure 6. Create database button

A new database instance gets created as indicated in the message shown in Figure 7.

Figure 7. New RDS Database instance create Figure 7. New RDS Database instance create

Enabling Connectivity to MySQL on RDS

To be able to connect to the MySQL database instance you need to ensure that connectivity to the instance is enabled. The Security group used for the RDS instance must have Inbound/Outbound rules set for the instance to be accessible. The RDS instance must be Publicly accessible if the Linux machine you’ll use to access it is not hosted on AWS EC2. A RDS instance created with the Easy create method is not publicly accessible as indicated in Figure 8.

Figure 8. RDS Instance not Publicly accessible Figure 8. RDS Instance not Publicly accessible

Click on Modify for the RDS instance as shown in Figure 9.

Figure 9. Modify Figure 9. Modify

In the Modify DB instance window click on Additional configuration in Connectivity and select the Publicly accessible radio button as shown in Figure 10.

Figure 10. Publicly Accessible Figure 10. Publicly Accessible

The Summary of modifications should list the Public accessibility as Yes as shown in Figure 11. You’ll need to specify the Master password when you modify a RDS instance. Select Schedule modifications as Apply immediately. Click on Modify DB instance.

Figure 11. Modify DB instance Figure 11. Modify DB instance

For a short time, the DB instance Status column shows Modifying as in Figure 12.

Figure 12. Modifying Status Figure 12. Modifying Status

After the modification is complete the Publicly accessible setting should show Yes as in Figure 13.

Figure 13. Publicly Accessible>Yes Figure 13. Publicly Accessible>Yes

Obtaining Connection Parameters

To connect to the newly created DB instance you’ll need the following connection parameters:

Some of these you made a note of when creating the database instance such as Username and Password. Click on the Connectivity and security tab to obtain the MySQL Endpoint and Port (3306 by default) as shown in Figure 14. Copy the Endpoint as you’ll need it to connect to the database from Directus.

Figure 14. Database Endpoint (Hostname) and Port Figure 14. Database Endpoint (Hostname) and Port

Verifying Connection to MySQL

On your local Linux machine create a directory called directus to manage your self-hosted Diectus project. Change to the directory. 

mkdir /directus
cd /directus

Connect to the MySQL Database instance you created using the URL command:

mysql -h mysqldb.crbmlbxmp8qi.us-east-1.rds.amazonaws.com -P 3306 -u admin -p <master password>

The hostname is specified with the -h option as the Endpoint you copied in Figure 14. Port is specified with -P as 3306. Username is specified with -uas the Master username set when creating a DB instance. Password is specified as -p as the Master password. Note that the MySQL port is specified with -P and the password with -p. When prompted, provide your password. This is the Master password you set when creating the RDS DB instance. The MySQL shell prompt gets displayed when a connection is made. 

deepakvohra@:~/directus$ mysql -h mysqldb.crbmlbxmp8qi.us-east-1.rds.amazonaws.com -P 3306 -u admin -p mysql
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Your MySQL connection id is 28
Server version: 8.0.35 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [mysql]>

Creating a Database

List all databases with show databases command:

MySQL [mysql]> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.079 sec)

As these are all system databases used by MySQL internally, create a new database for your Directus project with CREATE DATABASE statement.

MySQL [mysql]> CREATE DATABASE directusdb;

Query OK, 1 row affected (0.085 sec)

The new database should get listed:

MySQL [directusdb]> show databases;`
+--------------------+
| Database           |
+--------------------+
| directusdb         |`
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.088 sec)

Logout and connect to the new database you just created:

MySQL [mysql]> 
 deepakvohra@localhost:~/directus$ mysql -h mysqldb.crbmlbxmp8qi.us-east-1.rds.amazonaws.com -P 3306 -u admin -p directusdb
Enter password: 
Welcome. Commands end with ; or \g.
Your MySQL connection id is 29
Server version: 8.0.35 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [directusdb]> `

Configuring Docker Compose

Docker Compose is a tool for defining and running multi-container applications with Docker. At the least, it needs a configuration file in one of three formats JSON, YAML, and JS with the corresponding configuration file extensions .json, .yaml, or .js. Next, configure Docker Compose to run Docker containers for Directus. Create a configuration file config.json in the /directus directory.

To use MySQL Database with Directus some configuration options must be set in the configuration file. These options along with their values are listed in the following table; DB_CLIENT must be “mysql”, and the other option values could vary for users based on what you configured:

Table 1. Configuration Options Required for MySQL

Configuration Option Value
DB_CLIENT mysql
DB_HOST mysqldb.crbmlbxmp8qi.us-east-1.rds.amazonaws.com
DB_PORT 3306
DB_DATABASE directusdb
DB_USER admin
DB_PASSWORD master_password

The config.json is listed below, in which only the MySQL settings in the preceding table may be different for different users, and all the other settings can be used as listed. Optionally, KEY and SECRETcan be customized. 

version: '3'
services:
  directus:
    image: directus/directus:latest
    ports:
      - 8055:8055
    volumes:
      - ./database:/directus/database
      - ./uploads:/directus/uploads
    environment:
      KEY: 'replace-with-random-value'
      SECRET: 'replace-with-random-value'
      ADMIN_EMAIL: 'admin@example.com'
      ADMIN_PASSWORD: 'd1r3ctu5'
      DB_CLIENT: 'mysql'
      DB_HOST: 'mysqldb.crbmlbxmp8qi.us-east-1.rds.amazonaws.com'
      DB_PORT: '3306' 
`      DB_DATABASE: '``directusdb``'`

      DB_USER: 'admin'
      DB_PASSWORD: 'master_password'
      WEBSOCKETS_ENABLED: "true"

You need to create the directory used by Directus to store data on your local machine, and set its permissions so that Directus can access it. This is the directory set in the volumes under ./database setting as /directus/database. 

mkdir /directus/database
chmod 777 /directus/database

Creating and Starting a Directus Container

Next, you’ll create and start a Docker application for Directus using Docker Compose. The tool options and sub-commands may be listed with docker compose.

deepakvohra@localhost:~/directus$ docker compose`
Usage:  docker compose [OPTIONS] COMMAND
Define and run multi-container applications with Docker.
Options:
     …
  -p, --project-name string        Project name

Commands:
  build       Build or rebuild services
  config      Parse, resolve and render compose file in canonical format
  cp          Copy files/folders between a service container and the local                      filesystem
  create      Creates containers`` for a service.`

  down        Stop and remove containers, networks
  events      Receive real time events from containers.
  exec        Execute a command in a running container
  up          Create and start containers
  version     Show the Docker Compose version information

Next, create and start a Docker container using Docker Compose tool along with the configuration file. Downloading the Docker image for Directus can take some time and the default timeout setting of 25 seconds for Docker Compose may not be sufficient. Therefore, set the environment variable COMPOSE_HTTP_TIMEOUTto a larger timeout, such as 200 seconds. Run the following command, and you may need to specify the administrative password for your Linux machine when prompted:

sudo COMPOSE_HTTP_TIMEOUT=200 docker-compose up

As the partial output indicates, Directus gets launched and becomes accessible at URL http://0.0.0.0:8055.

deepakvohra@localhost:~/directus$ sudo COMPOSE_HTTP_TIMEOUT=200 docker-compose up [sudo] password for deepakvohra: 

Recreating directus_directus_1 ... 
Recreating directus_directus_1 ... done
Attaching to directus_directus_1
directus_1  | [00:15:33.918] INFO: Initializing bootstrap...
directus_1  | [00:15:34.098] INFO: Installing Directus system tables...
directus_1  | [00:15:39.726] INFO: Running migrations...
…
…
`directus_1  | [00:16:19.824] INFO: Setting ``up first`` admin role...`

directus_1  | [00:16:20.107] INFO: Adding first admin user...
directus_1  | [00:16:20.803] INFO: Done
directus_1  | 2024-06-15T00:16:22: PM2 log: Launching in no daemon mode
directus_1  | 2024-06-15T00:16:23: PM2 log: App [directus:0] starting in -cluster mode-
directus_1  | 2024-06-15T00:16:26: PM2 log: App [directus:0] online
directus_1  | [00:16:29.526] WARN: "PUBLIC_URL" should be a full URL
directus_1  | [00:16:29.530] WARN: Upload directory (/directus/uploads) is not read/writeable!
directus_1  | [00:16:29.733] INFO: GraphQL Subscriptions started at ws://0.0.0.0:8055/graphql
directus_1  | [00:16:29.733] INFO: WebSocket Server started at ws://0.0.0.0:8055/websocket
`directus_1  | [00:16:29.759] INFO: Server started at ``http://0.0.0.0:8055`

The docker compose output is shown in Figure 15.

Figure 15. Server started at http://0.0.0.0:8055 Figure 15. Server started at http://0.0.0.0:8055

Logging into Directus Console

Next, log into the Directus console using the URL http://0.0.0.0:8055 in a browser. In the login screen specify username and password. The username and password can be obtained from the Docker Compose configuration file config.json, listed earlier. Click on Sign In as shown in Figure 16.

Figure 16. Sign In to Directus Console Figure 16. Sign In to Directus Console

Creating a Collection

Create your first collection. To start with you can create a minimal collection and add fields as needed later. Click on Create Collection as shown in Figure 17.

Figure 17. Create Collection Figure 17. Create Collection

Specify a collection name (tbl1 for example) in the Creating New Collection window as shown in Figure 18. The Primary Key Field (id) is required, and its type is Auto-incremented integer. Click on Next.

Figure 18. Creating a New Collection Figure 18. Creating a New Collection

Select the Optional Fields to include as shown in Figure 19 in which all optional fields are selected. Click on Finish Setup.

Figure 19. Select Optional Fields and Finish Setup Figure 19. Select Optional Fields and Finish Setup

A new collection will get created as shown in Figure 20. The Data Model tab lists the fields. Additional fields can be created with the Create Field button.

Figure 20. Data Model for the Collection called Tbl1 Figure 20. Data Model for the Collection called Tbl1

Verifying Table in MySQL

In the MySQL shell you can verify that the collection you added is created as a new database table. Set the current database to the “directus” database with the use directus command. This is the database you created earlier and subsequently configured in the Docker Compose configuration file config.json. 

MySQL [directusdb]> use directusdb
Database changed

List tables with the SHOW TABLES command. The tbl1 table is listed along with the system tables. 

MySQL [directusdb]> SHOW TABLES;
+------------------------+
| Tables_in_directusdb   |
+------------------------+
| directus_activity      |
| directus_collections   |
| directus_dashboards    |
| directus_extensions    |
| directus_fields        |
| directus_files         |
| directus_flows         |
| directus_folders       |
| directus_migrations    |
| directus_notifications |
| directus_operations    |
| directus_panels        |
| directus_permissions   |
| directus_presets       |
| directus_relations     |
| directus_revisions     |
| directus_roles         |
| directus_sessions      |
| directus_settings      |
| directus_shares        |
| directus_translations  |
| directus_users         |
| directus_versions      |
| directus_webhooks      |
| tbl1                   |
+------------------------+
25 rows in set (0.089 sec)

Describe the tbl1 database with the DESC command. The table definition corresponds to the fields you added when creating the collection. 

MySQL [directusdb]> DESC tbl1
    -> ;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int unsigned | NO   | PRI | NULL    | auto_increment |
| status       | varchar(255) | NO   |     | draft   |                |
| sort         | int          | YES  |     | NULL    |                |
| user_created | char(36)     | YES  | MUL | NULL    |                |
| date_created | timestamp    | YES  |     | NULL    |                |
| user_updated | char(36)     | YES  | MUL | NULL    |                |
| date_updated | timestamp    | YES  |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+
7 rows in set (0.091 sec)

Initially, the table is empty:

MySQL [directusdb]> select * from tbl1;
Empty set (0.088 sec)

Importing Data into Collection

Create a sample data file (sample.json) to import data into the new collection. 

[    
    {
        "id": 1,
        "status": "First Draft",
        "sort": 1
    }
]

Create the sample.json file in the Directus working directory /directus as shown in Figure 21.

Figure 21. Sample Data file Figure 21. Sample Data file

In the Directus web console No items are listed for the Tbl1 collection. Click on Import/Export as shown in Figure 22.

Figure 22. Import/Export Figure 22. Import/Export

Select the sample.json file with the Import as shown in Figure 23.

Figure 23. Selecting the sample.json file Figure 23. Selecting the sample.json file

Click on Start Import as shown in Figure 24 to start the import.

Figure 24. Start Import Figure 24. Start Import

The Tbl1 collection has one item added as shown in Figure 25.

Figure 25. One item added to Tbl1 Collection Figure 25. One item added to Tbl1 Collection

In the MySQL shell run a SELECT query to list the data added:

MySQL [directusdb]> SELECT * FROM tbl1 \G`
*************************** 1. row ***************************
          id: 1
      status: First Draft
        sort: 1
user_created: ebee0895-01e9-473b-ad41-623cb6be8002
date_created: 2024-06-15 00:32:43
user_updated: NULL
date_updated: NULL
1 row in set (0.079 sec)

Adding a Collection Item from MySQL Shell

The collection in Directus corresponds to a table in the relational database. You may add data directly in the MySQL shell and it’ll get added to the Directus project collection. As an example, add a new row of data with the SQL statement INSERT INTO:

MySQL [directusdb]> INSERT INTO tbl1(id,status,sort) VALUES(2,'First Draft',1);
Query OK, 1 row affected (0.083 sec)

A SELECT statement should list as the new row added:

MySQL [directusdb]> SELECT * FROM tbl1 \G`

*************************** 1. row ***************************
          id: 1
      status: First Draft
        sort: 0
user_created: ebee0895-01e9-473b-ad41-623cb6be8002
date_created: 2024-06-15 00:32:43
user_updated: NULL
date_updated: NULL
*************************** 2. row ***************************
          id: 2
      status: First Draft
        sort: 1
user_created: NULL
date_created: NULL
user_updated: NULL
date_updated: NULL
2 rows in set (0.079 sec)

In the Directus web console, the Tbl1 console should list two collection items as shown in Figure 26.

Figure 26. Two Collection items Figure 26. Two Collection items

Any updates made in the Directus console get applied to the MySQL database table on which the data model is based. As an example, update the value of the status field to Published as shown in Figure 27.

Figure 27. Updating Status of Collection Items to Published Figure 27. Updating Status of Collection Items to Published

In the MySQL shell a SELECTstatement lists the status as published:

MySQL [directusdb]> SELECT * FROM tbl1 \G`
*************************** 1. row ***************************
          id: 1
      status: published
        sort: 0
user_created: ebee0895-01e9-473b-ad41-623cb6be8002
date_created: 2024-06-15 00:32:43
user_updated: ebee0895-01e9-473b-ad41-623cb6be8002
date_updated: 2024-06-15 00:40:04
*************************** 2. row ***************************
          id: 2
      status: published
        sort: 1
user_created: NULL
date_created: NULL
user_updated: ebee0895-01e9-473b-ad41-623cb6be8002
date_updated: 2024-06-15 00:40:04
2 rows in set (0.079 sec)

This bi-directional mapping and access to the data model is one of the features that Directus provides.

Exporting Collection Data

To export a collection, select the collection and click on Export Items as shown in Figure 28.

Figure 28. Export Items Figure 28. Export Items

In the Export Items window select the format as CSV or JSON, and specify the maximum number of records to export with Limit. Select an Export Location as Download File as shown in Figure 29.

Figure 29. Export Items Figure 29. Export Items

Click on Download File as shown in Figure 30.

Figure 30. Download File Figure 30. Download File

An export file (Figure 31) gets downloaded.

Figure 31. Export File Figure 31. Export File

Adding a Collection Item from Console

Three options are available to add a data item to a collection:

We already discussed the first two options. To demonstrate the third option, click on Create Item as shown in Figure 32.

Figure 32. Create Item Figure 32. Create Item

A new collection item starts to get created. Click on Save (Figure 33).

Figure 33. Saving a Collection Item Figure 33. Saving a Collection Item

The collection Tbl1 lists 3 items (Figure 34).

Figure 34. Three Collection items Figure 34. Three Collection items

A SELECT statement in MySQL shell lists three items as well:

MySQL [directusdb]> SELECT * FROM tbl1 \G`
*************************** 1. row ***************************
          id: 1
      status: published
        sort: 0
user_created: ebee0895-01e9-473b-ad41-623cb6be8002
date_created: 2024-06-15 00:32:43
user_updated: ebee0895-01e9-473b-ad41-623cb6be8002
date_updated: 2024-06-15 00:40:04
*************************** 2. row ***************************
          id: 2
      status: published
        sort: 1
user_created: NULL
date_created: NULL
user_updated: ebee0895-01e9-473b-ad41-623cb6be8002
date_updated: 2024-06-15 00:40:04
*************************** 3. row ***************************
          id: 3
      status: draft
        sort: NULL
user_created: ebee0895-01e9-473b-ad41-623cb6be8002
date_created: 2024-06-15 00:45:34
user_updated: NULL
date_updated: NULL
3 rows in set (0.079 sec)

Deleting a Collection

A collection can be deleted from the Directus console or the MySQL shell. The Directus project must be connected to the MySQL database to be able to delete a collection. A collection cannot be deleted if the database has been stopped or deleted. Select the tbl1 data model and click on Delete Collection as shown in Figure 35.

Figure 35. Delete Collection Figure 35. Delete Collection

Deleting the AWS RDS Instance

To delete an AWS RDS Database instance select Actions>Delete as shown in Figure 36.

Figure 36. Deleting a AWS RDS Database Instance Figure 36. Deleting a AWS RDS Database Instance

A message should indicate that the DB instance has been deleted.

Figure 37. RDS DB Instance Deleted Figure 37. RDS DB Instance Deleted

Conclusion

Diectus is a one-stop integrated platform that abstracts an underlying database and provides all the modern features for data management. In this article, you learned about creating a self-hosted Directus project with Docker Compose and the Docker image for Directus directus/directus. You learned about using MySQL database running on AWS Relational Database Service as the underlying database for the Directus data model. You learned about performing create, read, update, and delete (CRUD) operations using the MySQL-based data model.

What are the key takeaways from your post?

Key Takeaways

Country of residence

Canada

Terms & Conditions

github-actions[bot] commented 3 months ago

Thank you for submitting an idea for our guest blog.
We work through new ideas every few weeks as we put together our content schedule. This means you may not get an immediate response as to whether your idea has been accepted, or any follow-up questions we have to clarify your idea.
If your idea is accepted, we will provide a deadline for first draft and how much we can pay you for the post. You will have a few days to confirm whether you are still able and willing to write the post.
If you have any questions in the meantime, feel free to add a comment to this issue.

BB-Loft commented 3 months ago

Thank you for submitting this idea, but unfortunately we are not accepting it as part of our guest author program, we have similar guides on AWS and don't see a demand for this piece. Please feel free to submit additional ideas in future.

Deepak-Vohra commented 3 months ago

Hi Beth, If you'll reconsider, the emphasis is not on AWS, but is on MySQL. I developed article because I Googled "directus mysql" and did not find any resource/documentation/article. I only found a few issues: https://github.com/directus/directus/discussions/17005 https://github.com/directus/directus/discussions/17658 https://forums.docker.com/t/container-connect-to-mysql-failed/138154/2 https://stackoverflow.com/questions/67136373/directus-in-docker-cannot-connect-to-mysql-database-in-docker

BB-Loft commented 3 months ago

Thanks for providing the additional context. Unfortunately, we aren't able to accept this idea but please do feel free to submit different ideas in the future.

Deepak-Vohra commented 3 months ago

Hi Beth, I have a new article proposal around the new PGQL (Property Graph Query Language), but because Directus Doesn't support it, the article doesn't involve Directus. Does an article have to involve Directus for you to consider?

phazonoverload commented 3 months ago

Typically yes, but if it's directly relevant to our users, we can be a bit more flexible. However, PGQL doesn't make sense for us as a database abstraction layer :)

Deepak-Vohra commented 3 months ago

I want to get some initial feedback on a topic before submitting a detailed proposal such as the first one. Would the topic have any interest: How to Use Directus with Oracle Autonomous Database?

If you are not able to comment just based on topic, please let me know. I understand that initial topic feedback is not an acceptance of topic.