Knex.js 치트 시트

Node.js용 SQL 쿼리 빌더 – 쿼리, 스키마, 마이그레이션 및 트랜잭션.

설치 및 설정

Knex.js를 설치하고 데이터베이스 연결을 구성합니다

# knex와 데이터베이스 드라이버를 설치합니다
npm install knex --save

# 데이터베이스 드라이버 (하나 선택)
npm install pg          # PostgreSQL
npm install mysql2      # MySQL
npm install sqlite3     # SQLite
npm install tedious     # MSSQL

패키지 설치

const knex = require('knex')({
  client: 'mysql2',
  connection: {
    host: '127.0.0.1',
    port: 3306,
    user: 'your_database_user',
    password: 'your_database_password',
    database: 'myapp_test'
  },
  pool: { min: 0, max: 7 }
});

기본 설정

// 연결 문자열
const knex = require('knex')({
  client: 'pg',
  connection: process.env.DATABASE_URL,
  searchPath: ['knex', 'public']
});

// SQLite 메모리
const knex = require('knex')({
  client: 'sqlite3',
  connection: { filename: ':memory:' },
  useNullAsDefault: true
});

대체 연결 방법

기본 쿼리

조회, 삽입, 업데이트 및 삭제 작업

// 전체 조회
knex('users').select('*')
// SELECT * FROM users

// 특정 컬럼 조회
knex('users').select('id', 'name', 'email')
// SELECT id, name, email FROM users

// 별칭을 사용한 조회
knex('users').select('id', 'name as full_name')
// SELECT id, name as full_name FROM users

// 첫 번째 결과
knex('users').first('*')
// SELECT * FROM users LIMIT 1

SELECT 쿼리

// 단일 행 삽입
knex('users').insert({ name: 'John', email: 'john@example.com' })

// 다중 행 삽입
knex('users').insert([
  { name: 'John', email: 'john@example.com' },
  { name: 'Jane', email: 'jane@example.com' }
])

// 반환값과 함께 삽입 (PostgreSQL)
knex('users')
  .insert({ name: 'John', email: 'john@example.com' })
  .returning('id')

INSERT 쿼리

// 모든 행 업데이트
knex('users').update({ status: 'active' })

// 특정 행 업데이트
knex('users')
  .where('id', 1)
  .update({ name: 'John Updated' })

// 반환값과 함께 업데이트
knex('users')
  .where('id', 1)
  .update({ name: 'John' })
  .returning('*')

UPDATE 쿼리

// 모든 행 삭제
knex('users').del()

// 특정 행 삭제
knex('users').where('id', 1).del()

// 반환값과 함께 삭제
knex('users')
  .where('id', 1)
  .del()
  .returning('*')

DELETE 쿼리

WHERE 절

다양한 조건으로 데이터 필터링

// 간단한 where
knex('users').where('id', 1)
knex('users').where({ id: 1, status: 'active' })

// 연산자를 사용한 where
knex('users').where('age', '>', 18)
knex('users').where('created_at', '<=', '2024-01-01')

// 다중 조건 (AND)
knex('users')
  .where('status', 'active')
  .where('age', '>', 18)

// OR 조건
knex('users')
  .where('status', 'active')
  .orWhere('role', 'admin')

기본 WHERE

// IN 조건
knex('users').whereIn('id', [1, 2, 3])

// NOT IN 조건
knex('users').whereNotIn('status', ['banned', 'deleted'])

// NULL 조건
knex('users').whereNull('deleted_at')

// NOT NULL 조건
knex('users').whereNotNull('email')

// BETWEEN 조건
knex('users').whereBetween('age', [18, 65])

// NOT BETWEEN 조건
knex('users').whereNotBetween('age', [0, 18])

고급 WHERE

// 복잡한 중첩 조건
knex('users')
  .where('status', 'active')
  .where(function() {
    this.where('role', 'admin')
      .orWhere('role', 'moderator')
  })
// WHERE status = 'active' AND (role = 'admin' OR role = 'moderator')

// EXISTS 조건
knex('users')
  .whereExists(function() {
    this.select('*')
      .from('orders')
      .whereRaw('orders.user_id = users.id')
  })

// Raw WHERE
knex('users').whereRaw('age > ?', [18])

복잡한 WHERE

조인

여러 테이블의 데이터를 결합

// 내부 조인
knex('users')
  .join('orders', 'users.id', '=', 'orders.user_id')
  .select('users.name', 'orders.total')

// 왼쪽 조인
knex('users')
  .leftJoin('orders', 'users.id', 'orders.user_id')
  .select('users.name', 'orders.total')

// 오른쪽 조인
knex('users')
  .rightJoin('orders', 'users.id', 'orders.user_id')

// 전체 외부 조인
knex('users')
  .fullOuterJoin('orders', 'users.id', 'orders.user_id')

기본 조인

// 다중 조건 조인
knex('users')
  .join('orders', function() {
    this.on('users.id', '=', 'orders.user_id')
      .andOn('orders.status', '=', knex.raw('?', ['completed']))
  })

// OR 조건 조인
knex('users')
  .join('orders', function() {
    this.on('users.id', '=', 'orders.user_id')
      .orOn('users.email', '=', 'orders.email')
  })

// 교차 조인
knex('users').crossJoin('roles')

고급 조인

집계 및 그룹화

집계 함수와 GROUP BY 연산

// 카운트
knex('users').count('id as total')

// 고유 카운트
knex('users').countDistinct('email')

// 합계
knex('orders').sum('total as revenue')

// 평균
knex('orders').avg('total as average_order')

// 최소 및 최대
knex('orders').min('total as min_order')
knex('orders').max('total as max_order')

집계 함수

// 그룹화
knex('orders')
  .select('user_id')
  .count('id as order_count')
  .groupBy('user_id')

// 다중 컬럼 그룹화
knex('orders')
  .select('user_id', 'status')
  .sum('total as revenue')
  .groupBy('user_id', 'status')

// HAVING 절
knex('orders')
  .select('user_id')
  .count('id as order_count')
  .groupBy('user_id')
  .having('order_count', '>', 5)

// Raw HAVING
knex('orders')
  .select('user_id')
  .sum('total as revenue')
  .groupBy('user_id')
  .havingRaw('SUM(total) > ?', [1000])

GROUP BY 및 HAVING

정렬 및 제한

쿼리 결과를 정렬하고 페이지네이션

// 오름차순 정렬
knex('users').orderBy('name')
knex('users').orderBy('name', 'asc')

// 내림차순 정렬
knex('users').orderBy('created_at', 'desc')

// 다중 정렬 컬럼
knex('users')
  .orderBy('status', 'asc')
  .orderBy('created_at', 'desc')

// 배열을 이용한 정렬
knex('users').orderBy([
  { column: 'status', order: 'asc' },
  { column: 'name', order: 'desc' }
])

// Raw 정렬
knex('users').orderByRaw('age DESC NULLS LAST')

정렬

// 제한
knex('users').limit(10)

// 오프셋
knex('users').offset(20)

// 페이지네이션
knex('users')
  .limit(10)
  .offset(20)

// 고유값
knex('users').distinct('email')

// 다중 컬럼 고유값
knex('users').distinct('email', 'status')

제한 및 페이지네이션

스키마 - 테이블 생성

데이터베이스 테이블 정의 및 생성

// 테이블 생성
knex.schema.createTable('users', function(table) {
  table.increments('id').primary();
  table.string('name', 100).notNullable();
  table.string('email', 255).unique().notNullable();
  table.integer('age');
  table.boolean('is_active').defaultTo(true);
  table.timestamps(true, true);
});

// 존재하지 않을 경우 테이블 생성
knex.schema.createTableIfNotExists('users', function(table) {
  table.increments('id');
  table.string('email').unique();
});

기본 테이블 생성

// 일반 컬럼 타입
table.increments('id')              // 자동 증가 ID
table.integer('votes')              // 정수
table.bigInteger('big_votes')       // 큰 정수
table.text('description')           // 텍스트
table.string('name', 255)           // 길이 제한 문자열
table.float('amount')               // 부동소수점
table.decimal('price', 8, 2)        // Decimal(8,2)
table.boolean('is_admin')           // 불리언
table.date('birth_date')            // 날짜
table.datetime('created_at')        // 날짜시간
table.time('alarm_time')            // 시간
table.timestamp('updated_at')       // 타임스탬프
table.json('metadata')              // JSON
table.jsonb('data')                 // JSONB (PostgreSQL)
table.uuid('id')                    // UUID
table.enum('status', ['active', 'inactive'])  // 열거형

컬럼 타입

// 컬럼 수정자
table.string('email')
  .notNullable()           // NULL 허용 안 함
  .unique()                // 고유
  .defaultTo('default')    // 기본값
  .unsigned()              // 부호 없음 (숫자용)
  .index()                 // 인덱스 추가
  .comment('Email address') // 주석 추가

// 외래 키
table.integer('user_id')
  .unsigned()
  .notNullable()
  .references('id')
  .inTable('users')
  .onDelete('CASCADE')
  .onUpdate('CASCADE');

// 타임스탬프 헬퍼
table.timestamps(true, true)  // created_at, updated_at
table.timestamp('created_at').defaultTo(knex.fn.now())

컬럼 수정자 및 제약조건

스키마 - 테이블 수정

기존 테이블 구조 수정

// 컬럼 추가
knex.schema.table('users', function(table) {
  table.string('phone', 20);
});

// 여러 컬럼 추가
knex.schema.table('users', function(table) {
  table.string('phone', 20);
  table.string('address', 255);
  table.integer('zip_code');
});

// 컬럼 수정
knex.schema.alterTable('users', function(table) {
  table.string('email', 500).alter();
});

// 컬럼 이름 변경
knex.schema.table('users', function(table) {
  table.renameColumn('name', 'full_name');
});

테이블 수정

// 컬럼 삭제
knex.schema.table('users', function(table) {
  table.dropColumn('phone');
});

// 여러 컬럼 삭제
knex.schema.table('users', function(table) {
  table.dropColumns('phone', 'address');
});

// 테이블 삭제
knex.schema.dropTable('users');

// 존재하면 테이블 삭제
knex.schema.dropTableIfExists('users');

// 테이블 이름 변경
knex.schema.renameTable('users', 'customers');

삭제 및 이름 변경

// 테이블 존재 여부 확인
knex.schema.hasTable('users')
  .then(exists => {
    if (!exists) {
      return knex.schema.createTable('users', table => {
        table.increments('id');
      });
    }
  });

// 컬럼 존재 여부 확인
knex.schema.hasColumn('users', 'email')
  .then(exists => {
    if (!exists) {
      return knex.schema.table('users', table => {
        table.string('email');
      });
    }
  });

스키마 확인

스키마 - 인덱스 및 외래 키

데이터베이스 인덱스와 관계를 생성 및 관리

// 인덱스 생성
knex.schema.table('users', function(table) {
  table.index('email');
});

// 이름이 있는 인덱스 생성
knex.schema.table('users', function(table) {
  table.index('email', 'idx_users_email');
});

// 복합 인덱스
knex.schema.table('users', function(table) {
  table.index(['status', 'created_at'], 'idx_status_created');
});

// 고유 인덱스
knex.schema.table('users', function(table) {
  table.unique('email');
});

// 인덱스 삭제
knex.schema.table('users', function(table) {
  table.dropIndex('email');
  table.dropIndex([], 'idx_users_email'); // 이름으로
});

인덱스

// 외래 키 추가
knex.schema.table('orders', function(table) {
  table.foreign('user_id')
    .references('id')
    .inTable('users');
});

// 동작이 있는 외래 키
knex.schema.table('orders', function(table) {
  table.foreign('user_id')
    .references('id')
    .inTable('users')
    .onDelete('CASCADE')
    .onUpdate('RESTRICT');
});

// 이름이 있는 외래 키
knex.schema.table('orders', function(table) {
  table.foreign('user_id', 'fk_orders_user')
    .references('users.id');
});

// 외래 키 삭제
knex.schema.table('orders', function(table) {
  table.dropForeign('user_id');
  table.dropForeign([], 'fk_orders_user'); // 이름으로
});

외래 키

트랜잭션

여러 쿼리를 원자적으로 실행

// 기본 트랜잭션
knex.transaction(async (trx) => {
  await trx('users').insert({ name: 'John' });
  await trx('orders').insert({ user_id: 1, total: 100 });
})
.then(() => console.log('트랜잭션 커밋됨'))
.catch((err) => console.error('트랜잭션 실패:', err));

// 반환값이 있는 트랜잭션
const userId = await knex.transaction(async (trx) => {
  const [id] = await trx('users')
    .insert({ name: 'John' })
    .returning('id');
  
  await trx('profiles')
    .insert({ user_id: id, bio: '안녕하세요' });
  
  return id;
});

기본 트랜잭션

// 수동 트랜잭션 제어
const trx = await knex.transaction();

try {
  await trx('users').insert({ name: 'John' });
  await trx('orders').insert({ user_id: 1, total: 100 });
  await trx.commit();
} catch (err) {
  await trx.rollback();
  throw err;
}

// 저장점이 있는 트랜잭션
knex.transaction(async (trx) => {
  await trx('users').insert({ name: 'John' });
  
  const savepoint = await trx.savepoint();
  try {
    await trx('orders').insert({ user_id: 1, total: 100 });
    await savepoint.release();
  } catch (err) {
    await savepoint.rollback();
  }
});

고급 트랜잭션 제어

Raw 쿼리

필요할 때 Raw SQL 실행

// 간단한 Raw 쿼리
knex.raw('SELECT * FROM users WHERE id = ?', [1])

// 명명된 바인딩을 사용하는 Raw 쿼리
knex.raw('SELECT * FROM users WHERE id = :userId', {
  userId: 1
})

// select에 Raw 사용
knex('users')
  .select(knex.raw('COUNT(*) as total'))
  .where('status', 'active')

// where에 Raw 사용
knex('users')
  .whereRaw('age > ?', [18])
  .orWhereRaw('status = ?', ['admin'])

기본 Raw 쿼리

// Raw 업데이트
knex('users')
  .update({
    updated_at: knex.raw('NOW()'),
    views: knex.raw('views + 1')
  })
  .where('id', 1)

// Raw 삽입
knex('users').insert({
  name: 'John',
  created_at: knex.raw('CURRENT_TIMESTAMP')
})

// 컬럼 참조에 knex.ref 사용
knex('users')
  .where('created_at', '>', knex.ref('updated_at'))

쿼리 내 Raw 사용

마이그레이션

데이터베이스 스키마 버전 관리

// 마이그레이션 생성
npx knex migrate:make create_users_table

// 마이그레이션 실행
npx knex migrate:latest

// 마지막 배치 롤백
npx knex migrate:rollback

// 모든 마이그레이션 롤백
npx knex migrate:rollback --all

// 마이그레이션 상태 확인
npx knex migrate:status

// 특정 마이그레이션 실행
npx knex migrate:up 001_create_users.js

마이그레이션 명령

// 마이그레이션 파일 구조
exports.up = function(knex) {
  return knex.schema.createTable('users', function(table) {
    table.increments('id').primary();
    table.string('name', 100).notNullable();
    table.string('email', 255).unique().notNullable();
    table.timestamp('created_at').defaultTo(knex.fn.now());
  });
};

exports.down = function(knex) {
  return knex.schema.dropTable('users');
};

마이그레이션 파일 예시

// 여러 작업이 포함된 마이그레이션
exports.up = async function(knex) {
  // users 테이블 생성
  await knex.schema.createTable('users', table => {
    table.increments('id');
    table.string('email').unique();
  });
  
  // orders 테이블 생성
  await knex.schema.createTable('orders', table => {
    table.increments('id');
    table.integer('user_id').unsigned();
    table.foreign('user_id').references('users.id');
  });
  
  // 초기 데이터 시드
  await knex('users').insert([
    { email: 'admin@example.com' }
  ]);
};

exports.down = async function(knex) {
  await knex.schema.dropTable('orders');
  await knex.schema.dropTable('users');
};

복잡한 마이그레이션

시드

테스트 데이터를 사용해 데이터베이스 채우기

// 시드 파일 생성
npx knex seed:make 01_users

// 모든 시드 실행
npx knex seed:run

// 특정 시드 실행
npx knex seed:run --specific=01_users.js

시드 명령

// 기본 시드 파일
exports.seed = async function(knex) {
  // 기존 레코드 삭제
  await knex('users').del();
  
  // 시드 데이터 삽입
  await knex('users').insert([
    { name: 'John Doe', email: 'john@example.com' },
    { name: 'Jane Smith', email: 'jane@example.com' },
    { name: 'Bob Johnson', email: 'bob@example.com' }
  ]);
};

기본 시드

// 관계가 있는 시드
exports.seed = async function(knex) {
  // 올바른 순서로 테이블 정리
  await knex('orders').del();
  await knex('users').del();
  
  // 사용자 삽입 및 ID 가져오기
  const [userId1] = await knex('users')
    .insert({ name: 'John', email: 'john@example.com' })
    .returning('id');
  
  const [userId2] = await knex('users')
    .insert({ name: 'Jane', email: 'jane@example.com' })
    .returning('id');
  
  // 관련 주문 삽입
  await knex('orders').insert([
    { user_id: userId1, total: 100.50 },
    { user_id: userId1, total: 75.25 },
    { user_id: userId2, total: 200.00 }
  ]);
};

관계가 있는 시드

TypeScript 지원

TypeScript를 이용한 타입 안전 쿼리

// 테이블 타입 정의
interface User {
  id: number;
  name: string;
  email: string;
  created_at: string;
  updated_at: string;
}

interface UserInsert {
  name: string;
  email: string;
}

interface UserUpdate {
  name?: string;
  email?: string;
  updated_at?: string;
}

타입 정의

// Knex 타입 확장
import { Knex } from 'knex';

declare module 'knex/types/tables' {
  interface Tables {
    users: User;
    users_composite: Knex.CompositeTableType<
      User,
      UserInsert,
      UserUpdate
    >;
  }
}

// 타입 안전 쿼리
const users = await knex('users').select('*');
// users is typed as User[]

const newUser = await knex('users_composite')
  .insert({ name: 'John', email: 'john@example.com' })
  .returning('*');

타입 확장

// 일반적인 쿼리 타입
const getUserById = async (id: number): Promise<User | undefined> => {
  return knex<User>('users')
    .where({ id })
    .first();
};

const createUser = async (data: UserInsert): Promise<User> => {
  const [user] = await knex<User>('users')
    .insert(data)
    .returning('*');
  return user;
};

const updateUser = async (
  id: number,
  data: UserUpdate
): Promise<User | undefined> => {
  const [user] = await knex<User>('users')
    .where({ id })
    .update(data)
    .returning('*');
  return user;
};

타입이 지정된 쿼리 함수

고급 기능

고급 Knex.js 기능

// Upsert(삽입 또는 업데이트)
knex('users')
  .insert({ id: 1, name: 'John', email: 'john@example.com' })
  .onConflict('id')
  .merge()

// 특정 컬럼을 사용한 Upsert
knex('users')
  .insert({ id: 1, name: 'John', email: 'john@example.com' })
  .onConflict('email')
  .merge(['name'])

// Insert ignore (MySQL)
knex('users')
  .insert({ email: 'john@example.com' })
  .onConflict('email')
  .ignore()

Upsert 작업

// WITH (공통 테이블 식)
knex.with('active_users', (qb) => {
  qb.select('*').from('users').where('status', 'active');
})
.select('*')
.from('active_users')
.where('age', '>', 18);

// 재귀 CTE
knex.withRecursive('tree', (qb) => {
  qb.select('*').from('categories').where('parent_id', null)
    .union((qb2) => {
      qb2.select('c.*')
        .from('categories as c')
        .join('tree as t', 'c.parent_id', 't.id');
    });
})
.select('*').from('tree');

공통 테이블 식

// 서브쿼리
knex('users')
  .whereIn('id', function() {
    this.select('user_id').from('orders').where('total', '>', 100);
  })

// SELECT 내 서브쿼리
knex('users')
  .select('name')
  .select(
    knex('orders')
      .count('*')
      .where('orders.user_id', knex.raw('??', ['users.id']))
      .as('order_count')
  )

// FROM 절의 서브쿼리
knex
  .select('*')
  .from(
    knex('users')
      .where('status', 'active')
      .as('active_users')
  )

서브쿼리

// 윈도우 함수
knex('orders')
  .select('*')
  .select(
    knex.raw('ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) as row_num')
  )

// JSON 연산 (PostgreSQL)
knex('users')
  .select('name')
  .select(knex.raw("metadata->>'age' as age"))
  .where(knex.raw("metadata->>'city'"), 'New York')

// 배열 연산 (PostgreSQL)
knex('users')
  .whereRaw('? = ANY(tags)', ['javascript'])

고급 SQL 기능

연결 및 풀 관리

데이터베이스 연결 및 풀 관리

// 연결 풀 설정
const knex = require('knex')({
  client: 'pg',
  connection: {
    host: '127.0.0.1',
    user: 'user',
    password: 'password',
    database: 'myapp'
  },
  pool: {
    min: 2,
    max: 10,
    acquireTimeoutMillis: 30000,
    idleTimeoutMillis: 30000
  }
});

// 연결 풀 파괴
await knex.destroy();

// 재초기화
knex.initialize();

풀 설정

// 연결 afterCreate 훅
const knex = require('knex')({
  client: 'pg',
  connection: { /* ... */ },
  pool: {
    afterCreate: function(conn, done) {
      conn.query('SET timezone="UTC";', function(err) {
        done(err, conn);
      });
    }
  }
});

// 동적 연결
const knex = require('knex')({
  client: 'mysql',
  connection: async () => {
    const token = await getAuthToken();
    return {
      host: 'localhost',
      user: 'user',
      password: token,
      database: 'myapp'
    };
  }
});

연결 훅

// 쿼리 디버깅
const knex = require('knex')({
  client: 'pg',
  connection: { /* ... */ },
  debug: true  // 모든 쿼리 로그
});

// 특정 쿼리 디버깅
knex('users')
  .select('*')
  .debug()
  .then(rows => console.log(rows));

// 실행 없이 SQL 얻기
const sql = knex('users')
  .where('id', 1)
  .toSQL();
console.log(sql.sql);      // SQL 문자열
console.log(sql.bindings); // 파라미터

디버깅

Knex.js 치트 시트