Coffee Tracker: Exploring Bun's SQLite Integration, Shell API, and v1.1 updates

With the Bun reached 1.0 last year and recent release of Bun 1.1, JavaScript developers have a lot to be excited about. Windows support is a big headline feature, but there are several less-heralded improvements that make daily development significantly smoother. Today, we’ll explore two standout features: the built-in SQLite database and the Bun Shell API.

To showcase these features in action, we’ll build a simple coffee consumption tracker. It’s a toy example that demonstrates how these tools simplify everyday development tasks.

The Project: A Coffee Consumption Tracker

This utility will track what coffee you drink throughout the day, recording both the type and amount. It will calculate your caffeine intake and provide some basic statistics.

Here’s the complete code that we’ll break down and explain:

import { Database } from "bun:sqlite";
import { $ } from "bun";

// Initialize our database
const db = new Database("coffee-tracker.sqlite");

// Create our tables if they don't exist
db.run(`
  CREATE TABLE IF NOT EXISTS coffee_types (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT UNIQUE,
    caffeine_mg INTEGER
  );
  
  CREATE TABLE IF NOT EXISTS coffee_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    coffee_id INTEGER,
    cups INTEGER,
    timestamp TEXT,
    FOREIGN KEY(coffee_id) REFERENCES coffee_types(id)
  );
`);

// Seed some default coffee types if none exist
const coffeeCount = db.query("SELECT COUNT(*) as count FROM coffee_types").get().count;

if (coffeeCount === 0) {
  db.run(`
    INSERT INTO coffee_types (name, caffeine_mg) VALUES 
    ('Espresso', 63),
    ('Drip Coffee', 95),
    ('Cold Brew', 125),
    ('Americano', 77),
    ('Cappuccino', 63);
  `);
  console.log("🌱 Seeded default coffee types!");
}

// Helper to clear the terminal
async function clearScreen() {
  if (process.platform === "win32") {
    await $`cls`.quiet();
  } else {
    await $`clear`.quiet();
  }
}

// Display a fancy ASCII art title
function displayTitle() {
  console.log(`
  ☕️ ☕️ ☕️ ☕️ ☕️ ☕️ ☕️ ☕️ ☕️ ☕️ ☕️ ☕️ ☕️ ☕️ ☕️ ☕️
  
     COFFEE TRACKER - POWERED BY BUN SQLITE
  
  ☕️ ☕️ ☕️ ☕️ ☕️ ☕️ ☕️ ☕️ ☕️ ☕️ ☕️ ☕️ ☕️ ☕️ ☕️ ☕️
  `);
}

// List available coffee types
function listCoffeeTypes() {
  const coffeeTypes = db.query("SELECT id, name, caffeine_mg FROM coffee_types").all();
  console.log("\nAvailable coffee types:");
  
  coffeeTypes.forEach((coffee, index) => {
    console.log(`  ${index + 1}. ${coffee.name} (${coffee.caffeine_mg}mg caffeine)`);
  });
  
  return coffeeTypes;
}

// Log a coffee
function logCoffee(coffeeId, cups) {
  const timestamp = new Date().toISOString();
  db.run("INSERT INTO coffee_log (coffee_id, cups, timestamp) VALUES (?, ?, ?)", 
    [coffeeId, cups, timestamp]);
    
  console.log(`\n✅ Logged ${cups} cup(s)!`);
}

// Show today's stats
function showTodayStats() {
  const today = new Date().toISOString().split('T')[0];
  
  const stats = db.query(`
    SELECT 
      c.name, 
      SUM(l.cups) as total_cups,
      SUM(l.cups * c.caffeine_mg) as total_caffeine
    FROM coffee_log l
    JOIN coffee_types c ON l.coffee_id = c.id
    WHERE l.timestamp LIKE '${today}%'
    GROUP BY c.name
  `).all();
  
  console.log("\n📊 Today's Coffee Stats:");
  
  if (stats.length === 0) {
    console.log("  No coffee logged today yet.");
    return;
  }
  
  let totalCups = 0;
  let totalCaffeine = 0;
  
  stats.forEach((stat) => {
    console.log(`  ${stat.name}: ${stat.total_cups} cup(s) - ${stat.total_caffeine}mg caffeine`);
    totalCups += stat.total_cups;
    totalCaffeine += stat.total_caffeine;
  });
  
  console.log(`\n  Total: ${totalCups} cup(s) - ${totalCaffeine}mg caffeine`);
  
  // Add a fun caffeine warning
  if (totalCaffeine > 400) {
    console.log("\n⚠️ Caffeine warning: You've exceeded the recommended daily limit of 400mg!");
  } else {
    const remaining = 400 - totalCaffeine;
    console.log(`\n✅ You can safely drink ${Math.floor(remaining / 63)} more espresso shots today.`);
  }
}

// Export database to a backup file
async function exportDatabase() {
  const timestamp = new Date().toISOString().replace(/:/g, '-');
  const backupName = `coffee-backup-${timestamp}.sqlite`;
  
  // Use Bun's file APIs for a fast copy
  const dbContent = await Bun.file("coffee-tracker.sqlite").arrayBuffer();
  await Bun.write(backupName, dbContent);
  
  console.log(`\n✅ Database exported to ${backupName}`);
}

// Import from a backup file
async function importDatabase() {
  // List available backups
  const stdout = await $`ls -1 coffee-backup-*.sqlite 2>/dev/null || echo "No backups found"`.text();
  
  const backups = stdout.trim().split('\n');
  if (backups[0] === "No backups found") {
    console.log("\n❌ No backup files found.");
    return;
  }
  
  console.log("\nAvailable backups:");
  backups.forEach((backup, i) => {
    console.log(`  ${i+1}. ${backup}`);
  });
  
  const choice = parseInt(prompt("\nSelect backup to import (0 to cancel): ") || "0");
  if (choice === 0 || choice > backups.length) {
    console.log("Import cancelled.");
    return;
  }
  
  const selectedBackup = backups[choice-1];
  
  // Close current database
  db.close();
  
  // Create a backup of current database
  const currentDbContent = await Bun.file("coffee-tracker.sqlite").arrayBuffer();
  await Bun.write("coffee-tracker-before-import.sqlite", currentDbContent);
  
  // Import selected backup
  const backupContent = await Bun.file(selectedBackup).arrayBuffer();
  await Bun.write("coffee-tracker.sqlite", backupContent);
  
  console.log(`\n✅ Imported database from ${selectedBackup}`);
  console.log("   (Your previous database was backed up to coffee-tracker-before-import.sqlite)");
  
  // Restart the app
  console.log("\n🔄 Restarting app to apply changes...");
  await new Promise(r => setTimeout(r, 2000));
  
  // In a real app, you'd restart more elegantly
  process.exit(0);
}

// Main menu
async function main() {
  while (true) {
    await clearScreen();
    displayTitle();
    
    console.log("\nWhat would you like to do?");
    console.log("1. Log a coffee");
    console.log("2. View today's stats");
    console.log("3. Export database");
    console.log("4. Import database");
    console.log("5. Exit");
    
    const choice = prompt("Enter your choice (1-5): ");
    
    if (choice === "1") {
      const coffeeTypes = listCoffeeTypes();
      const coffeeChoice = parseInt(prompt(`\nSelect coffee type (1-${coffeeTypes.length}): `) || "0");
      
      if (coffeeChoice > 0 && coffeeChoice <= coffeeTypes.length) {
        const cups = parseInt(prompt("How many cups? ") || "1");
        logCoffee(coffeeTypes[coffeeChoice - 1].id, cups);
        await new Promise(r => setTimeout(r, 1500));
      }
    } else if (choice === "2") {
      showTodayStats();
      await new Promise(r => setTimeout(r, 5000));
    } else if (choice === "3") {
      await exportDatabase();
      await new Promise(r => setTimeout(r, 2000));
    } else if (choice === "4") {
      await importDatabase();
    } else if (choice === "5") {
      console.log("\nThanks for using Coffee Tracker! ☕️");
      break;
    }
  }
}

// Run the app
await main();

Let’s dive into the main features showcased by this code.

Feature 1: Built-in SQLite Integration

One of the most practical features in Bun is its built-in SQLite support, eliminating the need for external database drivers or complex setups.

Setting Up the Database

import { Database } from "bun:sqlite";

// Initialize our database
const db = new Database("coffee-tracker.sqlite");

Notice how clean this is - no need to install any packages! The database integration is part of the Bun runtime, accessible through the bun:sqlite import.

Creating Tables with Multi-Statement Queries

Bun 1.1 enhanced its SQLite support by allowing multiple SQL statements in a single call:

db.run(`
  CREATE TABLE IF NOT EXISTS coffee_types (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT UNIQUE,
    caffeine_mg INTEGER
  );
  
  CREATE TABLE IF NOT EXISTS coffee_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    coffee_id INTEGER,
    cups INTEGER,
    timestamp TEXT,
    FOREIGN KEY(coffee_id) REFERENCES coffee_types(id)
  );
`);

Previously, you’d need to execute each statement separately, but Bun 1.1 lets you send an entire schema at once. This makes database initialization more concise and readable.

Querying Data

The querying API is intuitive and familiar to SQL developers:

// Simple query with .get() to fetch a single row
const coffeeCount = db.query("SELECT COUNT(*) as count FROM coffee_types").get().count;

// Using .all() to fetch multiple rows
const coffeeTypes = db.query("SELECT id, name, caffeine_mg FROM coffee_types").all();

The .get() method returns a single row, while .all() returns an array of rows. Bun automatically converts the result to JavaScript objects with properly named properties.

Parameterized Queries

For data insertion, we use parameterized queries to prevent SQL injection:

db.run("INSERT INTO coffee_log (coffee_id, cups, timestamp) VALUES (?, ?, ?)", 
  [coffeeId, cups, timestamp]);

The placeholder ? syntax will be familiar to anyone who’s used SQLite before, making the transition to Bun’s implementation seamless.

Feature 2: Bun Shell API

The other standout feature is Bun’s shell API, which provides a clean way to interact with the system shell from JavaScript.

Cross-Platform Shell Commands

The $ template tag lets you execute shell commands directly:

import { $ } from "bun";

// Helper to clear the terminal
async function clearScreen() {
  if (process.platform === "win32") {
    await $`cls`.quiet();
  } else {
    await $`clear`.quiet();
  }
}

This concise syntax replaces clunky child_process.spawn() calls. The .quiet() method suppresses output to the console, but you can still access the result.

Capturing Command Output

Looking at our import function, we see how to capture and parse command output:

const stdout = await $`ls -1 coffee-backup-*.sqlite 2>/dev/null || echo "No backups found"`.text();
  
const backups = stdout.trim().split('\n');

The .text() method returns the command’s stdout as a string. You can also use:

.json() for parsing JSON output
.arrayBuffer() for binary data
.blob() for web-compatible binary data

Error Handling

In Bun 1.1, the shell API will automatically throw an error if a command exits with a non-zero status code. This makes error handling intuitive:

try {
  await $`some-command-that-might-fail`;
} catch (error) {
  console.error("Command failed:", error.message);
}

If you don’t want this behavior, you can use .throws(false) to prevent exceptions:

const { exitCode } = await $`risky-command`.throws(false);
if (exitCode !== 0) {
  // Handle error case
}

Putting It All Together

Our coffee tracker demonstrates these features working together to create a seamless developer experience:

We create and seed a SQLite database without any external dependencies
We use the shell API to handle cross-platform terminal operations
We implement database backup and restore using both Bun’s file API and shell commands

This might look like a trivial example, but it showcases a significant improvement in the JavaScript toolchain. Before Bun, you would need:

A SQLite driver package (like better-sqlite3)
Additional packages for shell commands (like execa or shelljs)
Careful configuration for cross-platform compatibility

Taking It Further: Database Import Feature

Bun 1.1 also introduced the ability to import SQLite databases directly as modules. Let’s extend our coffee tracker with a separate reporting script:

// coffee-report.js
import db from "./coffee-tracker.sqlite" with { type: "sqlite" };

function generateCoffeeReport() {
  // Get lifetime stats
  const stats = db.query(`
    SELECT 
      COUNT(*) as total_entries,
      SUM(l.cups) as total_cups,
      ROUND(SUM(l.cups * c.caffeine_mg)) as total_caffeine
    FROM coffee_log l
    JOIN coffee_types c ON l.coffee_id = c.id
  `).get();
  
  console.log("☕️ COFFEE CONSUMPTION REPORT ☕️");
  console.log("===============================");
  console.log(`Total entries: ${stats.total_entries}`);
  console.log(`Total cups consumed: ${stats.total_cups}`);
  console.log(`Total caffeine consumed: ${stats.total_caffeine}mg`);
  
  // Get consumption by coffee type
  const byType = db.query(`
    SELECT 
      c.name, 
      SUM(l.cups) as cups,
      ROUND(SUM(l.cups * c.caffeine_mg)) as caffeine
    FROM coffee_log l
    JOIN coffee_types c ON l.coffee_id = c.id
    GROUP BY c.name
    ORDER BY cups DESC
  `).all();
  
  console.log("\nCONSUMPTION BY TYPE");
  console.log("===================");
  byType.forEach(type => {
    console.log(`${type.name}: ${type.cups} cups (${type.caffeine}mg caffeine)`);
  });
}

generateCoffeeReport();

With this simple script, you can generate reports from your coffee database without duplicating any database setup code.

The Best Part: Single-File Executable

Finally, Bun 1.1 enhanced its ability to compile SQLite databases into executables. For example, we could create a single distributable file:

// coffee-tracker-app.js
import db from "./coffee-tracker.sqlite" with { type: "sqlite", embed: "true" };

// ... rest of app code ...

Then compile it:

bun build --compile coffee-tracker-app.js

This creates a single executable file with both the application code and the database embedded inside, perfect for distribution to users who don’t have Bun installed.

Conclusion

Bun continues to evolve as an attractive alternative to Node.js, especially for developers who value simplicity and performance. These features in version 1.1 strengthen the case for including Bun in your JavaScript toolkit.

Try the coffee tracker for yourself, and you might find these features becoming a staple in your development workflow!

The Project: A Coffee Consumption Tracker#

Feature 1: Built-in SQLite Integration#

Setting Up the Database#

Creating Tables with Multi-Statement Queries#

Querying Data#

Parameterized Queries#

Feature 2: Bun Shell API#

Cross-Platform Shell Commands#

Capturing Command Output#

Error Handling#

Putting It All Together#

Taking It Further: Database Import Feature#

The Best Part: Single-File Executable#

Conclusion#