Module 5

Database Integration & Analytics

Learn how to use SQLite database with your bot to store and retrieve data. Track user activity, save preferences, and build simple leaderboards.

4 steps

Intermediate

Learning Objectives

By the end of this module, you will be able to:

Set up a basic SQLite database with Bun

Track user activity (messages, reactions)

Build a simple leaderboard command

Prepare database for production deployment

Database Setup

Set up a SQLite database to store bot data persistently using Bun's built-in database support.

Initialize SQLite Database

Bun includes SQLite built-in. Create a database file and define a simple table to track user activity.

Database Setup

import { Database } from 'bun:sqlite'

// Create or open database
const db = new Database('bot.db')

// Create a simple user stats table
db.run(`
  CREATE TABLE IF NOT EXISTS user_stats (
    user_id TEXT PRIMARY KEY,
    space_id TEXT NOT NULL,
    message_count INTEGER DEFAULT 0,
    reaction_count INTEGER DEFAULT 0,
    last_active INTEGER DEFAULT 0
  )
`)

console.log('✅ Database initialized!')

What This Does

• Creates a bot.db file in your project
• Defines a user_stats table with basic columns
• Uses IF NOT EXISTS to avoid errors on restart
• Stores user ID, space ID, and activity counts

Tracking Activity

Track user messages and reactions by updating the database whenever users interact.

Track Messages

Update the database every time a user sends a message.

Message Tracking

bot.onMessage(async (handler, event) => {
  const { userId, spaceId } = event
  
  // Skip bot messages
  if (userId === bot.botId) return
  
  // Update or insert user stats
  db.run(`
    INSERT INTO user_stats (user_id, space_id, message_count, last_active)
    VALUES (?, ?, 1, ?)
    ON CONFLICT(user_id) DO UPDATE SET
      message_count = message_count + 1,
      last_active = ?
  `, [userId, spaceId, Date.now(), Date.now()])
  
  console.log(`📊 Updated stats for ${userId}`)
})

Track Reactions

Similarly, track when users add reactions.

Reaction Tracking

bot.onReaction(async (handler, event) => {
  const { userId, spaceId } = event
  
  // Skip bot reactions
  if (userId === bot.botId) return
  
  // Update reaction count
  db.run(`
    INSERT INTO user_stats (user_id, space_id, reaction_count, last_active)
    VALUES (?, ?, 1, ?)
    ON CONFLICT(user_id) DO UPDATE SET
      reaction_count = reaction_count + 1,
      last_active = ?
  `, [userId, spaceId, Date.now(), Date.now()])
})

Key Concepts

• INSERT ... ON CONFLICT updates existing records or creates new ones
• Always skip bot's own messages to avoid tracking bot activity
• Use Date.now() to track when users were last active
• Database operations are fast and don't slow down message handling

Leaderboard System

Query the database to show top users by message count.

Leaderboard Command

Create a /leaderboard command that shows the most active users.

Leaderboard Command

bot.onSlashCommand('leaderboard', async (handler, { channelId, spaceId }) => {
  try {
    // Query top 10 users by message count
    const topUsers = db.query(`
      SELECT user_id, message_count, reaction_count
      FROM user_stats
      WHERE space_id = ?
      ORDER BY message_count DESC
      LIMIT 10
    `).all(spaceId)
    
    if (topUsers.length === 0) {
      await handler.sendMessage(channelId, '📊 No activity data yet!')
      return
    }
    
    // Build leaderboard message
    let leaderboard = '🏆 **Top Contributors**\n\n'
    
    topUsers.forEach((user, index) => {
      const medal = index === 0 ? '🥇' : index === 1 ? '🥈' : index === 2 ? '🥉' : `${index + 1}.`
      leaderboard += `${medal} <@${user.user_id}>\n`
      leaderboard += `   💬 ${user.message_count} messages | ❤️ ${user.reaction_count} reactions\n\n`
    })
    
    await handler.sendMessage(channelId, leaderboard)
    
  } catch (error) {
    console.error('Leaderboard error:', error)
    await handler.sendMessage(channelId, '❌ Error fetching leaderboard')
  }
})

SQL Query

• ORDER BY message_count DESC sorts highest first
• LIMIT 10 gets only top 10
• WHERE space_id = ? filters by space

Formatting

• Use medals (🥇🥈🥉) for top 3
• Mention users with <@userId>
• Show both messages and reactions

Database for Production

Prepare your database code to work in production environments like Render.

Use Environment Variables

Make your database path configurable so it works both locally and in production.

Production-Ready Database Setup

import { Database } from 'bun:sqlite'

// Use environment variable for database path
// Locally: uses './bot.db'
// On Render: uses '/data/bot.db' (persistent disk)
const DB_PATH = process.env.DATABASE_PATH || './bot.db'

// Create or open database
const db = new Database(DB_PATH)

// Create tables
db.run(`
  CREATE TABLE IF NOT EXISTS user_stats (
    user_id TEXT PRIMARY KEY,
    space_id TEXT NOT NULL,
    message_count INTEGER DEFAULT 0,
    reaction_count INTEGER DEFAULT 0,
    last_active INTEGER DEFAULT 0
  )
`)

console.log(`✅ Database initialized at: ${DB_PATH}`)

How This Works

• Local Development: No env var set → uses ./bot.db
• Production (Render): Set DATABASE_PATH=/data/bot.db
• Persistent Storage: Render Disks keep data between deployments

Render Database Configuration

When deploying to Render, you'll need to configure a persistent disk for your database.

Render Disk Setup

• Add a persistent disk in Render
• Mount path: /data
• Size: 1 GB (free tier)
• Set env: DATABASE_PATH=/data/bot.db

Why This Matters

• Data persists across restarts
• Leaderboards don't reset
• User stats are saved
• Production-ready setup

Next Steps

You'll learn the complete Render deployment process in Module 6: Deployment & Production, including setting up the persistent disk, environment variables, and automatic deployments.

Module 5 Complete!

Great work! You've learned how to use SQLite with your bot to store data, track user activity, and build leaderboards. Your bot can now remember information and show useful statistics!

What You Built:

SQLite database setup

User activity tracking

Leaderboard system

Database for production (Render)

Ready For:

Deploy to Render

Set up Towns Developer Portal

Connect webhook

Test live bot in Towns

Continue to Module 6

Back to Course Overview