본문 바로가기
Database/MySQL, SQL

SQLite NodeJS 모듈 이용해서 CRUD 구현하기 # DBBrowser SQLite3

by Developer88 2022. 11. 8.
반응형

오늘은 SQLite 데이터베이스에 대해 알아보고,

NodeJS에서 SQLite3 모듈을 활용해,

CRUD (생성, 읽기, 업데이트, 삭제) 작업을 구현해 보겠습니다.

1. SQLite

1-1. SQLite

SQLite는 C언어로 개발된 라이브러리입니다.

크기가 작고 속도가 빠르며 상대적으로 안정적인 특성을 가진 SQL 데이터베이스 엔진인데요.

이러한 장점 덕분에 전 세계에서 가장 널리 사용되는 데이터베이스 엔진 중 하나로 자리잡았습니다.

 

 


SQLite의 핵심 특징 중 하나는 모든 데이터베이스 정보를 단 하나의 파일에 저장한다는 점입니다. 

이로 인해 여러 테이블, 인덱스, 스키마 등을 포함한 모든 데이터를 한 파일 안에 담을 수 있으며, 

데이터베이스 서버를 별도로 구동할 필요 없이 파일 하나로 쉽게 데이터베이스를 전송하고 공유할 수 있습니다. 

또한, SQL 쿼리 실행, 트랜잭션 처리, ACID(Atomicity, Consistency, Isolation, Durability) 속성을 포함한 데이터베이스 관리의 기본 기능들도 모두 지원하기 때문에 데이터 관리 시스템을 구축할 수 있습니다.

 

SQLite는 다양한 플랫폼에서 소규모 프로젝트부터 대규모 응용 프로그램까지 광범위하게 활용할 수 있는데요.

공식 문서에 따르면, SQLite는 적은 규모에서 중간 규모의 트래픽을 가진 웹사이트에 우수한 성능을 제공하는 데이터베이스 엔진이라고 합니다. 하루에 10만 건 이하의 히트를 기록하는 사이트에서 효과적으로 작동할 수 있고, 실제로는그보다 최대 10배 많은 트래픽도 충분히 처리할 수 있다고 합니다. 

 

 

2. SQLite3 모듈 설치하기

SQLite를 실제로 사용하기 위해 NodeJS 환경에서 SQLite 모듈을 설치하고,

프로젝트에 import하는 방법을 알아보겠습니다. 

2-1. 모듈 설치

아래 NPM 명령어를 실행해서, sqlite3 모듈을 설치해 줍니다.

 

npm install sqlite3 --save

 

2-2. Import

모듈이 설치 되었으니, 아래 코드를 이용해 sqlite3를 import 해 줍니다.

 

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

 

verbose() 메서드는 추가적인 메시지를 로그에 출력해서,

개발 과정에서 발생하는 문제를 쉽게 추적할 수 있게 해줍니다.

 

 

3. Data Types

3-1. 5가지 Data Types

SQLite를 사용하기 전에, 지원하는 데이터 타입을 이해하는 것이 중요합니다. 

SQLite는 비교적 심플한 데이터 타입 시스템을 가지고 있는데요.

심플하게 다음의 5가지 기본 데이터 타입만을 지원합니다.

  • NULL: 값이 없음을 나타냅니다.
  • INTEGER: 정수 값을 저장하는 데 사용되며, 크기는 저장되는 값에 따라 변할 수 있음(0, 1, 2, 3, 4, 6, 8바이트 중 하나로 저장)
  • REAL: 부동소수점 숫자를 저장하는 데 사용. 일반적으로 실수를 나타내는 데 쓰임
  • TEXT: 텍스트 데이터를 저장하는 데 사용되며, 문자열 정보를 담기 위한 타입으로 UTF-8,UTF16BE,UTF-16LE 중 하나로 저장
  • BLOB: Binary Large Object의 약자로, 이미지나 비디오 파일과 같은 이진 데이터를 그대로 저장하는 데 사용

 

 

데이터 타입을 선택할 때는 저장하려는 데이터의 성격을 고려하여 가장 적합한 타입을 사용해야 하겠지요.

 

3-2. Boolean

위에서 정리한 데이터타입에는 Boolean 데이터 타입이 보이지 않습니다.

Boolean 값은 어떻게 하라는 것 일까요?

만약 이게 정말 없다면, true, false는 어떻게 저장할까요?

 

SQLite에서는 Boolean 값을 표현하기 위해 정수 값인 0과 1을 사용합니다.

이는 SQLite가 Boolean 값을 처리하는 관례적인 방법입니다.

 

구분 정수 데이터(Integer)
TRUE 1
FALSE 0

 

위의 표와 같이 0이 false이고 1은 true 입니다.

 

 

다행히도 SQLite 3.23.0 버전부터는 'TRUE'와 'FALSE'라는 키워드를 인식하게 되었습니다.

다만, 실제로 데이터베이스 내에서 정수 0과 1로 저장되며,

Boolean 값을 표현하기 위한 또 다른 방법이라고 생각하면 됩니다.

 타입 자체는 여전히 Integer로 처리되지만, 

'TRUE'와 'FALSE'라는 표현을 사용함으로써 코드의 가독성을 높여주는 정도이지요.

 

 

3-3. Date 와 Time

SQLite는 날짜와 시간을 저장하기 위한 별도의 데이터 타입을 제공하지 않습니다. 

대신에, 날짜와 시간을 TEXT, REAL, INTEGER 값으로 저장할 수 있고요.

SQLite의 내장 날짜 및 시간 함수들을 사용해 이러한 형식 사이에서 자유롭게 변환할 수 있습니다.

각 형식은 다음과 같이 사용됩니다:

 

구분 내용
TEXT ISO8601 형식의 문자열(YYYY-MM-DD HH:MM:SS.SSS)로 날짜와 시간을 표현합니다. 

이 형식은 국제 표준에 따라 정해진 문자열 형태로, 가독성이 높고 직관적입니다.
REAL 줄리안 일 수(Julian day)로 표현된 날짜와 시간입니다.
기원전 4714년 11월 24일 정오부터 경과된 일 수를 의미합니다.

이렇게 하면 날짜 계산이나 비교가 쉬워지는 장점이 있습니다. 
예를 들어 2023년 4월 5일과 2023년 4월 1일의 차이는 줄리안일 기준으로 2,459,945 - 2,459,941 = 4가 되어,
간단히 계산할 수 있습니다.
INTEGER 유닉스 시간으로, 1970년 1월 1일 00:00:00 UTC 이후로 경과된 초 수를 저장해서 사용합니다.
언어나 플랫폼과 상관없이 표준적으로 사용되는 Unix Timestamp는 날짜 계산에 사용할 수 있습니다.
다만, 1970년 이전의 날짜는 저장할 수 없습니다.

 

이에 대한 실제 적용사례는, 아래 글5-2의 serialize의 3번째 사용 예제를 참조해 주세요.

 

 

3-4. 자동증가(Autoincrement)

보통 데이터베이스의 컬럼을 생성할 때,

유니크한 id값을 자동증가하게 해서 테이블의 각 행을 고유하게 식별하게 도와줍니다.

MySQL에서는 이를 위해 'AUTO_INCREMENT' 키워드를 사용하여 해당 컬럼을 지정하는데요.

 

SQLite에서는 컬럼이 INTEGER 타입이며 PRIMARY KEY로 지정된 경우, 

해당 컬럼에 대해 값을 명시적으로 지정하지 않으면 자동으로 값을 증가시켜줍니다. 

이는 SQLite가 자동으로 관리하는 특수한 ROWID 기능을 활용한 것입니다.

 

또한 'AUTOINCREMENT' 키워드를 명시적으로 사용할 수도 있습니다.

이 경우, SQLite는 해당 컬럼의 숫자가 이전에 사용된 값보다 항상 더 크도록 보장합니다.

 

정리하면, SQLite에서는 INTEGER 타입이면서 PRIMARY KEY로 지정된 컬럼에 자동으로 값을 증가시키는 기능을 제공하구요.

필요하다면 명시적으로 'AUTOINCREMENT' 키워드를 추가하여 사용할 수도 있습니다.

 

3-5. 동적 타이핑

SQLite는 동적 타이핑 시스템을 사용하므로,

열(컬럼)에 데이터 타입이 명시적으로 지정되어 있지 않다면,

해당 데이터에 가장 적합한 데이터 타입을 자동으로 선택해서 저장하려고 합니다.

이는 SQLite가 엄격한 데이터 타입을 강제하지 않기 때문에 가능한 동작입니다.

이것을 타입 유형성(Type Affinity)이라고 하는데요.

 

이러한 특징이 장점만 있는 것이 아닌데요.

아래와 같이 저장한다고 가정해 보겠습니다.

 

age의 타입은 정의되지 않았는데,

처음에는 정수 30으로 저장하고, 다음에는 TEXT인 문자로 저장합니다.

 

INSERT INTO users (name, age) VALUES ('John Doe', 30);
INSERT INTO users (name, age) VALUES ('Jane Doe', 'Thirty');

 

이렇게 되면,

삽입되는 데이터의 타입에 따라 동적으로 데이터 타입이 결정됩니다. 

첫 번째 INSERT 문에서는 age가 정수로 취급되며, 두 번째 INSERT 문에서는 문자열로 취급됩니다.

SQLite의 동적 타이핑 방식 덕분에 서로 다른 데이터 타입을 하나의 열에 저장할 수 있지만, 

데이터 무결성을 위해서는 처음부터 열의 타입을 명시하는 것이 안전합니다.

4. DBBrowser 와 SQLiteStudio프로그램 설치

SQLite를 터미널에서만 다루는 것이 아니라,

GUI프로그램에서 직관적으로 테이블을 보면서 사용할 수 있도록 해주는 프로그램들이 있습니다.

DBBrowser 와 SQLiteStuido가 있는데요. 

둘 중 사용하기 편한 프로그램을 사용하시면 됩니다.

GUI프로그램 사용이 필요하지 않으신 분들은 바로 '5. 주요 API들과 예제' 로 넘어가셔도 됩니다.

4-1. DBBrowser

DBBrowser프로그램을 이용하면, db파일에 담긴 데이터베이스를 확인하는데 큰 도움이 됩니다.

아래 링크에서 설치해서 사용할 수 있는데요.

맥의 경우, 애플실리콘 버전까지 따로 파일을 제공해 주고 있습니다.

>> https://sqlitebrowser.org/

 

단순히 데이터베이스 확인 뿐만이 아니고,

SQL 문법을 잘 모르는 사람이 Query문을 작성하는데 도움을 주기까지 합니다.

 

예를 들어서, 우측 상단의 New Database버튼을 누르고,

Table이름을 입력한 다음, 화살표가 가르키는 Add버튼을 누르면, 

field를 입력할 수 있는데요. 입력하면 아래와 같이 SQL문을 작성해 줍니다.

이 SQL문을 가지고 TABLE생성시에 그대로 사용할 수도 있습니다.

 

CREATE TABLE 다음에 "IF NOT EXISTS"를 붙여주면 존재하지 않을 때만 생성하도록 할 수 있습니다.

UI와 SQL문을 동시에 활용할 수 있는 것 이지요.

 

 

참고로 NN은 NotNull이구요.

PK는 PrimaryKey,

AI는 AutoIncrement,

U는 Unique입니다.

 

 

또한 Execute SQL탭에 가서 SQL문을 직접 작성한 다음, 상단의 3번째 Write Changes를 클릭해서 실행해 주어도 됩니다.

 

 

5. 주요 API들과 예제

이제 sqlite3모듈의 주요 API에 대해서 알아보겠습니다.

5-1. run

run함수는 SQL문을 실행하는 핵심 API입니다.

이 함수는 SQL 쿼리를 첫 번째 인자로 받아들이고, 

선택적으로 두 번째 인자로 SQL문 내의 placeholder에 해당하는 값을 넣어줄 수 있습니다. 

 

db.run("CREATE TABLE users (name TEXT, age INTEGER)");

db.run('INSERT INTO users (name) VALUES (?)', 'Park', function(err) {
  console.log(`새로운 데이터 ID: ${this.lastID}`);
});

db.run('UPDATE users SET name = ? WHERE id = ?', 'Park', 1, function(err) {
  console.log(`${this.changes} 변경됨`);
});

 

SQL문에서 placeholder를 사용하는 것은 SQL 쿼리를 보다 유연하게 만들고, SQL 인젝션 공격과 같은 보안 문제를 방지하는 데 도움이 됩니다.

 

SQLite에서는 SQL문의 placeholder를 집어넣는 방법으로 다음과 같은 세 가지 방법을 제공합니다:

  • ? - SQL문 내에서 값을 대체할 위치에 ?를 사용.
    • 실행 시에 '?'는 run 함수의 두 번째 인자로 전달된 값으로 순서대로 대체됩니다.
    • 예) INSERT INTO users (name, age) VALUES (?, ?)
  • $, :, @ 기호 - $, :, @ 기호를를 사용해서 placeholder에 이름을 붙일 수 있음
    • 이 방법은 SQL문 내에서 특정 값이 반복될 때 유용하게 사용할 수 있으며, 값을 객체 형태로 전달하여 매핑할 수 있음
    • 예) INSERT INTO users (name, age) VALUES ($name, $age)
  • 인덱스 기반 placeholder: ? 뒤에 숫자를 붙여 placeholder의 위치를 명시적으로 지정.
    • 같은 값이 여러 번 사용되어야 하거나, 명확한 순서를 지정하고 싶을 때 유용
    • 예: INSERT INTO users (name, age) VALUES (?1, ?2)

 

 

SQLite의 run 함수는 Database 객체를 반환하므로, 메소드 체이닝이 가능합니다.

run 함수 사용 시 콜백 function() {}을 전달하면,

콜백 내부에서 this 키워드로 lastID와 changes 프로퍼티에 접근할 수 있습니다.

  • lastID 프로퍼티: 가장 최근에 INSERT 연산으로 추가된 레코드의 ID 값
    • INSERT 연산 후에 새로 추가된 레코드의 고유 ID를 확인할 수 있습니다.
  • changes 프로퍼티: 최근 실행된 UPDATE 또는 DELETE 연산으로 영향을 받은 레코드의 수
    • UPDATE/DELETE 연산 후에 수정 또는 삭제 작업이 얼마나 많은 데이터에 영향을 미쳤는지 알 수 있음

 

 

이러한 기능은 데이터베이스 작업의 결과를 프로그래밍적으로 처리하고, 

에러 핸들링이나 후속 작업을 수행하는 데 유용한데요.

예를 들어, 사용자에게 데이터가 성공적으로 추가되었음을 알리거나, 

수정된 레코드의 수에 따라 다른 작업을 수행하도록 할 수 있습니다.

 

5-2. get() 과 all() 함수

A. get()

get()함수를 이용해서 SQL쿼리를 실행시킬수도 있는데요.

만약 결과가 없어서 비어있다면, 콜백으로 들어오는 두번째 인자인 row 는 undefined가 출력되게 됩니다.

if(row === undefined)로 알아볼 수 있겠지요.

 

 

B. all()

get()함수가 하나의 데이터를 가져왔다면, 

all()함수는 쿼리에 맞는 모든 행(rows)을 한 번에 가져와서 배열로 반환합니다.

호출되는 콜백은 다음과 같습니다.

 

function(err, rows)

 

 

문서상으로 이 함수는 모든 값을 가져와서 메모리에 저장하므로,

rows가 매우 큰 경우, 아래에서 다룰 each()함수를 사용하는 것을 추천하고 있습니다.

 

db.all("SELECT * FROM users", (err, rows) => {
  if (err) {
    throw err;
  }
  rows.forEach((row) => {
    console.log(`${row.id}: ${row.name}`);
  });
});

 

5-3. Each()

A. Each 함수

all()함수가 한번에 모든 rows들을 가져왔다면,

each()함수는 결과로 나온 row들을 한 행씩 순회하며 function(err, row){ } 콜백으로 호출해 줍니다.

결과 데이터가 많거나, 각 행을 받아서 바로 처리해야 할 때 유용합니다.


all()함수에 비해 메모리 사용량이 적고,

각 행이 처리되는 대로 바로 작업을 수행할 수 있다는 장점이 있습니다.

return되는 데이터수가 적다면, all()함수를 사용하는 것이 더 편하다고 공식문서에는 나와 있습니다.

 

 

  db.each("SELECT rowid AS id, info FROM tbl", function(err, row) {
      console.log(row.id + ": " + row.info);
  });

 

위에서 사용된 "SELECT rowid AS id, info FROM tbl" SQL문은,

tbl 테이블에서,

rowid 와 info 열을 선택하여 결과를 반환합니다.

rowid는 SQLite에서 자동으로 관리하는 유니크한 식별자로,

테이블에 명시적인 PRIMARY KEY가 없을 경우 내부적으로 사용되는 식별자입니다.

(위에서 테이블 생성시에 info열만 생성하였는데, rowid에 접근할 수 있는 것은 이러한 이유 때문입니다.)

 

위에서 SQL문으로 AS키워드가 사용되었는데요.

'rowid AS id'는,

rowid 열의 이름을 결과 세트에서 id로 변경하여 표시한다는 뜻 입니다.

 

B. 비동기로 동작

each함수는 비동기로 동작합니다.

동기적으로 처리하고 싶다고 할 경우는 어떻게 해야할까요?

each함수를 Promise로 래핑(감싸서)한 다음,

async 함수내에서 await를 사용해 동기적으로 처리 되도록 하는 것 입니다.

 

예를 들어서 SQLite에서 데이터를 가져온 다음,

특정 나이(age)이상인 사람만 보여지도록 필터링해 보겠습니다.

(실제로는 SQL문을 이렇게 하는 것이 좋지만, 가져온 데이터를 가지고 필터링해 보겠습니다.)

 

// each 함수를 프로미스로 감싸는 함수
function getUsers() {
  return new Promise((resolve, reject) => {
    let users = [];
    db.each("SELECT * FROM users", (err, row) => {
      if (err) reject(err);
      else users.push(row);
      }
    }, (err) => {
      if (err) reject(err);
      else resolve(users);
    });
  });
}

// 비동기 처리를 위한 async 함수
async function processUsers() {
  try {
    const users = await getUsers(); // 사용자 데이터를 가져옵니다.
    const filteredUsers = users.filter(user => user.age >= 18); 

    filteredUsers.forEach(user => {
      console.log(`이름: ${user.name}, 나이: ${user.age}`);
    });

  } catch (error) {
    console.error('Error:', error);
  } finally {
    db.close();
  }
}

processUsers();

 

5-4. prepare 와 finialize 함수

prepare는 준비하다는 뜻인데요.

이 함수를 이용하면 SQL 문을 미리 컴파일하고, 데이터베이스에게 이 쿼리를 실행할 준비 시킬 수 있습니다.

이러한 접근 방식은 SQL 인젝션 공격으로부터 보호해 줄 뿐만 아니라,

반복적인 쿼리 실행 시 성능을 개선하는 데에도 도움이 됩니다.

 

아래의 2단계를 거치게 되는데요.

  1. 쿼리준비: prepare 함수를 사용해 SQL 문을 미리 컴파일. 
    • 데이터를 직접 넣지는 않고, placeholder를 이용해 준비시킴
    • 예) const stmt = db.prepare("INSERT INTO users (name, age) VALUES (?, ?)");
  2. 쿼리 실행: prepare함수가 statement 객체를 return해 주는데, 이 객체에 run()함수를 실행해 주면 됨
    • 반복문을 사용하여 데이터를 순차적으로 삽입하는 경우에도, 쿼리를 매번 새로 컴파일할 필요 없이 run 함수에 value값만 전달하기만 하면 됨
      • for (let user of users) { stmt.run(user.name, user.age); }
        • 동기적으로 실행
        • 비동기적 실행: users.forEach(user => stmt.run(user.name, user.age));
          • 실행순서가 보장되지 않음

 

 

예제를 보면서 이해해 보겠습니다.

 

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database(':memory:');

db.serialize(() => {
  db.run("CREATE TABLE users (name TEXT, age INTEGER)");

  const stmt = db.prepare("INSERT INTO users (name, age) VALUES (?, ?)");

  // 데이터
  const users = [
    { name: "Alice", age: 30 },
    { name: "Bob", age: 25 },
    { name: "Charlie", age: 35 }
  ];

  // 반복문을 이용해 여러 사용자 데이터를 삽입
  for (let user of users) {
    stmt.run(user.name, user.age);
  }

  // 사용한 statement 객체 종료
  stmt.finalize();

  db.each("SELECT rowid AS id, name, age FROM users", (err, row) => {
    console.log(`${row.id}: ${row.name}, ${row.age}`);
  });
});

db.close();

 

위에서 prepare함수가 리턴해준 것을 stmt에 저장한 후,

데이터를 삽입 후, finalize()함수를 실행해 주었는데요.

이 함수는 준비된 SQL 문(statement)의 사용을 마무리할 때 호출해 줍니다.

 

이 함수가 호출되면,

준비된 SQL 문을 정리하고 관련 리소스를 해제합니다.

이 후 해당 statement는 더 이상 사용할 수 없게 됩니다.

 

이 함수는 반드시 사용해야하는 것은 아니지만,

다음 쿼리 실행 전 긴 시간의 딜레이가 예상될 때 권장됩니다.

statement 객체는 finalize되지 않으면 여러 번 호출될 수 있는데요.

더 이상 사용하지 않을 때는 자원을 해제하기 위해 finalize하는 것이 바람직합니다.

 

 

5-3. serialize

serialize 함수는 데이터베이스 작업을 순차적으로 한 줄씩 실행하려고 할 때 매우 유용합니다.

여러 데이터베이스 작업을 동기적으로 실행할 수 있으므로,

작업 간의 의존성이 있거나 순서가 중요한 경우에 특히 필요합니다. 

 

몇 가지 예제들을 보도록 하겠습니다.

아래에서 serialize 함수는 한줄씩 순차적으로 실행을 하게 됩니다.

 

db.serialize(function() {  
  // 먼저 테이블 생성
  db.run("CREATE TABLE IF NOT EXISTS tbl (info TEXT)");
  
  // 다음으로 INSERT 실행
  var stmt = db.prepare("INSERT INTO tbl VALUES (?)");
  for (var i = 0; i < 10; i++) {
      stmt.run("Some info " + i);
  }
  stmt.finalize();
  
  // 마지막으로, INSERT 된 데이터 조회
  db.each("SELECT rowid AS id, info FROM tbl", function(err, row) {
      console.log(row.id + ": " + row.info);
  });
});

 

 

두 번째 예제는 다음과 같은데요.

true와 false 를 0과 1을 이용해서 저장한 경우입니다.

 

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database('./users.db');

db.serialize(() => {
  // users 테이블 생성. isActive 컬럼은 불리언 표현을 위해 INTEGER 타입 사용
  db.run("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, isActive INTEGER)");

  // 사용자 추가 부분. isActive 컬럼에 0(거짓) 또는 1(참)을 사용
  const stmt = db.prepare("INSERT INTO users (name, isActive) VALUES (?, ?)");
  stmt.run("Kim", 1);
  stmt.run("Park", 0);
  stmt.finalize();

  // isActive가 1 (참)인 사용자 조회
  db.each("SELECT id, name, isActive FROM users WHERE isActive = 1", (err, row) => {
    if (err) return console.error(err);
    console.log(`${row.id}: ${row.name} (isActive: ${row.isActive})`);
  });
});
db.close();

 

세번째 예는 date와 time 의 타입을 구현한 예입니다.

 

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database('mydatabase.db');

db.serialize(() => {
  // users 테이블 생성
  db.run(`
    CREATE TABLE IF NOT EXISTS users (
      id INTEGER PRIMARY KEY,
      name TEXT,
      createdAt TEXT,
      updatedAt INTEGER
    )`);

  // 현재 날짜와 시간
  const now = new Date();
  const isoDate = now.toISOString(); // TEXT 형식으로 변환
  const unixTime = Math.floor(now.getTime() / 1000); // INTEGER 형식으로 변환

  // 사용자 추가
  db.run(`INSERT INTO users (name, createdAt, updatedAt) VALUES (?, ?, ?)`, [
    "Park",
    isoDate, // TEXT 형식의 날짜와 시간
    unixTime, // INTEGER 형식의 날짜와 시간
  ]);

  // 데이터 조회
  db.each("SELECT id, name, createdAt, updatedAt FROM users", (err, row) => {
    if (err) return console.error(err);
    console.log(`ID: ${row.id}, Name: ${row.name}, Created At: ${row.createdAt}, Updated At: ${new Date(row.updatedAt * 1000).toISOString()}`);
  });
});

db.close();

 

네번째 예제는, INTEGER이면서 PRIMARY KEY인경우,

값이 자동증가(AUTOINCREMENT)시켜주는 SQLite의 특징을 보여주는 예입니다.

(여기서 AUTOINCREMENT 라는 키워드를 사용했지만, 사용하지 않아도 자동증가는 동작합니다.)

 

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database('test.db');

db.serialize(() => {
  // 'users' 테이블 생성. 'id' 컬럼 자동 증가
  db.run("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)");

  // 사용자 추가
  db.run("INSERT INTO users (name) VALUES ('Kim')");
  db.run("INSERT INTO users (name) VALUES ('Park')");
  db.run("INSERT INTO users (name) VALUES ('Ahn')", function() {
    console.log(`A new row has been inserted with rowid ${this.lastID}`);
  });

  // 모든 사용자 데이터 조회
  db.all("SELECT id, name FROM users", (err, rows) => {
    if (err) return console.error(err);
    rows.forEach((row) => {
      console.log(`${row.id}: ${row.name}`);
    });
  });
});

db.close();

 

5-4. close()

데이터베이스를 다 사용한 후에는 반드시 닫아주어야 하겠지요.

close()함수가 그 역할을 합니다.

성공하면 null을 return해 주고, 문제가 생기면 error 객체를 첫번째 인자로 제공해주므로,

콜백을 이용해서 로깅을 할 수 있습니다.

 

 

db.close((err) => {
  if (err) {
    console.error('데이터베이스 종료 중 에러', err);
  } else {
    console.log('데이터베이스 커넥션 종료.');
  }
});

 

6. CRUD 구현

이제 CRUD를 구현해 볼 텐데요.

CRUD 작업을 시작하기 전에, 데이터베이스 파일과 테이블이 존재해야 합니다.

둘 다 초기에 한번 생성되면 이후에는 생성할 필요가 없는데요.

이를 설정하는 과정부터 시작해 보겠습니다.

 

6-1. DB파일 생성

NodeJS의 SQLite3모듈은 파일을 생성해주지 않습니다.

따라서 DB파일을 생성해 주어야 하는데요.

 

SQLite3의 파일확장자명은 아래 중 어떤 것으로 해도 영향을 미치지 않습니다.
이는 SQLite가 파일명을 기반으로 해서, 데이터베이스를 해석하지 않기 때문입니다.

  • .db
  • .sqlite3
  • .sqlite\

여기서는 '.db'파일명을 사용하겠습니다.

아무 데이터도 들어가지 않은 빈 파일을 확장자명 ".db"로 생성해 주어야 하겠지요.

 

A. 터미널에서 생성

가장 쉬운 방법은 원하는 폴더에서 파일을 직접 생성하는 것 입니다.

터미널에서 touch명령어로 아래와 같이 쉽게 생성할 수 있습니다.

 

touch <db이름>.db

 

B. 코드로 파일 생성

프로그램적으로 만든다면 아래와 같이 NodeJS의 File System(fs) 모듈을 이용해주면 됩니다.

writeFileSync에 데이터는 ""를 넣어주어서 빈 파일을 만들어 주었습니다.

 

const path = require('path');
const fs = require('fs');

const DB_PATH = path.resolve(__dirname, 'db', 'test.db');

// 파일 생성 로직
try {
    fs.writeFileSync(DB_PATH, { flag: 'wx' });
} catch (e) {
    if (e.code === 'EEXIST') {
      console.log('데이터베이스 파일이 이미 존재합니다.');
    } else {
      console.error(e.message);
    }
}

 

위 코드의 flag 'wx'는 다음과 같이 path가 존재한다면 fail을 시켜주는 옵션입니다.

 

파일이 이미 존재하면 Exception을 내게 됩니다.

 

아래와 같은 방법으로도 파일이 존재하는지 확인하고,

없으면 생성할 수 있습니다.

 

// 파일 생성 로직
if (!fs.existsSync(DB_PATH)) {
  try {
    fs.writeFileSync(DB_PATH, '');
    console.log('데이터베이스 파일이 생성되었습니다.');
  } catch (e) {
    console.error('데이터베이스 파일을 생성하는 중 에러가 발생했습니다:', e.message);
  }
} else console.log('데이터베이스 파일이 이미 존재합니다.');

 

 

 

6-2. Table생성

이제 SQLite에서 사용할 테이블을 생성할 차례입니다.

만약, SQL문에 자신이 없다면 위에서 알아보았던, DB Browser를 활용하는 것도 좋은 방법입니다.

 

테이블이 없을 경우에만 생성하도록 아래와 같이,

"IF NOT EXISTS" 를 추가해 주었습니다.

 

const createTableSQL = "CREATE TABLE IF NOT EXISTS receipts (" +
	"id INTEGER, " +
	"name TEXT NOT NULL UNIQUE," +
	"store_link TEXT," +
	"PRIMARY KEY(id AUTOINCREMENT)" +
")";
db.run(createTableSQL);

 

 

4-2. Create

본격적으로 실행하기 전에, 

INSERT문을 준비해 놓겠습니다.

위에서 알아본 prepare함수를 사용해 주면 됩니다.

 

const insertSQL = "INSERT INTO receipts (name, store_link VALUES (?, ?)"
const insertStmt = db.prepare(insertSQL)

 

이제 아래와 같이 실행해 주기만 하면 됩니다.

 

insertStmt.run("이름", "http://링크.링크") 
db.close()

 

 

prepare함수를 사용하지 않고 바로 SQL문만 이용해서 실행하려면 다음과 같이 해주면 됩니다.

insert에 성공하면 lastID 프로퍼티를 통해 ID를 볼 수 있습니다.

 

db.run(insertSQL, function(err){
  if(err) throw(err)
  console.log(`추가된 row의 id ${this.lastID}`)
});
db.close(err => {
	if(err) console.log(err) 
});

 

4-3. Read

이번에도 SQL문을 미리 준비해 보겠습니다.

모든 필드값을 필요로 할 때도 있을 것이구요.

아래의 store_link처럼 하나의 필드값만 필요할 경우도 있을 것 입니다.

 

const selectSQL = "SELECT * FROM receipts WHERE name = ?"

const selectLinkSQL = "SELECT store_link FROM receipts WHERE name = ?"

 

이제 준비된 sql문을 아래와 같이 실행시켜 봅니다.

해당하는 하나의 아이템을 가져올 때는 db객체에 get()함수를 사용해 주면 됩니다.

 

db.get(selectSQL, "이름", (err, row) => { 
  if(err) throw err;
  if(rows === undefined) console.log("결과 없음");
  console.log(row);
}
db.close();

 

 

해당하는 여러개의 결과를 가져오고 싶다면,

아래와 같이 all()함수를 사용하면 됩니다.

 

db.all(selectSQL, "이름", (err, rows) => { 
  if(err) throw err;
  for(let row of rows){
    console.log(row)
  }
}
db.close();

 

4-4. Update

이번에는 Update를 해 보겠습니다.

가장 먼저 할 것은 SQL문을 준비하는 것이구요.

이 후 prepare함수를 실행해 줍니다.

백틱(`)을 사용해서 ${}안에 변수를 SQL문안에 넣어주어야 합니다.

물론 미리 넣어주지 않고, "?"로 처리한 다음, sql문 실행시에 넣어주어도 됩니다.

 

마지막줄에는 prepare함수를 실행해서 필요할 때 바로 실행할 수 있도록 하겠습니다.

 

const storeId = 8;
const updateSQL = `UPDATE receipts` +
  ` SET name = ?, store_link = ?` +
  ` WHERE id = ${storeId}`;
  
const updateStmt = db.prepare(updateSQL);

 

 

이제 이 sql문을 실행시켜보겠습니다.

 

updateStmt.run(
    "새이름",
    "새링크"
);

 

prepare()를 사용하지 하지 않고 바로 run()을 실행시킨다면 아래와 같습니다.

 

db.run(updateSQL, function(err){
  if(err)throw err;
  console.log(`updated: ${this.changes}`);
}

 

위에서도 보았지만, run()함수는 성공하게 되면, lastId와 changes라는 프로퍼티를 가지는데요.

위에서는 그것을 console에 로깅하고 있습니다.

 

 

4-5. DELETE

이번에는 Delete를 구현해 보겠습니다.

가장 먼저 할 것은 SQL문을 작성해 주는 것 인데요.

 

const deleteSQL = "DELETE FROM receipts WHERE id = ?";

const deleteStmt = db.prepare(deleteSQL);

 

 

이제 이것을 실행해 주기만 하면 됩니다.

 

deleteStmt.run(8);

 

prepare함수를 사용하지 않는다면 아래와 같이 해주면 되겟지요.

 

db.run(deleteSQL, 8, function(err){
  if(err) console.log(err);
  console.log(`deleted Row: ${this.changes}`);
});

 

5. SQLite 팁

5-1. 날짜와 시간의 표현

위의 SQLite데이터 타입을 보면,

date나 time에 관한 타입이 없습니다.

SQLite에서는 날짜 및 시간을 표현할 때 TEXT 형식을 사용하여 날짜 및 시간 정보를 문자열로 저장합니다.

이 문자열은 일반적으로 ISO 8601 형식이나 다른 표준 형식으로 표현됩니다.

예를 들어, YYYY-MM-DD HH:MM:SS 형식으로 날짜 및 시간을 저장할 수 있습니다. 

저장된 값은 텍스트 형식이지만, SQLite에서는 다양한 날짜 및 시간 함수를 사용하여 이러한 값들을 처리할 수 있습니다.

 

이와 관련해서 함수들도 제공하는데요.

다음과 같습니다.

 

  • date('now'): 현재 날짜
  • time('now'): 현재 시간
  • datetime('now'): 현재 날짜와 시간
  • strftime(format, timestring, modifier...): 특정형식으로 날짜 및 시간 문자열을 반환합니다.
    format에는 날짜 및 시간 형식을 지정하고,
    timestring에는 날짜 및 시간 값이 들어갑니다.
    modifier를 사용하여 추가적인 조작이 가능한데요. 아래에서 보겠습니다.

 

아래의 예제를 보도록 하겠습니다.

 

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

// 테이블 생성 SQL 문
const createTableSQL = `
  CREATE TABLE IF NOT EXISTS myTable (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    data_column TEXT,
    created_at TEXT,
    updated_at TEXT
  )
`;

// 테이블 생성
db.run(createTableSQL, (err) => {
  if (err) {
    console.error(err.message);
  } else {
    console.log('Table created or already exists');
  }
});

// 새로운 레코드 추가
const insertRecordSQL = `
  INSERT INTO myTable (data_column, created_at, updated_at)
  VALUES (?, datetime("now"), datetime("now"))
`;

const dataValue = 'Sample Data';
db.run(insertRecordSQL, [dataValue], (err) => {
  if (err) {
    console.error(err.message);
  } else {
    console.log('Row added to the table');
  }
});

// 기존 레코드 업데이트
const updateRecordSQL = `
  UPDATE myTable
  SET data_column = ?, updated_at = datetime("now")
  WHERE id = ?
`;

const newDataValue = 'Updated Data';
const recordIdToUpdate = 1; // 갱신할 레코드의 ID

db.run(updateRecordSQL, [newDataValue, recordIdToUpdate], (err) => {
  if (err) {
    console.error(err.message);
  } else {
    console.log('Row updated in the table');
  }
});

db.close();

 

이상으로 NodeJS에서 사용하는 SQLite에 대해 알아보고,

CRUD를 구현해 보았습니다.

728x90
저작자표시 비영리 변경금지

'Database > MySQL, SQL' 카테고리의 다른 글

데이터베이스 시간 기록: created_at 과 updated_at 자동 업데이트 하기  (0) 2024.04.13
Join 에 관해서 정리해 보겠습니다. #SQL  (0) 2020.05.22
MySql 의 기본 사용법과 주요 명령어들 총정리  (1) 2020.05.21
CSV 파일을 MYSQL로 Import  (1) 2017.01.05

관련글

댓글

티스토리툴바