Connecting To MySql Database In Strapi

Introduction

This article helps you to create a connection to the MySQL database using Strapi. This is an article for the beginners where the basic connection can be created with the existing MySQL database.

I have explained it in step by step way along with providing the code snippet. Also, the framework and its features are also discussed as questions and answers below which will make the users understand actually how the code executes and establishes the connection. 

What is Strapi?

Strapi is an open-source Node.js rich framework for building applications and services. Strapi enables developers to focus on writing reusable application logic instead of spending time building infrastructure. It is designed for building practical and production-ready Node.js applications in a matter of hours instead of weeks. The framework sits on top of Koa. It's ensemble of small modules working together to provide simplicity, maintainability, and structural conventions to Node.js applications.

Can I connect the MYSQL database to Strapi?

Yes, you are capable of connecting the MySQL database to the Strapi using the configuration settings code that helps you connect the Strapi and MySQL database.

Do we need to use any specific MySQL Framework?

Yes, it’s recommended to use the Sails framework for creating the database connection when you use the Strapi codes.

What is the Sails Framework?

Build practical, production-ready Node.js apps in a matter of weeks, not months.

"Sails" is the most popular MVC framework for Node.js, designed to emulate the familiar MVC pattern of frameworks like Ruby on Rails, but with support for the requirements of modern apps: data-driven APIs with a scalable, service-oriented architecture.

Which ORM is used by SAILS Framework?

Sails come bundled with an ORM called Waterline (implemented in the ORM hook.)

How to connect the MySQL using Strapi in Sails Framework?

Step 1

Install the necessary packages from the NPM [Nugget Package Manager]. Use the following code to install the Sails from the NPM.

$ npm install sails-mysql

Step 2 Configuration of Sails

Create a file name MyConnection.js. This file serves the main configuration to hold all the information to connect to the database.

module.exports.connections = {
  mysql: {
    module    : 'sails-mysql',
    host      : 'localhost', // The host server address, I have used local machine
    port      : 3306, //Port of the server
    user      : 'username', //”BhuvanUser” – user name to connect to the database
    password  : 'password', // “Ert#2123” Credentails for that username
    database  : 'MySQL Database Name' //”StudentsDB” name of the database

    // OR (explicit sets take precedence)
    module    : 'sails-mysql',
    url       : 'mysql2://USER:PASSWORD@HOST:PORT/DATABASENAME'

    // Optional
    charset   : 'utf8',
    collation : 'utf8_swedish_ci'
  }
};

Step 3

Modify the default model of configuration to config/models.js file.

module.exports.models = {
  connection: 'mysql'
};

Step 4

Execute the code and check if that works fine. Set environment variables that have to override the default database config for the testing of connection.

$ WATERLINE_ADAPTER_TESTS_PASSWORD=yourpass npm test
Default settings are:

{
  host: process.env.WATERLINE_ADAPTER_TESTS_HOST || 'localhost',
  port: process.env.WATERLINE_ADAPTER_TESTS_PORT || 3306,
  user: process.env.WATERLINE_ADAPTER_TESTS_USER || 'root',
  password: process.env.WATERLINE_ADAPTER_TESTS_PASSWORD || '',
  database: process.env.WATERLINE_ADAPTER_TESTS_DATABASE || 'sails_mysql',
  pool: true,
  connectionLimit: 10,
  waitForConnections: true
}

Step 5

When you find the connection is established successfully, then the project can be continued. If not, please check for the domain name, database credentials, and port number verification.

Sometimes, the Firewall in the system may block the port to access or block the system to access the port. So, make sure that the port is available for use and also, it's always advisable to check the code in Administrator access if the connection is not getting established.

Hope this article has helped you.