Connecting to MySql DB in strapi

Solution for 2020 (Strapi 3.1.3)

  1. Add client yarn add sails-mysql

  2. Change config <project>/config/database.js (for development) or <project>/config/env/production/database.js (for production)

module.exports = ({ env }) => ({
  defaultConnection: 'default',
  connections: {
    default: {
      connector: 'bookshelf',
      settings: {
        client: "mysql",
        host: env('DATABASE_HOST', 'localhost'),
        port: env('DATABASE_PORT', 3306),
        database: env('DATABASE_NAME', 'default'),
        username: env('DATABASE_USERNAME', 'root'),
        password: env('DATABASE_PASSWORD', ''),
      },
      options: {
        useNullAsDefault: true,
      },
    },
  },
});
  1. Also may be you'll need to run yarn build to rebuild your CMS.

You should add a new connection like this:

{
  "orm": {
    "adapters": {
      "mysql": "sails-mysql"
    },
    "defaultConnection": "default",
    "connections": {
      "someMysqlServer": {
        "adapter": "mysql",
        "host": "YOUR_MYSQL_SERVER_HOSTNAME_OR_IP_ADDRESS",
        "user": "YOUR_MYSQL_USER",
        "password": "YOUR_MYSQL_PASSWORD",
        "database": "YOUR_MYSQL_DB"
      }
    }
  }
}

The current version of Strapi is based on Waterline so if you can't find the right info in the documentation, take a look at the Waterline/Sails documentation as well.


MySQL adapter for the Sails framework and Waterline ORM. Allows you to use MySQL via your models to store and retrieve data. Also provides a query() method for a direct interface to execute raw SQL commands.

Install from NPM.

In your app:

$ npm install sails-mysql

Sails Configuration

Add the mysql config to the config/connections.js file. Basic options:

module.exports.connections = {
  mysql: {
    module    : 'sails-mysql',
    host      : 'localhost',
    port      : 3306,
    user      : 'username',
    password  : 'password',
    database  : 'MySQL Database Name'

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

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

And then change default model configuration to the config/models.js:

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

Run tests

You can set environment variables to override the default database config for the tests, e.g.:

$ 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
}

Reference: https://www.npmjs.com/package/sails-mysql

Tags:

Strapi