Typically for a nodejs project I just follow this standard example:
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 doesn't allow the var mysql = require('mysql');
So I tried replacing that with import { mysql } from 'mysql';
Which also doesn't work.
Not sure if anyone has experience with this and can direct me to understand my mistake.
1. Install mysql2 package
npm install --save mysql2
2. Setup MySQL connection
lib/db/mysql.js
import mysql from 'mysql2/promise';
export const mysqlconn = await mysql.createConnection({
host: '<myhost>',
user: 'root',
password: 'mypassword',
database: 'mydatabase'
});
3. Create an API endpoint
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
}
}
Which is better? The above code or this code?
1. Install mysql2 package
npm install --save mysql2
2. Setup MySQL connection
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. Create an API endpoint
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
}
}
You can also set up MySQL connection in hooks.
Refer: https://github.com/sveltejs/kit/issues/1538#issuecomment-1002106271
may something like knex works?
// in /src/lib/db.js
import knex from 'knex'
export default knex({
client: 'mysql',
version: '5.7',
connection: {
host: '127.0.0.1',
port: 3306,
user: 'root',
password: '',
database: 'library'
},
})
anywhere in endpoint
// in /src/routes/api/books.js
import db from '$lib/db'
// get all books
export const get = async request => {
const books= await db.select().from('books')
if (voters) {
return {
body: {
books
}
}
}
// else
}
// add a book
export const post = async ({ body }) => {
const added = await db
.insert({
title: body.get('title'),
author: body.get('author'),
// .insert(JSON.parse(body)) // or you can send JSON.stringfy(dataObject)
.into('admins')
if (added) {
return {
status: 200,
body: {
message: 'A book added successfully'
}
}
}
// else
}
you can fetch it the way you like,,
this is may not appropriate answer, i just found it last weekend