joo
joo
NNuxt
Created by joo on 6/21/2024 in #❓・help
Noob question - How to read .db file in production?
Hi! I'm working on a website that displays products based on a sqlite .db file. It works in dev mode but after deploying the pages can't seem to access the .db file. How i've set it up: .db file is placed in /data folder API performs sql query on .db file and returns necessary data for display:
import sqlite3 from 'sqlite3';

const db = new sqlite3.Database('data/portable_monitors.db');

export default defineEventHandler(async (event) => {
return new Promise((resolve, reject) => {
const query = `
SELECT MAX(opinion_count) as max_opinion_count
FROM (
SELECT COUNT(*) as opinion_count
FROM opinions
GROUP BY monitor_id
)
`;
db.get(query, [], (err, row) => {
if (err) {
reject(err);
} else {
resolve(row);
}
});
});
});
import sqlite3 from 'sqlite3';

const db = new sqlite3.Database('data/portable_monitors.db');

export default defineEventHandler(async (event) => {
return new Promise((resolve, reject) => {
const query = `
SELECT MAX(opinion_count) as max_opinion_count
FROM (
SELECT COUNT(*) as opinion_count
FROM opinions
GROUP BY monitor_id
)
`;
db.get(query, [], (err, row) => {
if (err) {
reject(err);
} else {
resolve(row);
}
});
});
});
Initially it worked on dev but didn't work when I ran "npm run build" and then "npm run preview". I realised that somehow the .db file wasn't included in the .output folder. So I added a script to manually copy and paste the data folder with the .db file to the .output folder.
import fs from 'fs';
import path from 'path';
import { fileURLToPath } from 'url';
import { dirname } from 'path';

const __filename = fileURLToPath(import.meta.url);
const __dirname = dirname(__filename);

const src = path.join(__dirname, '..', 'data', 'portable_monitors.db');
const dest = path.join(__dirname, '..', '.output', 'data', 'portable_monitors.db');

fs.mkdirSync(path.dirname(dest), { recursive: true });

fs.copyFileSync(src, dest);
import fs from 'fs';
import path from 'path';
import { fileURLToPath } from 'url';
import { dirname } from 'path';

const __filename = fileURLToPath(import.meta.url);
const __dirname = dirname(__filename);

const src = path.join(__dirname, '..', 'data', 'portable_monitors.db');
const dest = path.join(__dirname, '..', '.output', 'data', 'portable_monitors.db');

fs.mkdirSync(path.dirname(dest), { recursive: true });

fs.copyFileSync(src, dest);
And edited the "build" script in package.json to run this on npm run build after nuxt build After making these changes, everything worked when i previewed with npm run preview However, when I deployed it to cloudflare pages it doesn't work. I feel like I'm missing something? I'm just trying to read and display a .db file.
1 replies