joo Posts - Answer Overflow

joo

•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

Gaming

Programming