适用于 MySQL 的 Azure 数据库 - webapp nodejs [英] Azure Database for MySQL - webapp nodejs
问题描述
所以我一直在使用本地 mysql 工作台开发 Web 应用程序.我最近将数据库移至 Azure Database for MySQL.在我离开本地之前，一切正常.我所有的网页都正常工作，现在 4 个页面中只有 2 个工作正常，当我点击损坏的页面时，我遇到了下面的错误.下面是我连接数据库的方式，我不确定第二个连接是否有效.
我需要做这样的事情吗?
在这里编辑------------------------
table_routes.js
var express = require('express'), http = require('http'), mysql = require('mysql');//<---- 这里var app = express();const fs = require('fs');const path = require('path');app.use(function(req, res, next) {res.header("Access-Control-Allow-Origin", "http://127.0.0.1:3000");res.header(Access-Control-Allow-Headers", Origin, X-Requested-With, Content-Type, Accept");下一个();});var connection = mysql.createConnection({主持人:'主持人'，用户:'root'，密码:密码"，数据库:'db1'，ssl:{ca: fs.readFileSync(path.resolve(__dirname, 'BaltimoreCyberTrustRoot.crt.pem'))}});连接.connect();//<---- 和这里//所有环境app.set('port', process.env.PORT || 7003);table_routes2.js
var express = require('express'), http = require('http'), mysql = require('mysql');//<---- 这里var app = express();const fs = require('fs');const path = require('path');app.use(function(req, res, next) {res.header("Access-Control-Allow-Origin", "http://127.0.0.1:3000");res.header(Access-Control-Allow-Headers", Origin, X-Requested-With, Content-Type, Accept");下一个();});var connection = mysql.createConnection({主持人:'主持人'，用户:'用户'，密码:密码"，数据库:'db2'，ssl:{ca: fs.readFileSync(path.resolve(__dirname, 'BaltimoreCyberTrustRoot.crt.pem'))}});连接.connect();//<---- 和这里//所有环境app.set('port', process.env.PORT || 7004);最新
我建议你使用 sequelize 来连接多个数据库.只需要在config.js中配置数据库即可.不是您使用 7003 和 7004 定义多个数据库的方式.
我的示例代码演示了在mysql和sqlsever中连接两个数据库.下图是操作结果.它只是演示代码，如果你想在你的项目中使用它，你需要学习它.
我的demo目录结构如下.
config.js
const fs = require('fs');const path = require('path');模块.出口 = {/**我的开发环境的数据库声明**/发展":{数据库":{dbinmysql":{"database": "mysql",//你应该总是将这些值保存在环境变量中"username":"***@p***mysql",//仅供测试使用，这里也可以定义值密码":Ja***"，主机":*****mysql.mysql.database.azure.com"，端口":3306，ssl":真，"dialect": "mysql",//这里你需要定义你的数据库的方言，在我的例子中是Postgres方言选项":{ssl:{ca: fs.readFileSync(path.resolve(__dirname, 'BaltimoreCyberTrustRoot.crt.pem'))}},},dbinsqlserver":{数据库":pa*****db"，用户名":pa***i"，密码":J*****0"，主机":***sqlserver.database.windows.net"，端口":1433，"dialect": "mssql",//第二个数据库可以有不同的方言方言选项":{选项: {加密:真，}}},},}}test.js
const Sequelize = require('sequelize');const env = process.env.NODE_ENV ||'发展';const { QueryTypes } = require('sequelize');//从config.js加载配置const config = require(`./config.js`)[env];//创建一个空对象来存储我们的数据库const db = {};//将数据库信息提取到数组中const 数据库 = Object.keys(config.databases);//遍历数组并从config.js为每个数据库创建一个新的Sequelize实例for(let i = 0; i < databases.length; ++i) {让数据库 = 数据库 [i];让 dbPath = config.databases[database];console.log("尝试连接"+数据库);//将数据库连接存储在我们的db对象中db[database] = new Sequelize( dbPath.database, dbPath.username, dbPath.password, dbPath );如果(数据库=='dbinmysql'){const results = db[database].query("SELECT *FROM USER", { type: QueryTypes.SELECT })} else if (database == 'dbinsqlserver') {const 结果 = db[database].query("SELECT *FROM TEST", { type: QueryTypes.SELECT })}}隐私
我认为根本原因是当您将 webapp 发布到 azure 时，table_routes_1and2.js 最终监听的端口只有一个，http_80 和 https_443，最终应该只有一个 db生效，这应该是根本原因.
So I have been working on a web app with local mysql workbench. I recently moved the database to Azure Database for MySQL. Everything was working properly before I moved away from local. All my webpages were working properly, now only 2 out of the 4 pages work and I am running into the error below when I click on the broken pages. Below is how I am connecting to the database, I am not sure if the second connection is working.
Do I need to do something like this?
https://docs.microsoft.com/en-us/azure/mysql/howto-configure-ssl
Thank you for any help!
var connection = mysql.createConnection({
host: 'host',
user: 'user',
password: "password",
database: 'schema_1',
ssl: true
});
var connection = mysql.createConnection({
host: 'host',
user: 'user',
password: "password",
database: 'schema_2',
ssl: true
});
EDIT HERE ------------------------
table_routes.js
var express = require('express')
, http = require('http')
, mysql = require('mysql'); // <---- HERE
var app = express();
const fs = require('fs');
const path = require('path');
app.use(function(req, res, next) {
res.header("Access-Control-Allow-Origin", "http://127.0.0.1:3000");
res.header("Access-Control-Allow-Headers", "Origin, X-Requested-With, Content-Type, Accept");
next();
});
var connection = mysql.createConnection({
host: 'host',
user: 'root',
password: "password",
database: 'db1',
ssl: {
ca: fs.readFileSync(path.resolve(__dirname, 'BaltimoreCyberTrustRoot.crt.pem'))
}
});
connection.connect(); // <---- AND HERE
// all environments
app.set('port', process.env.PORT || 7003);
table_routes2.js
var express = require('express')
, http = require('http')
, mysql = require('mysql'); // <---- HERE
var app = express();
const fs = require('fs');
const path = require('path');
app.use(function(req, res, next) {
res.header("Access-Control-Allow-Origin", "http://127.0.0.1:3000");
res.header("Access-Control-Allow-Headers", "Origin, X-Requested-With, Content-Type, Accept");
next();
});
var connection = mysql.createConnection({
host: 'host',
user: 'user',
password: "password",
database: 'db2',
ssl: {
ca: fs.readFileSync(path.resolve(__dirname, 'BaltimoreCyberTrustRoot.crt.pem'))
}
});
connection.connect(); // <---- AND HERE
// all environments
app.set('port', process.env.PORT || 7004);
Newest
I suggest you use sequelize to connect multiple databases. Only need to configure the database in config.js. Not the way you used 7003 and 7004 to define multiple databases.
My sample code demonstrates connecting two databases in mysql and sqlsever.The following figure is the operation result. It just demo code, if you want to use it in your project, you need learn it.
The structure of my demo directory is as follows.
config.js
const fs = require('fs');
const path = require('path');
module.exports = {
/**Declaration of databases for my development environment**/
"development": {
"databases": {
"dbinmysql": {
"database": "mysql", //you should always save these values in environment variables
"username": "***@p***mysql", //only for testing purposes you can also define the values here
"password": "Ja***",
"host": "*****mysql.mysql.database.azure.com",
"port": 3306,
"ssl":true,
"dialect": "mysql", //here you need to define the dialect of your databse, in my case it is Postgres
"dialectOptions": {
ssl: {
ca: fs.readFileSync(path.resolve(__dirname, 'BaltimoreCyberTrustRoot.crt.pem'))
}
},
},
"dbinsqlserver": {
"database": "pa*****db",
"username": "pa***i",
"password": "J*****0",
"host": "***sqlserver.database.windows.net",
"port": 1433,
"dialect": "mssql", //second database can have a different dialect
"dialectOptions": {
options: {
encrypt: true,
}
}
},
},
}
}
test.js
const Sequelize = require('sequelize');
const env = process.env.NODE_ENV || 'development';
const { QueryTypes } = require('sequelize');
//Load the configuration from the config.js
const config = require(`./config.js`)[env];
//Create an empty object which can store our databases
const db = {};
//Extract the database information into an array
const databases = Object.keys(config.databases);
//Loop over the array and create a new Sequelize instance for every database from config.js
for(let i = 0; i < databases.length; ++i) {
let database = databases[i];
let dbPath = config.databases[database];
console.log("try to connecting "+database);
//Store the database connection in our db object
db[database] = new Sequelize( dbPath.database, dbPath.username, dbPath.password, dbPath );
if (database == 'dbinmysql') {
const results = db[database].query("SELECT *FROM USER", { type: QueryTypes.SELECT })
} else if (database == 'dbinsqlserver') {
const results = db[database].query("SELECT *FROM TEST", { type: QueryTypes.SELECT })
}
}
PRIVIOUS
I think the root cause is that when you publish your webapp to azure, there is only one port that table_routes_1and2.js finally listens to, http_80 and https_443, there should be only one db that finally takes effect, this should be the root cause.
这篇关于适用于 MySQL 的 Azure 数据库 - webapp nodejs的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
