Database

Database manager tersedia melalui binding container db. Konfigurasi awal menggunakan SQLite dan dapat diganti ke MySQL melalui environment.

Konfigurasi koneksi

SQLite

DB_CONNECTION=sqlite
DB_DATABASE=database/database.sqlite

MySQL

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=lanjit
DB_USERNAME=root
DB_PASSWORD=

Saat migration MySQL dijalankan, Lanjit mencoba membuat database yang belum tersedia.

Query builder

const posts = await app.make('db')
  .table('posts')
  .where('published', true)
  .orderBy('created_at', 'desc')
  .limit(20)
  .get();

Method yang tersedia meliputi where, orderBy, limit, get, first, find, insert, update, dan delete.

const id = await app.make('db').table('posts').insert({
  title: 'Halo Lanjit',
  published: true,
});

await app.make('db').table('posts').where('id', id).update({ title: 'Judul baru' });

Model

Model menggunakan pola active record.

import { Model } from '../../framework/Database/Model.js';

export class Post extends Model {
  static table = 'posts';
}

const post = new Post({ title: 'Dokumentasi Lanjit' });
await post.save();

const existing = await Post.find(1);
const recent = await Post.query().orderBy('created_at', 'desc').get();

Migration

Buat migration dengan Artisan:

node artisan make:migration create_posts_table

export async function up(schema) {
  await schema.create('posts', (table) => {
    table.id();
    table.string('title');
    table.text('body').nullable();
    table.boolean('published');
    table.timestamps();
  });
}

export async function down(schema) {
  await schema.dropIfExists('posts');
}

Menjalankan migration

node artisan migrate
node artisan migrate:status
node artisan migrate:rollback
node artisan migrate:fresh --seed

migrate:fresh menghapus seluruh tabel. Gunakan hanya ketika kehilangan data dapat diterima.

Transaction

Jalankan beberapa operasi sebagai satu unit atomik melalui database manager.

await app.make('db').transaction(async (connection) => {
  await connection.statement('UPDATE "accounts" SET "balance" = ? WHERE "id" = ?', [900, 1]);
  await connection.statement('UPDATE "accounts" SET "balance" = ? WHERE "id" = ?', [1100, 2]);
});