如何将MySQL与Sveltekit/NodeJS集成

对于NodeJS项目，我通常只遵循以下标准示例：

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'me',
  password : 'secret',
  database : 'my_db'
});
 
connection.connect();
 
connection.query('SELECT 1 + 1 AS solution', function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results[0].solution);
});
 
connection.end();

Sveltekit不允许var mysql = require('mysql'); 因此我尝试将其替换为import { mysql } from 'mysql'; 这也不起作用。 不确定是否有人有这方面的经验，可以指导我理解我的错误。

---

解决方案

1.安装mysql2包

npm install --save mysql2

2.设置MySQL连接

lib/db/mysql.js

import mysql from 'mysql2/promise';

export const mysqlconn = await mysql.createConnection({ 
    host: '<myhost>',
    user: 'root',
    password: 'mypassword',
    database: 'mydatabase'
});

3.创建API端点

routes/api/read.js

import { mysqlconn } from '$lib/db/mysql';


export async function get() {
    
    let results = await mysqlconn.query('SELECT * FROM mytable')
        .then(function([rows,fields]) {
            console.log(rows);
            return rows;
        });
    
    return {
        body: results
    }
}

---

更新

哪个更好？是上面的代码还是这个代码？

1.安装mysql2包

npm install --save mysql2

2.设置MySQL连接

lib/db/mysql.js

import mysql from 'mysql2/promise';

let mysqlconn = null;

export function mysqlconnFn() {

    if (!mysqlconn) {
        mysqlconn = mysql.createConnection({ 
            host: '<myhost>',
            user: 'root',
            password: 'mypassword',
            database: 'mydatabase'
        });
    }

    return mysqlconn;
}

3.创建API端点

routes/api/read.js

import { mysqlconnFn } from '$lib/db/mysql';


export async function get() {

    let mysqlconn = await mysqlconnFn();

    let results = await mysqlconn.query('SELECT * FROM mytable')
        .then(function([rows,fields]) {
            console.log(rows);
            return rows;
        });
    
    return {
        body: results
    }
}

---

更新2

您还可以在hooks中设置MySQL连接。

参考：https://github.com/sveltejs/kit/issues/1538#issuecomment-1002106271