Turso: Edge Database dengan libSQL

# Instal Turso CLI
curl -sSfL https://get.tur.so/install.sh | bash
source ~/.bashrc

# Login ke Turso
turso auth login

# Membuat database
turso db create my-app-db

# Mendapatkan URL database dan token
turso db show my-app-db --url
turso db tokens create my-app-db

# Membuat database di region tertentu
turso db create my-app-db --location sjc  # San Jose

# Menambah replica di region lain
turso db replicate my-app-db ams  # Amsterdam
turso db replicate my-app-db sin  # Singapore

# Lihat status database
turso db show my-app-db

# Koneksi via turso CLI shell
turso db shell my-app-db

# Atau menggunakan libSQL client
npm install @libsql/client

-- Membuat tabel (SQLite syntax)
CREATE TABLE IF NOT EXISTS posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    slug TEXT UNIQUE NOT NULL,
    content TEXT,
    author_id INTEGER NOT NULL,
    category TEXT DEFAULT 'general',
    published BOOLEAN DEFAULT 0,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now')),
    FOREIGN KEY (author_id) REFERENCES authors(id)
);

CREATE TABLE IF NOT EXISTS authors (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    bio TEXT
);

-- Membuat index
CREATE INDEX idx_posts_author ON posts(author_id);
CREATE INDEX idx_posts_category ON posts(category);
CREATE INDEX idx_posts_published ON posts(published, created_at);

-- Full-text search (FTS5)
CREATE VIRTUAL TABLE posts_fts USING fts5(
    title, content,
    content='posts',
    content_rowid='id'
);

-- Trigger untuk sync FTS
CREATE TRIGGER posts_ai AFTER INSERT ON posts BEGIN
    INSERT INTO posts_fts(rowid, title, content)
    VALUES (new.id, new.title, new.content);
END;

CREATE TRIGGER posts_ad AFTER DELETE ON posts BEGIN
    INSERT INTO posts_fts(posts_fts, rowid, title, content)
    VALUES ('delete', old.id, old.title, old.content);
END;

import { createClient } from '@libsql/client';

// Koneksi ke Turso Cloud
const db = createClient({
    url: 'libsql://my-app-db.turso.io',
    authToken: process.env.TURSO_AUTH_TOKEN,
});

// Koneksi ke local file (development)
// const db = createClient({ url: 'file:local.db' });

// Membuat tabel
await db.execute(`
    CREATE TABLE IF NOT EXISTS todos (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        completed BOOLEAN DEFAULT 0,
        created_at TEXT DEFAULT (datetime('now'))
    )
`);

// INSERT
await db.execute({
    sql: 'INSERT INTO todos (title) VALUES (?)',
    args: ['Belajar Turso']
});

// INSERT batch
await db.batch([
    {
        sql: 'INSERT INTO todos (title) VALUES (?)',
        args: ['Setup edge replicas']
    },
    {
        sql: 'INSERT INTO todos (title) VALUES (?)',
        args: ['Deploy ke production']
    }
], 'write');

// SELECT
const result = await db.execute('SELECT * FROM todos');
for (const row of result.rows) {
    console.log(`${row.id}: ${row.title} [${row.completed ? '✓' : '○'}]`);
}

// SELECT dengan parameter
const filtered = await db.execute({
    sql: 'SELECT * FROM todos WHERE completed = ?',
    args: [0]
});

// Transaction
const tx = await db.transaction('write');
try {
    await tx.execute({
        sql: 'UPDATE todos SET completed = 1 WHERE id = ?',
        args: [1]
    });
    await tx.execute({
        sql: 'INSERT INTO todos (title) VALUES (?)',
        args: ['Tugas baru']
    });
    await tx.commit();
} catch (e) {
    await tx.rollback();
    throw e;
}

import libsql_experimental as libsql

# Koneksi ke Turso
conn = libsql.connect(
    database="libsql://my-app-db.turso.io",
    auth_token=os.environ.get("TURSO_AUTH_TOKEN")
)

# Atau koneksi local
# conn = libsql.connect("local.db")

# DDL
conn.execute("""
    CREATE TABLE IF NOT EXISTS products (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        price REAL NOT NULL,
        category TEXT,
        stock INTEGER DEFAULT 0
    )
""")

# INSERT
conn.execute(
    "INSERT INTO products (name, price, category, stock) VALUES (?, ?, ?, ?)",
    ["Laptop Gaming", 15000000.0, "Electronics", 50]
)

# SELECT
rows = conn.execute("SELECT * FROM products WHERE price > ?", [1000000]).fetchall()
for row in rows:
    print(f"{row[1]}: Rp {row[2]:,.0f}")

# Commit
conn.commit()

import { createClient } from '@libsql/client';

// Embedded replica: data disimpan lokal, di-sync dari primary
const db = createClient({
    url: 'file:local-replica.db',     // File lokal untuk replica
    syncUrl: 'libsql://my-app-db.turso.io',  // Primary URL
    authToken: process.env.TURSO_AUTH_TOKEN,
    syncInterval: 60,  // Sync setiap 60 detik (detik)
});

// Manual sync (opsional, untuk sync on-demand)
await db.sync();

// Read lokal (cepat!)
const result = await db.execute('SELECT * FROM products WHERE category = ?', ['Electronics']);
console.log(result.rows); // Dibaca dari file lokal

// Write tetap di-route ke primary
await db.execute({
    sql: 'INSERT INTO products (name, price, category) VALUES (?, ?, ?)',
    args: ['New Product', 100000, 'Electronics']
});

// Cek sync status
const syncResult = await db.sync();
console.log(`Synced ${syncResult.frames_applied} frames`);

// Cloudflare Worker dengan Turso embedded replica
import { createClient } from '@libsql/client';

export default {
    async fetch(request, env) {
        const db = createClient({
            url: 'file:turso-replica.db',  // Di-sync oleh platform
            syncUrl: env.TURSO_DB_URL,
            authToken: env.TURSO_AUTH_TOKEN,
        });

        const url = new URL(request.url);

        if (url.pathname === '/api/products') {
            // Read dari local replica — sangat cepat!
            const result = await db.execute('SELECT * FROM products LIMIT 50');
            return Response.json(result.rows);
        }

        if (request.method === 'POST' && url.pathname === '/api/products') {
            const body = await request.json();
            // Write di-route ke primary
            const result = await db.execute({
                sql: 'INSERT INTO products (name, price, category) VALUES (?, ?, ?)',
                args: [body.name, body.price, body.category]
            });
            return Response.json({ success: true, id: Number(result.last_insert_rowid) });
        }

        return new Response('Not Found', { status: 404 });
    }
};

# Membuat branch dari database utama
turso db branch create my-app-db staging

# Lihat semua branches
turso db branch list my-app-db

# Mendapatkan URL branch untuk testing
turso db show my-app-db --branch staging

# Koneksi ke branch
turso db shell my-app-db --branch staging

# Menghapus branch
turso db branch drop my-app-db staging

# Reset branch ke state primary
turso db branch reset my-app-db staging

# Menggunakan branch di CI/CD pipeline
# 1. Buat branch dari production
# 2. Jalankan migration di branch
# 3. Jalankan tests
# 4. Merge branch ke production (atau promote)

# .github/workflows/test.yml
name: Test with Turso Branch
on: [pull_request]

jobs:
  test:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - name: Create Turso branch
        run: |
          turso auth login
          BRANCH_NAME="pr-${{ github.event.pull_request.number }}"
          turso db branch create my-app-db $BRANCH_NAME

      - name: Run migrations
        env:
          TURSO_DB_URL: ${{ secrets.TURSO_DB_URL }}
          TURSO_AUTH_TOKEN: ${{ secrets.TURSO_AUTH_TOKEN }}
        run: |
          BRANCH_URL=$(turso db show my-app-db --branch "pr-${{ github.event.pull_request.number }}" --url)
          # Run migrations terhadap branch
          npm run migrate -- --url=$BRANCH_URL

      - name: Run tests
        run: npm test

      - name: Cleanup
        if: always()
        run: |
          turso db branch drop my-app-db "pr-${{ github.event.pull_request.number }}"

import { createClient } from '@libsql/client';

const db = createClient({
    url: 'libsql://my-app-db.turso.io',
    authToken: process.env.TURSO_AUTH_TOKEN,
});

// Tabel untuk menyimpan vectors
await db.execute(`
    CREATE TABLE IF NOT EXISTS documents (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        content TEXT NOT NULL,
        embedding F32_BLOB(384),  -- 384-dimension float32 vector
        metadata TEXT
    )
`);

// Membuat vector index
await db.execute(`
    CREATE INDEX IF NOT EXISTS idx_docs_embedding
    ON documents(libsql_vector_idx(embedding))
`);

// Menyimpan dokumen dengan embedding
// (embedding dihasilkan oleh model seperti OpenAI, Sentence Transformers, dll.)
const documents = [
    { content: 'CockroachDB adalah distributed SQL database', embedding: [...] },
    { content: 'ClickHouse untuk analytical queries', embedding: [...] },
    { content: 'Redis digunakan sebagai caching layer', embedding: [...] },
];

for (const doc of documents) {
    await db.execute({
        sql: `INSERT INTO documents (content, embedding)
              VALUES (?, vector32(?))`,
        args: [doc.content, new Float32Array(doc.embedding)]
    });
}

// Vector similarity search (cosine distance)
const queryEmbedding = [...]; // embedding dari query user

const results = await db.execute({
    sql: `
        SELECT
            content,
            vector_distance_cos(embedding, vector32(?)) AS distance
        FROM documents
        ORDER BY distance
        LIMIT 5
    `,
    args: [new Float32Array(queryEmbedding)]
});

for (const row of results.rows) {
    console.log(`[Distance: ${row.distance.toFixed(4)}] ${row.content}`);
}

// db/index.ts
import { createClient } from '@libsql/client';
import { drizzle } from 'drizzle-orm/libsql';

const client = createClient({
    url: process.env.TURSO_DATABASE_URL!,
    authToken: process.env.TURSO_AUTH_TOKEN!,
});

export const db = drizzle(client);

// db/schema.ts
import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core';

export const posts = sqliteTable('posts', {
    id: integer('id').primaryKey(),
    title: text('title').notNull(),
    content: text('content'),
    published: integer('published', { mode: 'boolean' }).default(false),
    createdAt: text('created_at').default(new Date().toISOString()),
});

// app/api/posts/route.ts
import { db } from '@/db';
import { posts } from '@/db/schema';
import { eq } from 'drizzle-orm';

export async function GET() {
    const allPosts = await db.select().from(posts)
        .where(eq(posts.published, true));
    return Response.json(allPosts);
}

export async function POST(request: Request) {
    const body = await request.json();
    const result = await db.insert(posts).values({
        title: body.title,
        content: body.content,
    }).returning();
    return Response.json(result[0], { status: 201 });
}

// src/lib/server/db.ts
import { createClient } from '@libsql/client';
import { TURSO_DATABASE_URL, TURSO_AUTH_TOKEN } from '$env/static/private';

export const db = createClient({
    url: TURSO_DATABASE_URL,
    authToken: TURSO_AUTH_TOKEN,
});

// src/routes/+page.server.ts
import { db } from '$lib/server/db';

export async function load() {
    const result = await db.execute(
        'SELECT * FROM posts WHERE published = 1 ORDER BY created_at DESC'
    );
    return { posts: result.rows };
}

export const actions = {
    create: async ({ request }) => {
        const data = await request.formData();
        await db.execute({
            sql: 'INSERT INTO posts (title, content) VALUES (?, ?)',
            args: [data.get('title'), data.get('content')]
        });
        return { success: true };
    }
};