Develop a JavaScript application with MySQL or MariaDB on Azure
MariaDB and MySQL share a common ancestry and maintain compatibility via the MySQL protocol. MySQL clients can connect to MariaDB and vice versa.
To create, move, or use a MySQL or MariaDB database, you need an Azure resource. Learn how to create the resource and use your database.
Create an Azure Database for MySQL resource
You can create a resource with:
- Azure CLI
- Azure portal
- @azure/arm-mysql
Create an Azure Database for MySQL resource with Azure CLI
Use the following Azure CLI az mysql server create command in the Azure Cloud Shell to create a new MySQL resource for your database.
az mysql server create \
--subscription YOUR-SUBSCRIPTION-ID-OR-NAME \
--resource-group YOUR-RESOURCE-GROUP \
--name YOURRESOURCENAME \
--location eastus \
--admin-user YOUR-ADMIN-NAME \
--ssl-enforcement Disabled \
--enable-public-network Enabled
This command may take a couple of minutes to complete and creates a publicly available resource in the eastus region.
The response includes your server's configuration details including:
- the autogenerated password for the admin account
- the command to connect to the server with mysql CLI
{
"additionalProperties": {},
"administratorLogin": "mySqlAdmin",
"byokEnforcement": "Disabled",
"connectionString": "mysql defaultdb --host mysqldb-2.mysql.database.azure.com --user mySqlAdmin@mysqldb-2 --password=123456789",
"databaseName": "defaultdb",
"earliestRestoreDate": "2021-02-08T16:48:01.673000+00:00",
"fullyQualifiedDomainName": "mysqldb-2.mysql.database.azure.com",
"id": "/subscriptions/.../resourceGroups/my-resource-group/providers/Microsoft.DBforMySQL/servers/mysqldb-2",
"identity": null,
"infrastructureEncryption": "Disabled",
"location": "westus",
"masterServerId": "",
"minimalTlsVersion": "TLSEnforcementDisabled",
"name": "mysqldb-2",
"password": "123456789",
"privateEndpointConnections": [],
"publicNetworkAccess": "Enabled",
"replicaCapacity": 5,
"replicationRole": "None",
"resourceGroup": "my-resource-group",
"sku": {
"additionalProperties": {},
"capacity": 2,
"family": "Gen5",
"name": "GP_Gen5_2",
"size": null,
"tier": "GeneralPurpose"
},
"sslEnforcement": "Disabled",
"storageProfile": {
"additionalProperties": {},
"backupRetentionDays": 7,
"geoRedundantBackup": "Disabled",
"storageAutogrow": "Enabled",
"storageMb": 51200
},
"tags": null,
"type": "Microsoft.DBforMySQL/servers",
"userVisibleState": "Ready",
"version": "5.7"
}
Before you can connect to the server programmatically, you need to configure your firewall rules to allow your client IP address through.
Add firewall rule for your client IP address to MySQL resource
By default, the firewall rules are not configured. You should add your client IP address so your client connection to the server with JavaScript is successful.
az mysql server firewall-rule create \
--resource-group YOUR-RESOURCE-GROUP \
--server YOURRESOURCENAME \
--name AllowMyIP --start-ip-address 123.123.123.123 \
--end-ip-address 123.123.123.123
If you don't know your client IP address, use one of these methods:
- Use the Azure portal to view and change your firewall rules, which includes adding your detected client IP
- Run you Node.js code, the error about your firewall rules violation includes your client IP address
While you can add the full range of internet addresses as a firewall rule, 0.0.0.0-255.255.255.255, this leaves your server open to attacks.
Create a database on the server with Azure CLI
Use the following Azure CLI az mysql db create command in the Azure Cloud Shell to create a new MySQL database on your server.
az mysql db create \
--subscription YOUR-SUBSCRIPTION-ID-OR-NAME \
--resource-group YOUR-RESOURCE-GROUP \
--server-name YOURRESOURCENAME \
--name YOURDATABASENAME
Get the MySql connection string with Azure CLI
Retrieve the MySql connection string for this instance with the az mysql server show-connection-string command:
az mysql server show-connection-string \
--subscription YOUR-SUBSCRIPTION-ID-OR-NAME \
--server-name YOURRESOURCENAME
This returns the connection strings for the popular languages as a JSON object. You need to replace {database}, {username}, and {password} with your own values before using the connection string.
{
"connectionStrings": {
"ado.net": "Server=YOURRESOURCENAME.mysql.database.azure.com; Port=3306; Database={database}; Uid={username}@YOURRESOURCENAME; Pwd={password}",
"jdbc": "jdbc:mysql://YOURRESOURCENAME.mysql.database.azure.com:3306/{database}?user={username}@YOURRESOURCENAME&password={password}",
"mysql_cmd": "mysql {database} --host YOURRESOURCENAME.mysql.database.azure.com --user {username}@YOURRESOURCENAME --password={password}",
"node.js": "var conn = mysql.createConnection({host: 'YOURRESOURCENAME.mysql.database.azure.com', user: '{username}@YOURRESOURCENAME',password: {password}, database: {database}, port: 3306});",
"php": "host=YOURRESOURCENAME.mysql.database.azure.com port=3306 dbname={database} user={username}@YOURRESOURCENAME password={password}",
"python": "cnx = mysql.connector.connect(user='{username}@YOURRESOURCENAME', password='{password}', host='YOURRESOURCENAME.mysql.database.azure.com', port=3306, database='{database}')",
"ruby": "client = Mysql2::Client.new(username: '{username}@YOURRESOURCENAME', password: '{password}', database: '{database}', host: 'YOURRESOURCENAME.mysql.database.azure.com', port: 3306)"
}
}
View and use your MySQL on Azure
While developing your MySQL database with JavaScript, use one of the following tools:
- Azure Cloud Shell's mysql CLI
- MySQL Workbench
- Visual Studio Code extension
Use SDK packages to develop your MySQL on Azure
The Azure MySQL uses npm packages already available, such as:
Use Promise-mysql SDK to connect to MySQL on Azure
To connect and use your MySQL on Azure with JavaScript, use the following procedure.
Make sure Node.js and npm are installed.
Create a Node.js project in a new folder:
mkdir MySQLDemo && \ cd MySQLDemo && \ npm init -y && \ npm install promise-mysql && \ touch index.js && \ code .The command:
- creates a project folder named
MySQLDemo - changes the Bash terminal into that folder
- initializes the project, which creates the
package.jsonfile - installs the promise-mysql npm package - to use async/await
- creates the
index.jsscript file - opens the project in Visual Studio Code
- creates a project folder named
Copy the following JavaScript code into
index.js:// To install npm package, // run following command at terminal // npm install promise-mysql // get MySQL SDK const MySQL = require('promise-mysql'); // query server and close connection const query = async (config) => { // creation connection const connection = await MySQL.createConnection(config); // show databases on server const databases = await connection.query('SHOW DATABASES;'); console.log(databases); // show tables in the mysql database const tables = await connection.query('SHOW TABLES FROM mysql;'); console.log(tables); // show users configured for the server const rows = await connection.query('select User from mysql.user;'); console.log(rows); // close connection connection.end(); }; const config = { host: 'YOUR-RESOURCE_NAME.mysql.database.azure.com', user: 'YOUR-ADMIN-NAME@YOUR-RESOURCE_NAME', password: 'YOUR-ADMIN-PASSWORD', port: 3306, }; query(config) .then(() => console.log('done')) .catch((err) => console.log(err));Replace the host, user, and password with your values in the script for your connection configuration object,
config.Run the script.
[ RowDataPacket { Database: 'information_schema' }, RowDataPacket { Database: 'defaultdb' }, RowDataPacket { Database: 'dbproducts' }, RowDataPacket { Database: 'mysql' }, RowDataPacket { Database: 'performance_schema' }, RowDataPacket { Database: 'sys' } ] [ RowDataPacket { Tables_in_mysql: '__az_action_history__' }, RowDataPacket { Tables_in_mysql: '__az_changed_static_configs__' }, RowDataPacket { Tables_in_mysql: '__az_replica_information__' }, RowDataPacket { Tables_in_mysql: '__az_replication_current_state__' }, RowDataPacket { Tables_in_mysql: '__firewall_rules__' }, RowDataPacket { Tables_in_mysql: '__querystore_event_wait__' }, RowDataPacket { Tables_in_mysql: '__querystore_query_metrics__' }, RowDataPacket { Tables_in_mysql: '__querystore_query_text__' }, RowDataPacket { Tables_in_mysql: '__querystore_wait_stats_procedure_errors__' }, RowDataPacket { Tables_in_mysql: '__querystore_wait_stats_procedure_status__' }, RowDataPacket { Tables_in_mysql: '__recommendation__' }, RowDataPacket { Tables_in_mysql: '__recommendation_session__' }, RowDataPacket { Tables_in_mysql: '__script_version__' }, RowDataPacket { Tables_in_mysql: 'columns_priv' }, RowDataPacket { Tables_in_mysql: 'db' }, RowDataPacket { Tables_in_mysql: 'engine_cost' }, RowDataPacket { Tables_in_mysql: 'event' }, RowDataPacket { Tables_in_mysql: 'func' }, RowDataPacket { Tables_in_mysql: 'general_log' }, RowDataPacket { Tables_in_mysql: 'gtid_executed' }, RowDataPacket { Tables_in_mysql: 'help_category' }, RowDataPacket { Tables_in_mysql: 'help_keyword' }, RowDataPacket { Tables_in_mysql: 'help_relation' }, RowDataPacket { Tables_in_mysql: 'help_topic' }, RowDataPacket { Tables_in_mysql: 'innodb_index_stats' }, RowDataPacket { Tables_in_mysql: 'innodb_table_stats' }, RowDataPacket { Tables_in_mysql: 'ndb_binlog_index' }, RowDataPacket { Tables_in_mysql: 'plugin' }, RowDataPacket { Tables_in_mysql: 'proc' }, RowDataPacket { Tables_in_mysql: 'procs_priv' }, RowDataPacket { Tables_in_mysql: 'proxies_priv' }, RowDataPacket { Tables_in_mysql: 'query_store' }, RowDataPacket { Tables_in_mysql: 'query_store_wait_stats' }, RowDataPacket { Tables_in_mysql: 'recommendation' }, RowDataPacket { Tables_in_mysql: 'server_cost' }, RowDataPacket { Tables_in_mysql: 'servers' }, RowDataPacket { Tables_in_mysql: 'slave_master_info' }, RowDataPacket { Tables_in_mysql: 'slave_relay_log_info' }, RowDataPacket { Tables_in_mysql: 'slave_worker_info' }, RowDataPacket { Tables_in_mysql: 'slow_log' }, RowDataPacket { Tables_in_mysql: 'tables_priv' }, RowDataPacket { Tables_in_mysql: 'time_zone' }, RowDataPacket { Tables_in_mysql: 'time_zone_leap_second' }, RowDataPacket { Tables_in_mysql: 'time_zone_name' }, RowDataPacket { Tables_in_mysql: 'time_zone_transition' }, RowDataPacket { Tables_in_mysql: 'time_zone_transition_type' }, RowDataPacket { Tables_in_mysql: 'user' } ] [ RowDataPacket { User: 'mySqlAdmin' }, RowDataPacket { User: 'azure_superuser' }, RowDataPacket { User: 'azure_superuser' }, RowDataPacket { User: 'mysql.session' }, RowDataPacket { User: 'mysql.sys' } ] done