SQLite 3 데이터베이스 관리 및 활용

SQLite 3는 널리 사용되는 임베디드 관계형 데이터베이스 관리 시스템입니다. 서버 프로세스 없이 간편하게 사용할 수 있고, 모든 데이터가 단일 파일에 저장되는 특성 때문에 모바일 앱, 임베디드 시스템, 소규모 데스크톱 애플리케이션, 그리고 로컬 개발 환경에서 이상적인 선택지입니다. SQLite 3는 관리와 사용이 매우 쉽습니다. 본 가이드는 ServBay 사용자를 위해 ServBay 환경에서 SQLite 3 데이터베이스를 효율적으로 설치, 연결, 기본 조작, 데이터 백업과 복원, 성능 최적화, 보안 이슈까지 포괄적으로 다룹니다.

개요

ServBay는 올인원 로컬 웹 개발 환경으로, SQLite 3와 관련 개발 라이브러리가 기본 내장되어 있습니다. 명령줄, 또는 ServBay가 지원하는 다양한 프로그래밍 언어(PHP, Node.js, Python, Go 등)에서 별도 설치 없이 바로 접근하고 조작할 수 있습니다. 이를 통해 로컬 개발에서 SQLite 3 활용 절차가 매우 간소화됩니다.

SQLite 3 설치 및 설정

ServBay에는 SQLite 3가 이미 통합되어 있어 추가 설치 과정이 필요 없습니다. 실행 파일과 관련 라이브러리가 소프트웨어 패키지에 포함되어 있습니다.

SQLite 3 설치 확인

아래 명령어를 터미널에서 실행하여 ServBay에서 제공하는 SQLite 3 실행 파일에 접근 가능한지, 그리고 버전 정보를 확인할 수 있습니다:

bash

sqlite3 --version

명령 실행 결과로 SQLite 3 버전(예: 3.41.2 2023-03-26 11:52:19 ...)이 출력된다면, 정상적으로 통합되었음을 의미합니다.

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

SQLite 3 데이터베이스는 본질적으로 단일 파일입니다. ServBay는 기본적으로 데이터베이스 파일을 /Applications/ServBay/db/sqlite 디렉터리에 저장하는 것을 권장하지만, 프로젝트 요구사항에 따라 다른 경로도 무방합니다. 데이터베이스 생성 및 연결은 sqlite3 명령줄 도구나 각 언어의 SQLite 라이브러리로 손쉽게 할 수 있습니다.

명령줄 도구를 이용한 연결

sqlite3 명령줄 툴은 SQLite 3 데이터베이스를 관리하는 가장 직관적인 방법입니다.

데이터베이스 생성 또는 연결
아래 명령어를 터미널에 입력하면 해당 경로에 데이터베이스 파일이 없을 경우 새로 만들어지고, 이미 있다면 해당 데이터베이스에 연결됩니다.
bash
```
sqlite3 /Applications/ServBay/db/sqlite/servbay.db
```
1
(예제 데이터베이스명을 servbay.db로, ServBay 브랜드를 반영함)
sqlite> 프롬프트에 진입하면 SQL 명령 또는 .commands를 실행할 수 있습니다.
SQLite 3 종료
sqlite> 프롬프트에서 다음 명령을 입력하여 명령줄 도구를 종료할 수 있습니다.
sql
```
.exit
```
1

프로그래밍 언어를 이용한 연결

ServBay가 지원하는 언어들은 모두 SQLite 3 데이터베이스 연결 및 조작용 라이브러리를 갖추고 있습니다. 아래는 PHP와 Node.js의 예제입니다.

PHP 예제

ServBay는 기본적으로 PHP의 SQLite 3 확장(php-sqlite3)이 내장되어 있으며, 내장 SQLite3 클래스를 활용해 데이터베이스에 연결할 수 있습니다.

php

<?php
// 데이터베이스 파일 경로 지정 (ServBay의 db/sqlite 디렉터리 사용 권장)
$database_file = '/Applications/ServBay/db/sqlite/servbay.db';

// SQLite 3 데이터베이스에 연결
// 파일이 없다면 SQLite3 생성자가 자동으로 생성함
$db = new SQLite3($database_file);

if (!$db) {
    die("SQLite 3 데이터베이스에 연결할 수 없습니다: " . $db->lastErrorMsg());
}

echo "SQLite 3 데이터베이스 연결 성공: " . $database_file . "\n";

// 테이블 생성 (존재하지 않을 경우)
$create_table_sql = "CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE
)";
$db->exec($create_table_sql);
echo "'users' 테이블 확인 또는 생성 완료.\n";

// 데이터 삽입
$name = 'ServBay Demo User';
$email = '[email protected]';
$insert_sql = $db->prepare('INSERT INTO users (name, email) VALUES (:name, :email)');
$insert_sql->bindValue(':name', $name, SQLITE3_TEXT);
$insert_sql->bindValue(':email', $email, SQLITE3_TEXT);

if ($insert_sql->execute()) {
    echo "데이터 삽입 성공.\n";
} else {
    echo "데이터 삽입 실패: " . $db->lastErrorMsg() . "\n";
}


// 데이터 조회
$query_sql = "SELECT id, name, email FROM users";
$result = $db->query($query_sql);

if ($result) {
    echo "조회 결과:\n";
    while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
        echo "ID: " . $row['id'] . ", Name: " . $row['name'] . ", Email: " . $row['email'] . "\n";
    }
} else {
     echo "조회 실패: " . $db->lastErrorMsg() . "\n";
}


// 데이터베이스 연결 닫기
$db->close();
unset($db); // 리소스 해제
echo "데이터베이스 연결이 종료되었습니다.\n";

?>

Node.js 예제

Node.js에서 SQLite 3를 사용하려면 sqlite3 npm 패키지를 설치해야 합니다. 터미널을 열고 프로젝트 디렉터리에서 다음을 실행하세요:

bash

npm install sqlite3

이후 Node.js 코드 내에서 이를 활용해 데이터베이스를 조작할 수 있습니다.

javascript

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

// 데이터베이스 파일 경로 지정, path.join 사용으로 플랫폼 호환성 보장
const dbPath = path.join('/Applications/ServBay/db/sqlite', 'servbay.db');

// SQLite 3 데이터베이스에 연결
// 파일이 없으면 sqlite3.Database가 자동으로 생성
let db = new sqlite3.Database(dbPath, sqlite3.OPEN_READWRITE | sqlite3.OPEN_CREATE, (err) => {
  if (err) {
    console.error('SQLite 3 데이터베이스에 연결할 수 없습니다:', err.message);
  } else {
    console.log('SQLite 데이터베이스 연결 성공.');
  }
});

// 명령 순차 실행 보장을 위해 serialize 사용
db.serialize(() => {
  // 테이블 생성 (존재하지 않을 경우)
  db.run(`CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE
  )`, (err) => {
    if (err) {
      console.error('테이블 생성 실패:', err.message);
    } else {
      console.log('"users" 테이블 확인 또는 생성 완료.');
    }
  });

  // 데이터 삽입
  const name = 'ServBay Demo User';
  const email = '[email protected]';
  db.run(`INSERT INTO users (name, email) VALUES (?, ?)`, [name, email], function(err) {
    if (err) {
      // SQLITE_CONSTRAINT는 유니크 제약조건 에러코드
      if (err.errno === 19) { // SQLITE_CONSTRAINT
         console.warn(`사용자 '${name}' (${email})는 이미 존재하여 삽입을 건너뜁니다.`);
      } else {
         console.error('데이터 삽입 실패:', err.message);
      }
    } else {
      console.log(`데이터 1건 삽입 성공, ID: ${this.lastID}`);
    }
  });

  // 데이터 조회
  db.all(`SELECT id, name, email FROM users`, [], (err, rows) => {
    if (err) {
      throw err;
    }
    console.log('조회 결과:');
    rows.forEach((row) => {
      console.log(`ID: ${row.id}, Name: ${row.name}, Email: ${row.email}`);
    });
  });
});

// 데이터베이스 연결 닫기
db.close((err) => {
  if (err) {
    console.error('데이터베이스 연결 종료 실패:', err.message);
  } else {
    console.log('데이터베이스 연결이 종료되었습니다.');
  }
});

참고: 실무 환경에서는 데이터베이스 경로 보안 및 오류 및 동시 접근 문제를 반드시 적절하게 처리해야 합니다.

데이터베이스 관리

기본 SQL 문법

SQLite 3에 연결되면, 표준 SQL 명령어를 실행해 데이터를 관리할 수 있습니다. 대표 예시는 다음과 같습니다.

테이블 생성: 데이터 구조 정의

sql

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY AUTOINCREMENT,
    product_name TEXT NOT NULL,
    price REAL DEFAULT 0.00
);

데이터 삽입: 새 레코드 추가

sql

INSERT INTO products (product_name, price) VALUES ('ServBay T-Shirt', 19.99);
INSERT INTO products (product_name, price) VALUES ('ServBay Sticker Pack', 4.99);

데이터 조회: 레코드 검색

sql

SELECT * FROM products;
SELECT product_name, price FROM products WHERE price > 10.00;

데이터 수정: 기존 레코드 변경

sql

UPDATE products SET price = 24.99 WHERE product_name = 'ServBay T-Shirt';

데이터 삭제: 레코드 제거

sql

DELETE FROM products WHERE product_id = 1;
DELETE FROM products; -- 테이블 내 전체 데이터 삭제

테이블 삭제: 테이블 구조 및 데이터 통째로 제거
sql
```
DROP TABLE products;
```
1

이 명령어들은 sqlite3 명령줄 툴 또는 언어에서 제공하는 SQLite 라이브러리로 모두 실행할 수 있습니다.

백업과 복원

SQLite 3는 전체 데이터베이스가 하나의 파일이기 때문에 백업 및 복원이 매우 간단합니다.

데이터베이스 백업

가장 간단한 방법은 데이터베이스 파일을 복사하는 것입니다. ServBay는 백업 파일을 /Applications/ServBay/backup/sqlite 폴더에 모아 관리할 것을 권장합니다.

명령줄을 통해 데이터베이스 파일을 복사해 백업할 수 있습니다:

bash

# 백업 디렉터리 생성 (존재하지 않을 경우)
mkdir -p /Applications/ServBay/backup/sqlite

# 데이터베이스 파일 백업
# 파일명에 날짜/시간 정보를 포함해 각 버전 관리
cp /Applications/ServBay/db/sqlite/servbay.db /Applications/ServBay/backup/sqlite/servbay_$(date +%Y%m%d_%H%M%S).db

sqlite3 명령줄 툴의 .backup 명령으로 열려있는 상태에서 백업하는 방법도 있지만, 로컬 개발 환경에선 단순 파일 복사로도 충분합니다.

데이터베이스 복원

백업 파일만 원래 위치에 다시 복사하면 복원 완료입니다. 복원 전에는 반드시 해당 데이터베이스를 사용하는 앱이나 ServBay 서비스를 모두 중지해 파일 잠금(lock) 혹은 데이터 불일치를 방지해야 합니다.

bash

# 예시: 최신 백업 파일로 복원
# 최신 파일 찾기 예시: servbay_20231027_103000.db
LATEST_BACKUP=$(ls -t /Applications/ServBay/backup/sqlite/servbay_*.db | head -n 1)

# 백업 파일 존재 여부 확인
if [ -z "$LATEST_BACKUP" ]; then
  echo "오류: SQLite 백업 파일을 찾을 수 없습니다."
  exit 1
fi

echo "복원할 백업 파일: $LATEST_BACKUP"

# 관련 서비스/앱 중지... (ServBay 설정 및 사용 방식에 따라 다름)
# 예: PHP 앱에서 사용 중일 때 PHP 프로세스가 동작 중이면 안 됨

# 백업 파일로 데이터베이스 파일 덮어쓰기
cp "$LATEST_BACKUP" /Applications/ServBay/db/sqlite/servbay.db

echo "데이터베이스 복원이 완료되었습니다."

# 관련 서비스/앱 재시작...

중요: 단순 파일 복사는 데이터베이스에 활발한 쓰기가 이뤄질 때 데이터가 손상될 수 있습니다. 반드시 데이터를 쓰지 않는 정지 상태에서 복원하거나, 더 고급의 핫백업 API를 사용하는 것이 안전합니다.

성능 최적화

SQLite 3는 PRAGMA 명령과 인덱스를 통해 성능을 효율적으로 높일 수 있습니다.

인덱스 최적화

자주 조회 필터로 사용하거나(WHERE), 조인/정렬(JOIN, ORDER BY)에 쓰이는 컬럼에 인덱스를 추가하면 쿼리 속도가 크게 향상됩니다.

sql

-- users 테이블의 email 컬럼에 유니크 인덱스 생성
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- products 테이블의 product_name 컬럼에 일반 인덱스 생성
CREATE INDEX idx_products_name ON products(product_name);

쿼리 최적화

EXPLAIN QUERY PLAN 명령으로 SQLite가 쿼리를 어떻게 실행하는지 확인하여 병목 구간을 찾을 수 있습니다.

sql

EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = '[email protected]';
EXPLAIN QUERY PLAN SELECT product_name FROM products ORDER BY price DESC;

쿼리 계획을 분석해 인덱스 사용 여부, 풀스캔 여부(SCAN TABLE) 등을 점검하세요.

설정 최적화 (PRAGMA)

PRAGMA 명령은 SQLite 3의 런타임 동작을 제어합니다. 성능 관련 주요 PRAGMA는 다음과 같습니다:

PRAGMA journal_mode; / PRAGMA journal_mode = mode;: 로그 모드 설정. WAL(Write-Ahead Logging) 모드는 여러 읽기 작업이나 동시 쓰기에서 성능이 좋습니다. 기본값은 DELETE 또는 TRUNCATE일 수 있으나, WAL로 설정 시 락 충돌이 줄어듭니다.
sql
```
PRAGMA journal_mode = WAL;
```
1
PRAGMA synchronous; / PRAGMA synchronous = level;: 파일 시스템 동기화 강도 제어. OFF(0)은 쓰기 성능을 극대화하지만 최근 트랜잭션 데이터 손실 위험이 있습니다. FULL(1)이나 NORMAL(2)은 데이터 보안은 높지만 속도는 느립니다. 로컬 개발 목적이라면 OFF 설정이 실속 있습니다.
sql
```
PRAGMA synchronous = OFF;
```
1
PRAGMA cache_size; / PRAGMA cache_size = pages;: 메모리 캐시 페이지 수 조정. 캐시 크기를 늘리면 디스크 I/O를 줄여 성능이 좋아지나, 메모리 점유도 함께 높아집니다.
sql
```
PRAGMA cache_size = -20000; -- 20MB 캐시 (음수면 KB 단위)
```
1

참고: PRAGMA 설정은 연결마다 적용되므로, 앱 시작 시 매번 설정해주는 것이 일반적입니다.

보안 관리

SQLite 3도 로컬 파일 DB이지만, 기본적인 보안 수칙은 지키는 것이 중요합니다.

파일 권한

운영체제에서 SQLite 데이터베이스 파일의 권한을 적절히 설정해 ServBay를 실행하는 사용자만 읽기/쓰기를 할 수 있도록 하세요.

bash

# 예시: 파일 소유자를 현재 사용자로, 오직 소유자만 읽기/쓰기 허용
# 실제 ServBay 실행 계정에 맞게 조정 필요
chmod 600 /Applications/ServBay/db/sqlite/servbay.db

데이터 암호화

SQLite 3 자체는 내장 암호화를 제공하지 않습니다. 로컬 환경에서도 민감한 데이터가 있다면, SQLCipher와 같은 암호화 지원 SQLite 확장을 사용해야 하며, 관련 API 및 라이브러리 추가 설치가 필요합니다.

자주 묻는 질문 및 문제해결

SQLite 3 데이터베이스 연결 불가

데이터베이스 파일 경로 확인
연결 문자열이나 명령줄에서 지정한 경로가 올바른지, 파일이 존재하는지 점검하세요.
파일 권한 확인
ServBay 실행 사용자나 자신의 터미널 계정에 읽기/쓰기 권한이 있는지 확인합니다. 권한은 ls -l /Applications/ServBay/db/sqlite/servbay.db로, 필요시 chmod 또는 chown으로 조정합니다.
데이터베이스 파일 손상 여부
sqlite3 명령줄로 접근을 시도했을 때 에러가 발생하면 파일이 손상됐을 수 있습니다. 이런 경우 백업에서 복구를 시도하세요.

데이터베이스 잠김 현상("Database is locked")

SQLite 3는 쓰기 작업 시 전체 데이터베이스 파일을 잠급니다. 쓰기 중 다른 프로세스에서 쓰기를 시도하거나(혹은 로그 모드에 따라 일부 읽기조차)하면 잠금 에러가 발생할 수 있습니다.

동시 접근 환경 점검
동시에 DB를 쓰는 앱·스크립트·프로세스가 없는지 확인하세요. 로컬 개발 환경에서는 여러 개발 서버나 스크립트가 동시에 접근 시 흔히 발생합니다.
WAL 모드 사용
PRAGMA journal_mode = WAL; 설정을 적용하면 동시 읽기/쓰기 상황에서 성능과 안정성이 개선됩니다. DB 연결 후 즉시 설정하는 것이 좋습니다.
잠금 에러 처리
코딩 시에는 잠금 에러 발생 시 바로 실패하지 말고, 짧은 딜레이 후 재시도를 구현하는 것이 권장됩니다. 대부분의 SQLite 라이브러리는 busy timeout(대기 시간) 옵션을 제공합니다.
예를 들어 Node.js의 sqlite3는 다음과 같이 설정 가능합니다:
javascript
```
db.configure("busyTimeout", 5000); // 5000ms(5초) 대기
```
1
PHP SQLite3 클래스는 직접 busyTimeout 메서드는 없으니, 애플리케이션 코드에서 재시도 루프를 구현하거나, 보다 저수준의 sqlite_busy_timeout 함수를 사용할 수 있다면 활용하세요.

요약

ServBay는 개발자에게 최적화된 로컬 웹 개발 환경으로, 강력하면서 사용이 편리한 SQLite 3 데이터베이스를 내장하고 있습니다. 본 문서를 통해 ServBay 환경에서 SQLite 3의 설치 확인, 연결, 데이터 관리, 효율적인 백업/복원, 성능 튜닝, 보안까지 한 눈에 익힐 수 있습니다. SQLite 3는 경량성과 편리함 덕분에 로컬 개발·테스트, 소규모 프로젝트에 매우 탁월한 데이터베이스입니다. 더불어, ServBay의 PHP, Node.js, Python, Go 등 다양한 도구와 연계해 복잡한 앱 및 웹 서비스를 손쉽게 빌드하고 테스트할 수 있습니다.

SQLite 3 데이터베이스 관리 및 활용 ​

개요 ​

SQLite 3 설치 및 설정 ​

SQLite 3 설치 확인 ​

SQLite 3 데이터베이스 생성 및 연결 ​

명령줄 도구를 이용한 연결 ​

프로그래밍 언어를 이용한 연결 ​

PHP 예제 ​

Node.js 예제 ​

데이터베이스 관리 ​

기본 SQL 문법 ​

백업과 복원 ​

데이터베이스 백업 ​

데이터베이스 복원 ​

성능 최적화 ​

인덱스 최적화 ​

쿼리 최적화 ​

설정 최적화 (PRAGMA) ​

보안 관리 ​

파일 권한 ​

데이터 암호화 ​

자주 묻는 질문 및 문제해결 ​

SQLite 3 데이터베이스 연결 불가 ​

데이터베이스 잠김 현상("Database is locked") ​

요약 ​

SQLite 3 데이터베이스 관리 및 활용

개요

SQLite 3 설치 및 설정

SQLite 3 설치 확인

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

명령줄 도구를 이용한 연결

프로그래밍 언어를 이용한 연결

PHP 예제

Node.js 예제

데이터베이스 관리

기본 SQL 문법

백업과 복원

데이터베이스 백업

데이터베이스 복원

성능 최적화

인덱스 최적화

쿼리 최적화

설정 최적화 (PRAGMA)

보안 관리

파일 권한

데이터 암호화

자주 묻는 질문 및 문제해결

SQLite 3 데이터베이스 연결 불가

데이터베이스 잠김 현상("Database is locked")

요약