[error: NodeJS & SQLite] An error occurred: TypeError: SQLite3 can only bind numbers, strings, bigints, buffers, and null

NodeJS를 사용하여 SQLite Dataabase를 연결하는 중이었다.
서버 실행 시 아래와 같은 에러가 발생했다.

 

An error occurred: TypeError: SQLite3 can only bind numbers, strings, bigints, buffers, and null
    at file:///C:/Users/Owner/Desktop/engine_templates/dist/app.js:88:22
    at new Promise (<anonymous>)
    at selectIFC (file:///C:/Users/Owner/Desktop/engine_templates/dist/app.js:86:12)
    at main (file:///C:/Users/Owner/Desktop/engine_templates/dist/app.js:232:15)
    at file:///C:/Users/Owner/Desktop/engine_templates/dist/app.js:244:1
    at ModuleJob.run (node:internal/modules/esm/module_job:222:25)
    at async ModuleLoader.import (node:internal/modules/esm/loader:316:24)
    at async asyncRunEntryPointWithESMLoader (node:internal/modules/run_main:123:5)

 

 

에러가 발생했을 때의 app.ts 파일

import express, { Application } from "express";
import cors from 'cors';
import path from 'path';
import fs from 'fs';
import Database from "better-sqlite3";
import { fileURLToPath } from 'url';
import { func } from "three/examples/jsm/nodes/Nodes.js";


const app: Application = express();
const port: number = 3000;

app.use(express.json());
app.use(cors());

app.get('/', function(req, res) {
  res.send('Hello World')
});

app.listen(port, () => {
  console.log(`Connected successfully on port ${port}`)
});


const db = new Database('testDB.db');

const ifcSQL = `
  CREATE TABLE IF NOT EXISTS ifc (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(255),
    content BLOB
  )
`;


const projectSQL = `
  CREATE TABLE IF NOT EXISTS project (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(255),
    description VARCHAR(255),
    status VARCHAR(255),
    finishDate DATE,
    FOREIGN KEY (project_ifc) REFERENCES ifc(id)
  )
`;

// 파일 경로 
const __dirname = fileURLToPath(new URL(".", import.meta.url));
const filePath = path.join(__dirname, '../../sampleIFC/small.ifc');

async function setupIfcDatabase() {
    return new Promise<void>(() => {
        db.exec(ifcSQL);
    });
}

async function insertIfcSQL() {
  return new Promise<void>(() => {
    fs.readFile(filePath, (err) => {
      if (err) {
        console.error('Error reading the file:', err);
        return;
      }

      const insertIfcSQL = `
        INSERT INTO ifc (name, content)
        VALUES (?, ?)
      `;

      db.exec(insertIfcSQL);
    });
  });
}

async function selectIFC() {
    return new Promise<void>((resolve, reject) => {
        const selectIFCSQL = db.prepare("SELECT * FROM IFC WHERE id = ?");
        selectIFCSQL.get(1, (err: Error | null, row: any) => {
        if (err) {
            console.error(err.message);
            reject(err);
        } else {
            console.log('Retrieved row:', row);
            resolve();
        }
        });
    });
}



async function setupProjectDatabase() {
    return new Promise<void>(() => {
      db.exec(projectSQL);
    });
}

async function insertProject() {
    return new Promise<void>(() => {
      const insertProjectSQL = `
        INSERT INTO project (name, description, status, finishDate, project_ifc)
        VALUES ("project", "description", "pending", datetime('now'), 1)
      `;
    
      db.exec(insertProjectSQL);
      });
  }

  async function selectProject() {
    return new Promise<void>((resolve, reject) => {
        console.log("selectProject");
      const selectProjectSQL = db.prepare("SELECT * FROM project WHERE id = ?");
      selectProjectSQL.get(1, (err: Error | null, row: any) => {
        if (err) {
          console.error(err.message);
          reject(err);
        } else {
          console.log('Retrieved row:', row);
          resolve();
        }
      });
    });
  }
  


async function closeDatabase() {
    db.close();
    console.log("Close Databse");
}

async function main() {
  try {
    setupIfcDatabase();
    await insertIfcSQL();
    await selectIFC();
    
    await setupProjectDatabase();
    await insertProject();
    await selectProject();
  } catch (error) {
    console.error('An error occurred:', error);
  } finally {
    await closeDatabase();
  }
}

main();

 

 

 

오류를 해석해보니, 데이터베이스 쿼리에 전달된 값의 타입이 SQLite에서 지원하지 않아서 발생하는 오류이다.

async 함수와 Promise 타입을 써서 발생한 오류인 것 같았다.

그래서 아래와 같이 코드를 수정하니깐 잘 동작했다.

 

에러 수정 후 app.ts 파일

import express, { Application } from "express";
import cors from 'cors';
import path from 'path';
import fs from 'fs';
import Database from "better-sqlite3";
import { fileURLToPath } from 'url';
import { func } from "three/examples/jsm/nodes/Nodes.js";


const app: Application = express();
const port: number = 3000;

app.use(express.json());
app.use(cors());

app.get('/', function(req, res) {
  res.send('Hello World')
});

app.listen(port, () => {
  console.log(`Connected successfully on port ${port}`)
});

const db = new Database('testDB.db');

const ifcSQL = `
  CREATE TABLE IF NOT EXISTS ifc (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    content BLOB
  )
`;


const projectSQL = `
  CREATE TABLE IF NOT EXISTS project (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    description TEXT,
    status TEXT,
    finishDate TEXT,
    project_ifc INTEGER,
    FOREIGN KEY (project_ifc) REFERENCES ifc(id)
  )
`;


// 파일 경로 
const __dirname = fileURLToPath(new URL(".", import.meta.url));
const filePath = path.join(__dirname, '../../sampleIFC/small.ifc');

function setupIfcDatabase() {
    db.exec(ifcSQL);
    console.log('IFC Table created or already exists.');
}

function insertIfcSQL() {
    try {
        const fileContent = fs.readFileSync(filePath);
        const insertIfcSQL = `
            INSERT INTO ifc (name, content)
            VALUES (?, ?)
        `;
        db.prepare(insertIfcSQL).run("small.ifc", fileContent);
        console.log("IFC file inserted into the database.");
        } catch (err) {
        console.error('Error reading or inserting the file:', err);
    }
}

function selectIFC() {
    try {
      const selectIFCSQL = db.prepare("SELECT * FROM ifc WHERE id = ?");
      const row = selectIFCSQL.get(1);
      if (row) {
        console.log('Retrieved row:', row);
      } else {
        console.log('No row found with the given ID.');
      }
    } catch (err) {
      console.error('Error selecting IFC:', err);
    }
}

function setupProjectDatabase() {
    db.exec(projectSQL);
    console.log('Project Table created or already exists.');
}

function insertProject() {
    try {
        const insertProjectSQL = `
          INSERT INTO project (name, description, status, finishDate, project_ifc)
          VALUES (?, ?, ?, ?, ?)
        `;
        db.prepare(insertProjectSQL).run("project", "description", "pending", "2024-11-13", 1);
        console.log("Project inserted into the database.");
      } catch (err) {
        console.error('Error inserting project:', err);
      }
  }

 function selectProject() {
    try {
      const selectProjectSQL = db.prepare("SELECT * FROM project WHERE id = ?");
      const row = selectProjectSQL.get(1);
      if (row) {
        console.log('Retrieved row:', row);
      } else {
        console.log('No row found with the given ID.');
      }
    } catch (err) {
      console.error('Error selecting project:', err);
    }
  }
  
function closeDatabase() {
    db.close();
    console.log("Close Database.");
}

function main() {
  try {
    setupIfcDatabase();
    insertIfcSQL();
    selectIFC();

    setupProjectDatabase();
    insertProject();
    selectProject();
    
  } catch (error) {
    console.error('An error occurred:', error);
  } finally {
    closeDatabase();
  }
}

main();