Node.js SQLite 연동 완벽 가이드

 

Node.js에서 SQLite 데이터베이스를 연동하는 방법을 알아봅니다. 파일 기반의 경량 데이터베이스인 SQLite는 별도의 서버 설치 없이 사용할 수 있어 프로토타입 개발, 임베디드 애플리케이션, 소규모 프로젝트에 적합합니다.

1. SQLite 소개와 사용 사례

SQLite는 서버리스, 설정이 필요 없는 자체 포함형 SQL 데이터베이스 엔진입니다. 데이터베이스 전체가 단일 파일로 저장되며, 크로스 플랫폼을 지원합니다.

SQLite 주요 특징:

• 서버 프로세스 불필요 (Serverless)
• 별도 설정 없이 바로 사용 가능 (Zero Configuration)
• 단일 파일로 전체 데이터베이스 저장
• ACID 트랜잭션 지원
• 최대 281TB 데이터베이스 크기 지원

적합한 사용 사례:

• Electron 데스크톱 애플리케이션
• 모바일 앱 로컬 저장소
• 테스트 및 프로토타입 개발
• 임베디드 시스템
• 소규모 웹사이트 (동시 접속 10명 이하)
• 데이터 분석 및 임시 저장소

부적합한 사용 사례:

• 고트래픽 웹 애플리케이션
• 클라이언트/서버 구조가 필요한 경우
• 대규모 동시 쓰기 작업

2. better-sqlite3 vs sqlite3 패키지 비교

Node.js에서 SQLite를 사용할 때 주로 두 가지 패키지를 선택합니다.

sqlite3 패키지

npm install sqlite3

특징:

• 비동기 API 기반 (콜백, Promise)
• Node.js 공식 sqlite3 바인딩
• 가장 오래되고 널리 사용됨
• npm 주간 다운로드: 약 100만 이상

const sqlite3 = require('sqlite3').verbose();

const db = new sqlite3.Database('./mydb.sqlite', (err) => {
  if (err) console.error(err.message);
  console.log('Connected to SQLite database.');
});

// 비동기 쿼리 (콜백 방식)
db.all('SELECT * FROM users', [], (err, rows) => {
  if (err) throw err;
  console.log(rows);
});

better-sqlite3 패키지

npm install better-sqlite3

특징:

• 동기 API 기반
• 더 빠른 성능 (2~5배)
• 간결한 API
• 트랜잭션 처리 용이
• Worker Threads와 함께 사용 권장

const Database = require('better-sqlite3');

const db = new Database('./mydb.sqlite');

// 동기 쿼리
const rows = db.prepare('SELECT * FROM users').all();
console.log(rows);

성능 비교

항목	better-sqlite3	sqlite3
API 방식	동기	비동기
단일 쿼리 성능	빠름	보통
대량 INSERT	매우 빠름	느림
메모리 사용량	적음	보통
코드 복잡도	낮음	높음

선택 기준:

• 간단한 동기 작업, 성능 중시: better-sqlite3
• 기존 비동기 코드와 통합, async/await 선호: sqlite3

3. 데이터베이스 연결 및 생성

better-sqlite3로 연결

const Database = require('better-sqlite3');

// 파일 기반 데이터베이스 생성/연결
const db = new Database('myapp.db');

// 옵션과 함께 연결
const dbWithOptions = new Database('myapp.db', {
  readonly: false,           // 읽기 전용 모드
  fileMustExist: false,      // 파일이 없으면 에러
  timeout: 5000,             // 잠금 대기 시간 (ms)
  verbose: console.log       // 쿼리 로깅
});

// 데이터베이스 닫기
db.close();

sqlite3로 연결

const sqlite3 = require('sqlite3').verbose();

// 파일 기반 데이터베이스 생성/연결
const db = new sqlite3.Database('./myapp.db', sqlite3.OPEN_READWRITE | sqlite3.OPEN_CREATE, (err) => {
  if (err) {
    console.error('Database connection error:', err.message);
  } else {
    console.log('Connected to SQLite database');
  }
});

// 데이터베이스 닫기
db.close((err) => {
  if (err) console.error(err.message);
  console.log('Database connection closed');
});

테이블 생성

// better-sqlite3
const db = new Database('myapp.db');

db.exec(`
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
  )
`);

db.exec(`
  CREATE TABLE IF NOT EXISTS posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    title TEXT NOT NULL,
    content TEXT,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
  )
`);

반응형

4. 동기/비동기 API 사용법

better-sqlite3 (동기 API)

const Database = require('better-sqlite3');
const db = new Database('sync-example.db');

// 단일 쿼리 실행
const info = db.prepare('INSERT INTO users (username, email) VALUES (?, ?)').run('john', 'john@example.com');
console.log('Inserted ID:', info.lastInsertRowid);
console.log('Changes:', info.changes);

// 단일 행 조회
const user = db.prepare('SELECT * FROM users WHERE id = ?').get(1);
console.log(user);

// 모든 행 조회
const allUsers = db.prepare('SELECT * FROM users').all();
console.log(allUsers);

// 이터레이터로 조회 (대량 데이터)
for (const row of db.prepare('SELECT * FROM users').iterate()) {
  console.log(row);
}

sqlite3 (비동기 API)

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database('./async-example.db');

// 콜백 방식
db.run('INSERT INTO users (username, email) VALUES (?, ?)', ['john', 'john@example.com'], function(err) {
  if (err) return console.error(err.message);
  console.log('Inserted ID:', this.lastID);
  console.log('Changes:', this.changes);
});

// 단일 행 조회
db.get('SELECT * FROM users WHERE id = ?', [1], (err, row) => {
  if (err) return console.error(err.message);
  console.log(row);
});

// 모든 행 조회
db.all('SELECT * FROM users', [], (err, rows) => {
  if (err) return console.error(err.message);
  console.log(rows);
});

sqlite3를 Promise로 래핑

const sqlite3 = require('sqlite3').verbose();

class AsyncDatabase {
  constructor(filename) {
    this.db = new sqlite3.Database(filename);
  }

  run(sql, params = []) {
    return new Promise((resolve, reject) => {
      this.db.run(sql, params, function(err) {
        if (err) reject(err);
        else resolve({ lastID: this.lastID, changes: this.changes });
      });
    });
  }

  get(sql, params = []) {
    return new Promise((resolve, reject) => {
      this.db.get(sql, params, (err, row) => {
        if (err) reject(err);
        else resolve(row);
      });
    });
  }

  all(sql, params = []) {
    return new Promise((resolve, reject) => {
      this.db.all(sql, params, (err, rows) => {
        if (err) reject(err);
        else resolve(rows);
      });
    });
  }

  close() {
    return new Promise((resolve, reject) => {
      this.db.close((err) => {
        if (err) reject(err);
        else resolve();
      });
    });
  }
}

// 사용 예시
async function main() {
  const db = new AsyncDatabase('./async-wrapped.db');

  await db.run('CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)');
  const result = await db.run('INSERT INTO users (name) VALUES (?)', ['Alice']);
  console.log('Inserted:', result.lastID);

  const users = await db.all('SELECT * FROM users');
  console.log(users);

  await db.close();
}

main().catch(console.error);

5. CRUD 작업 예제

better-sqlite3 CRUD

const Database = require('better-sqlite3');
const db = new Database('crud-example.db');

// 테이블 생성
db.exec(`
  CREATE TABLE IF NOT EXISTS products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    price REAL NOT NULL,
    stock INTEGER DEFAULT 0,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
  )
`);

// CREATE - 단일 삽입
const insertProduct = db.prepare('INSERT INTO products (name, price, stock) VALUES (?, ?, ?)');
const result = insertProduct.run('노트북', 1500000, 10);
console.log('생성된 ID:', result.lastInsertRowid);

// CREATE - 다중 삽입 (트랜잭션)
const insertMany = db.transaction((products) => {
  for (const product of products) {
    insertProduct.run(product.name, product.price, product.stock);
  }
});

insertMany([
  { name: '마우스', price: 50000, stock: 100 },
  { name: '키보드', price: 80000, stock: 50 },
  { name: '모니터', price: 350000, stock: 20 }
]);

// READ - 단일 조회
const getProduct = db.prepare('SELECT * FROM products WHERE id = ?');
const product = getProduct.get(1);
console.log('단일 조회:', product);

// READ - 전체 조회
const getAllProducts = db.prepare('SELECT * FROM products ORDER BY id');
const allProducts = getAllProducts.all();
console.log('전체 조회:', allProducts);

// READ - 조건 조회
const getByPriceRange = db.prepare('SELECT * FROM products WHERE price BETWEEN ? AND ?');
const filtered = getByPriceRange.all(50000, 100000);
console.log('가격 필터:', filtered);

// UPDATE
const updateProduct = db.prepare('UPDATE products SET price = ?, stock = ? WHERE id = ?');
const updateResult = updateProduct.run(55000, 95, 2);
console.log('업데이트된 행:', updateResult.changes);

// DELETE
const deleteProduct = db.prepare('DELETE FROM products WHERE id = ?');
const deleteResult = deleteProduct.run(1);
console.log('삭제된 행:', deleteResult.changes);

db.close();

sqlite3 CRUD (async/await)

const sqlite3 = require('sqlite3').verbose();
const { promisify } = require('util');

const db = new sqlite3.Database('./crud-async.db');
const dbRun = promisify(db.run.bind(db));
const dbGet = promisify(db.get.bind(db));
const dbAll = promisify(db.all.bind(db));

async function crudExample() {
  // 테이블 생성
  await dbRun(`
    CREATE TABLE IF NOT EXISTS products (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      price REAL NOT NULL,
      stock INTEGER DEFAULT 0
    )
  `);

  // CREATE
  await dbRun('INSERT INTO products (name, price, stock) VALUES (?, ?, ?)', ['노트북', 1500000, 10]);

  // READ
  const product = await dbGet('SELECT * FROM products WHERE id = ?', [1]);
  console.log('조회:', product);

  const allProducts = await dbAll('SELECT * FROM products');
  console.log('전체:', allProducts);

  // UPDATE
  await dbRun('UPDATE products SET price = ? WHERE id = ?', [1400000, 1]);

  // DELETE
  await dbRun('DELETE FROM products WHERE id = ?', [1]);

  db.close();
}

crudExample().catch(console.error);

6. Prepared Statement 사용

Prepared Statement는 SQL 인젝션 방지와 성능 향상에 필수적입니다.

better-sqlite3 Prepared Statement

const Database = require('better-sqlite3');
const db = new Database('prepared.db');

db.exec(`
  CREATE TABLE IF NOT EXISTS employees (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    department TEXT,
    salary INTEGER
  )
`);

// Prepared Statement 생성
const insert = db.prepare('INSERT INTO employees (name, department, salary) VALUES (@name, @department, @salary)');
const selectByDept = db.prepare('SELECT * FROM employees WHERE department = ?');
const selectBySalary = db.prepare('SELECT * FROM employees WHERE salary > :minSalary AND salary < :maxSalary');

// 네임드 파라미터 사용
insert.run({ name: '김철수', department: '개발', salary: 5000000 });
insert.run({ name: '이영희', department: '디자인', salary: 4500000 });
insert.run({ name: '박지민', department: '개발', salary: 6000000 });

// 위치 파라미터
const developers = selectByDept.all('개발');
console.log('개발팀:', developers);

// 네임드 파라미터로 조회
const highPaid = selectBySalary.all({ minSalary: 4000000, maxSalary: 5500000 });
console.log('급여 범위:', highPaid);

// Statement 재사용으로 성능 향상
const insertEmployee = db.prepare('INSERT INTO employees (name, department, salary) VALUES (?, ?, ?)');

const employees = [
  ['홍길동', '마케팅', 4000000],
  ['정수연', '인사', 3800000],
  ['최민수', '개발', 5500000]
];

// 트랜잭션으로 대량 삽입
const insertAll = db.transaction((list) => {
  for (const emp of list) {
    insertEmployee.run(...emp);
  }
  return list.length;
});

const count = insertAll(employees);
console.log(`${count}명 삽입 완료`);

// Prepared Statement 바인딩 해제 (필요시)
insert.bind({ name: '기본값', department: '미정', salary: 0 });

db.close();

트랜잭션 활용

const Database = require('better-sqlite3');
const db = new Database('transaction.db');

db.exec(`
  CREATE TABLE IF NOT EXISTS accounts (
    id INTEGER PRIMARY KEY,
    name TEXT,
    balance INTEGER
  )
`);

// 초기 데이터
db.prepare('INSERT OR REPLACE INTO accounts (id, name, balance) VALUES (?, ?, ?)').run(1, 'Alice', 10000);
db.prepare('INSERT OR REPLACE INTO accounts (id, name, balance) VALUES (?, ?, ?)').run(2, 'Bob', 5000);

// 계좌 이체 트랜잭션
const transfer = db.transaction((fromId, toId, amount) => {
  const from = db.prepare('SELECT balance FROM accounts WHERE id = ?').get(fromId);
  const to = db.prepare('SELECT balance FROM accounts WHERE id = ?').get(toId);

  if (!from || !to) {
    throw new Error('계좌를 찾을 수 없습니다');
  }

  if (from.balance < amount) {
    throw new Error('잔액이 부족합니다');
  }

  db.prepare('UPDATE accounts SET balance = balance - ? WHERE id = ?').run(amount, fromId);
  db.prepare('UPDATE accounts SET balance = balance + ? WHERE id = ?').run(amount, toId);

  return { from: fromId, to: toId, amount };
});

try {
  const result = transfer(1, 2, 3000);
  console.log('이체 완료:', result);
} catch (err) {
  console.error('이체 실패:', err.message);
}

// 결과 확인
const accounts = db.prepare('SELECT * FROM accounts').all();
console.log('계좌 현황:', accounts);

db.close();

7. 인메모리 데이터베이스 활용

인메모리 데이터베이스는 디스크 I/O 없이 메모리에서만 동작하여 매우 빠릅니다. 테스트, 캐싱, 임시 데이터 처리에 유용합니다.

better-sqlite3 인메모리 데이터베이스

const Database = require('better-sqlite3');

// 인메모리 데이터베이스 생성
const memDb = new Database(':memory:');

// 테이블 생성 및 데이터 삽입
memDb.exec(`
  CREATE TABLE cache (
    key TEXT PRIMARY KEY,
    value TEXT,
    expires_at INTEGER
  )
`);

// 캐시 클래스 구현
class SQLiteCache {
  constructor() {
    this.db = new Database(':memory:');
    this.db.exec(`
      CREATE TABLE cache (
        key TEXT PRIMARY KEY,
        value TEXT,
        expires_at INTEGER
      )
    `);

    this.setStmt = this.db.prepare('INSERT OR REPLACE INTO cache (key, value, expires_at) VALUES (?, ?, ?)');
    this.getStmt = this.db.prepare('SELECT value, expires_at FROM cache WHERE key = ?');
    this.deleteStmt = this.db.prepare('DELETE FROM cache WHERE key = ?');
    this.cleanupStmt = this.db.prepare('DELETE FROM cache WHERE expires_at < ?');
  }

  set(key, value, ttlSeconds = 3600) {
    const expiresAt = Date.now() + (ttlSeconds * 1000);
    this.setStmt.run(key, JSON.stringify(value), expiresAt);
  }

  get(key) {
    const row = this.getStmt.get(key);
    if (!row) return null;
    if (row.expires_at < Date.now()) {
      this.deleteStmt.run(key);
      return null;
    }
    return JSON.parse(row.value);
  }

  delete(key) {
    this.deleteStmt.run(key);
  }

  cleanup() {
    return this.cleanupStmt.run(Date.now()).changes;
  }

  close() {
    this.db.close();
  }
}

// 사용 예시
const cache = new SQLiteCache();

cache.set('user:1', { name: 'John', email: 'john@example.com' }, 60);
cache.set('user:2', { name: 'Jane', email: 'jane@example.com' }, 120);

console.log('캐시 조회:', cache.get('user:1'));
console.log('없는 키:', cache.get('user:999'));

cache.close();

테스트용 인메모리 데이터베이스

const Database = require('better-sqlite3');

// 테스트 유틸리티
function createTestDatabase() {
  const db = new Database(':memory:');

  // 스키마 설정
  db.exec(`
    CREATE TABLE users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      username TEXT UNIQUE NOT NULL,
      email TEXT NOT NULL
    );

    CREATE TABLE posts (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      user_id INTEGER,
      title TEXT NOT NULL,
      content TEXT,
      FOREIGN KEY (user_id) REFERENCES users(id)
    );
  `);

  return db;
}

// 테스트 예시 (Jest 스타일)
function testUserCreation() {
  const db = createTestDatabase();

  const insert = db.prepare('INSERT INTO users (username, email) VALUES (?, ?)');
  const result = insert.run('testuser', 'test@example.com');

  console.assert(result.lastInsertRowid === 1, 'ID should be 1');
  console.assert(result.changes === 1, 'Changes should be 1');

  const user = db.prepare('SELECT * FROM users WHERE id = ?').get(1);
  console.assert(user.username === 'testuser', 'Username should match');

  db.close();
  console.log('테스트 통과');
}

testUserCreation();

파일과 메모리 간 복사

const Database = require('better-sqlite3');

// 파일 데이터베이스를 메모리로 로드
function loadToMemory(filePath) {
  const fileDb = new Database(filePath, { readonly: true });
  const memDb = new Database(':memory:');

  // 백업 기능으로 복사
  fileDb.backup(memDb);
  fileDb.close();

  return memDb;
}

// 메모리 데이터베이스를 파일로 저장
function saveToFile(memDb, filePath) {
  const fileDb = new Database(filePath);
  memDb.backup(fileDb);
  fileDb.close();
}

// 사용 예시
const memDb = new Database(':memory:');
memDb.exec('CREATE TABLE test (id INTEGER PRIMARY KEY, value TEXT)');
memDb.prepare('INSERT INTO test (value) VALUES (?)').run('Hello World');

saveToFile(memDb, './backup.db');
console.log('메모리 데이터베이스가 파일로 저장됨');

memDb.close();

결론

Node.js에서 SQLite를 연동하면 별도의 데이터베이스 서버 없이 파일 기반으로 데이터를 관리할 수 있습니다. 성능과 간결한 동기 API가 필요하다면 better-sqlite3를, 기존 비동기 코드와의 통합이 중요하다면 sqlite3 패키지를 선택하세요. Prepared Statement와 트랜잭션을 적극 활용하면 안전하고 효율적인 데이터베이스 작업이 가능합니다.