Connecting to MySql DB in strapi
Solution for 2020 (Strapi 3.1.3)
Add client
yarn add sails-mysql
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,
},
},
},
});
- 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