[노드JS] SQLite를 사용한 CRUD

Create

File disk 기반 데이터베이스을 사용하여 학생의 정보를 저장하는 Student 테이블을 생성해 보겠습니다.

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

let db = new sqlite3.Database('./db/chinook.db');

db.run('CREATE TABLE student(id integer primary key, name text not null, email text unique)');

db.close();

sqlite3패키지와 chinook.db 파일이 없는 경우에는 [노드JS] SQLite 연결하기 참고

db.run()에는 sql문이 들어가게 됩니다. 저희가 생성하는 Student테이블은 id, name과 email 필드가 있습니다.

모든 필드는 column_name data_type가 꼭 들어가야 하며 primary key, not null, unique 등의 선택적 용어들이 그 이후에 들어가게 됩니다.

이종현의 이름을 가진 학생을 생성해서 Student 테이블에 값을 넣어봅시다.

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

let db = new sqlite3.Database('./db/chinook.db');

// insert one row into the student table
db.run(`INSERT INTO student(name, email) VALUES('이종현', '1428ksu@gmail.com')`, function (err) {
    if (err) {
        return console.log(err.message);
    }
    // get the last insert id
    console.log(`A row has been inserted with rowid ${this.lastID}`);
});

// close the database connection
db.close();

아래와 같은 결과가 터미널에 보이면 성공적으로 insert한 것입니다.

Read

sql쿼리를 사용해서 이전에 저장한 이종현이라는 이름을 가진 학생을 불러오는 것을 하나의 예제로 sqlite를 이용하여 Read를 해보겠습니다.

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

// open the database
let db = new sqlite3.Database('./db/chinook.db');

let sql = `SELECT * FROM student
           WHERE name = '이종현'`;

db.all(sql, [], (err, rows) => {
  if (err) {
    throw err;
  }
  rows.forEach((row) => {
    console.log(row);
  });
});

// close the database connection
db.close();

결과는 rows라는 object에 저장되고 에러가 있을 경우 if(err){.. 라는 if문에 의해서 처리가 됩니다.

db.all() 은 모든 결과를 불러오는 메서드 입니다. 한 결과만 가져오고 싶을 때는 db.get()을 쓰면 됩니다.

이전에 생성한 값이 json데이터로 잘 오는 것을 볼 수 있습니다.

Update

이종현 학생의 이메일이 수정이 필요하다고 가정해 보겠습니다. 그렇게 하려면 sql쿼리를 사용해서 이종현이라는 학생을 찾고 email을 update해야 됩니다.

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

// open the database
let db = new sqlite3.Database('./db/chinook.db');

let sql = `UPDATE student
            SET email = '1sw2o@naver.com'
            WHERE name = '이종현'`;

db.run(sql, function (err) {
    if (err) {
        return console.error(err.message);
    }
    console.log(`Row(s) updated: ${this.changes}`);

});

// close the database connection
db.close();

터미널에 이와 같은 메시지가 뜨면 update가 잘 된겁니다. 이 학생의 정보가 실제로 update가 잘 됬는지 Read에서 쓴 코드로 확인해보겠습니다. 이메일이 gmail에서 네이버로 잘 update되었습니다.

Delete

이종현 학생의 id를 가져오고 그 값이 어떤 변수에 저장되어 있다는 가정하에 이 학생의 정보를 삭제해 보겠습니다

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

// open a database connection
let db = new sqlite3.Database('./db/chinook.db', (err) => {
    if (err) {
        console.error(err.message);
    }
});

let id = 1;
// delete a row based on id
db.run(`DELETE FROM student WHERE id=?`, id, function (err) {
    if (err) {
        return console.error(err.message);
    }
    console.log(`Row(s) deleted ${this.changes}`);
});

// close the database connection
db.close((err) => {
    if (err) {
        return console.error(err.message);
    }
});

위에 코드는 id라는 변수를 sql문과 같이 사용하여 id가 1인 학생을 삭제하려고 하고 있습니다.

sql문에 ?를 넣는것으로 db.run() 에서의 두 번째 패러미터로 변수를 사용할 수가 있습니다.
두 개 이상의 변수를 사용해야 된다면 다수의 ?를 sql문에 넣고 db.run() 을 invoke했을 때 패러미터로 계속 추가하면 됩니다.
db.run(sql, var1, var2, ..... , function(err)) {....

터미널에 위와 같은 메시지가 뜨는 것으로 성공적으로 삭제 되었다는 것을 알 수 있습니다.