Building a Real-time Crypto Analytics Dashboard with QuestDB on Windows: A Step-by-Step Guide

Crypto trading never sleeps, and the markets move faster than most databases can keep up. If you’re serious about running a trading bot, crunching tick-by-tick data, or just keeping a close eye on your crypto portfolio, you need a backend built for speed. That’s where QuestDB comes in—a purpose-built time-series database that’s designed for real-time analytics. In this guide, let’s dive into how you can set up a live crypto dashboard with QuestDB and effortlessly stream market data as it happens.

What We’re Building

By the end of this tutorial, you’ll have a fully functional cryptocurrency analytics dashboard that:

• Ingests real-time market data from Coinbase WebSocket feeds
• Stores millions of data points in QuestDB
• Automatically generates 1-minute OHLCV candles
• Displays beautiful, real-time visualizations
• Handles thousands of updates per second

The best part? You don’t need PostgreSQL installed. QuestDB comes with its own PostgreSQL wire protocol implementation, making it a true drop-in solution.

Why QuestDB?

Before diving into the code, let’s understand why QuestDB is perfect for financial time-series data:

1. Built for Speed: QuestDB can ingest millions of rows per second. It’s written in C++ and Java with zero garbage collection in the hot path.

2. SQL Interface: No need to learn a new query language. If you know SQL, you already know QuestDB.

3. Automatic Partitioning: Tables are automatically partitioned by time, making queries blazingly fast.

4. Column-based Storage: Efficient compression means you can store years of tick data without breaking the bank.

5. Zero Dependencies: Unlike TimescaleDB, you don’t need PostgreSQL. QuestDB is completely self-contained.

Prerequisites

Before we start, make sure you have:

• Windows 10 or 11
• Python 3.8 or higher
• Basic knowledge of SQL and Python
• A desire to work with real-time data

Step 1: Installing QuestDB on Windows

Installing QuestDB on Windows is refreshingly simple. No installers, no complex configurations, just download and run.

# 1. Download QuestDB from https://questdb.io/download/
# 2. Extract the ZIP file to C:\questdb
# 3. Goto the Folder c:\questdb\bin and run questdb.exe this will run the questdb in the background

That’s it! QuestDB is now running with:

• Web Console: http://localhost:9000
• PostgreSQL endpoint: localhost:8812
• InfluxDB Line Protocol: localhost:9009

You can verify the installation by opening the web console in your browser. You should see QuestDB’s clean, modern interface ready for your queries.

Step 2: Understanding the Architecture

Our system consists of four main components working in harmony:

Coinbase WebSocket → Python Service → QuestDB → Web Dashboard

The Python service acts as the bridge between Coinbase’s real-time data feeds and QuestDB. It handles WebSocket connections, data parsing, and automatic candle generation. The web dashboard provides a beautiful interface for visualizing the data in real-time.

Step 3: Project Structure

Let’s set up our project with a clean, modular structure:

crypto-analytics/
├── main.py              # FastAPI application
├── config.py            # Configuration settings
├── models.py            # Data models
├── database.py          # QuestDB operations
├── websocket_client.py  # Coinbase WebSocket client
├── dashboard.html       # Frontend dashboard
└── requirements.txt     # Python dependencies

This modular approach makes the code maintainable and easy to extend.

Step 4: Setting Up the Database Models

First, let’s define our data models. Create a models.py file:

from dataclasses import dataclass
from datetime import datetime

@dataclass
class Ticker:
    """Represents real-time price data"""
    symbol: str
    best_bid: float
    best_ask: float
    last_price: float
    volume_24h: float
    time: datetime

@dataclass
class Trade:
    """Represents individual trade executions"""
    symbol: str
    price: float
    size: float
    side: str
    time: datetime
    trade_id: int

We also define our QuestDB table schemas:

QUESTDB_SCHEMAS = {
    "coinbase_ticker": """
        CREATE TABLE IF NOT EXISTS coinbase_ticker (
            symbol SYMBOL,
            best_bid DOUBLE,
            best_ask DOUBLE,
            last_price DOUBLE,
            spread DOUBLE,
            volume_24h DOUBLE,
            timestamp TIMESTAMP
        ) timestamp(timestamp) PARTITION BY DAY;
    """,
    
    "coinbase_trades": """
        CREATE TABLE IF NOT EXISTS coinbase_trades (
            symbol SYMBOL,
            price DOUBLE,
            size DOUBLE,
            side SYMBOL,
            trade_id LONG,
            timestamp TIMESTAMP
        ) timestamp(timestamp) PARTITION BY DAY;
    """
}

Notice the PARTITION BY DAY clause. This is QuestDB’s secret sauce for handling time-series data efficiently.

Step 5: Building the WebSocket Client

The heart of our system is the WebSocket client that connects to Coinbase’s real-time feed:

class CoinbaseWebSocketClient:
    def __init__(self, on_ticker, on_trade):
        self.on_ticker = on_ticker
        self.on_trade = on_trade
        self.running = False
        
    async def connect_and_subscribe(self):
        subscribe_message = {
            "type": "subscribe",
            "product_ids": ["BTC-USD", "ETH-USD", "SOL-USD"],
            "channels": ["ticker", "matches"]
        }
        
        async with websockets.connect(COINBASE_WS_URL) as websocket:
            await websocket.send(json.dumps(subscribe_message))
            
            async for message in websocket:
                await self._process_message(json.loads(message))

The client handles automatic reconnection and processes two types of messages: ticker updates (price quotes) and matches (executed trades).

Step 6: Database Operations

Now let’s create the database client that handles all QuestDB operations:

class QuestDBClient:
    def __init__(self):
        self.conn = psycopg2.connect(
            host="localhost",
            port=8812,
            user="admin",
            password="quest",
            database="qdb"
        )
        self.cursor = self.conn.cursor()
    
    def insert_ticker(self, ticker):
        """Insert real-time ticker data"""
        query = """
            INSERT INTO coinbase_ticker 
            (symbol, best_bid, best_ask, last_price, spread, volume_24h, timestamp) 
            VALUES (%s, %s, %s, %s, %s, %s, %s)
        """
        spread = ticker.best_ask - ticker.best_bid
        self.cursor.execute(query, (
            ticker.symbol, ticker.best_bid, ticker.best_ask, 
            ticker.last_price, spread, ticker.volume_24h, ticker.time
        ))
        self.conn.commit()

Step 7: Generating 1-Minute Candles

One of the most powerful features is automatic candle generation. QuestDB makes this trivial with its time-series functions:

def generate_candles(self):
    """Generate 1-minute OHLCV candles from ticker data"""
    query = """
        INSERT INTO coinbase_candles (symbol, open, high, low, close, volume, timestamp)
        SELECT 
            symbol,
            first(last_price) as open,
            max(last_price) as high,
            min(last_price) as low,
            last(last_price) as close,
            avg(volume_24h) as volume,
            date_trunc('minute', timestamp) as timestamp
        FROM coinbase_ticker
        WHERE timestamp >= dateadd('h', -2, now())
        GROUP BY symbol, date_trunc('minute', timestamp)
    """
    self.cursor.execute(query)
    self.conn.commit()

This single query aggregates tick data into proper OHLCV candles. The date_trunc function groups data by minute, while first and last give us the open and close prices.

Step 8: Building the FastAPI Application

Let’s tie everything together with FastAPI:

from fastapi import FastAPI, WebSocket
from contextlib import asynccontextmanager

@asynccontextmanager
async def lifespan(app: FastAPI):
    # Startup
    db_client.connect()
    db_client.create_tables()
    
    # Start WebSocket client
    ws_task = asyncio.create_task(ws_client.start())
    
    yield
    
    # Shutdown
    ws_client.stop()
    db_client.close()

app = FastAPI(lifespan=lifespan)

@app.get("/api/market-stats")
async def get_market_stats():
    """Get comprehensive market statistics"""
    return db_client.get_market_stats()

@app.websocket("/ws")
async def websocket_endpoint(websocket: WebSocket):
    """WebSocket endpoint for dashboard updates"""
    await websocket.accept()
    # Handle real-time updates

Step 9: Creating the Dashboard

The dashboard uses DaisyUI for a professional dark theme and Chart.js for visualizations. Here’s a snippet of the real-time price update logic:

function updatePriceCard(data) {
    const priceElement = document.getElementById(`price-${data.symbol}`);
    const oldPrice = parseFloat(priceElement.textContent);
    
    priceElement.textContent = `$${data.price.toFixed(2)}`;
    
    // Add color animation for price changes
    if (oldPrice < data.price) {
        priceElement.classList.add('price-up');
    } else if (oldPrice > data.price) {
        priceElement.classList.add('price-down');
    }
}

Step 10: Running the System

To run the complete system:

# 1. Install dependencies
pip install -r requirements.txt

# 2. Start the application
python main.py

# 3. Open your browser
# Navigate to http://localhost:8000

You’ll see the dashboard spring to life with real-time cryptocurrency data flowing in from Coinbase.

Querying Your Data

Once data is flowing, you can run powerful queries in QuestDB’s web console:

Get the latest prices:

SELECT symbol, last(last_price) as current_price
FROM coinbase_ticker
WHERE timestamp > dateadd('m', -5, now())
GROUP BY symbol;

Calculate price changes:

WITH current_prices AS (
    SELECT symbol, last(last_price) as price_now
    FROM coinbase_ticker
    WHERE timestamp > dateadd('m', -5, now())
    GROUP BY symbol
),
hour_ago AS (
    SELECT symbol, first(last_price) as price_1h_ago
    FROM coinbase_ticker
    WHERE timestamp BETWEEN dateadd('h', -1, now()) - '5m' 
        AND dateadd('h', -1, now())
    GROUP BY symbol
)
SELECT 
    c.symbol,
    c.price_now,
    ((c.price_now - h.price_1h_ago) / h.price_1h_ago * 100) as change_pct
FROM current_prices c
JOIN hour_ago h ON c.symbol = h.symbol;

Getting 1 minute Candle Data of BTCUSD – realtime aggregated data

-- Generate 1-minute candles from ticker data
SELECT 
  date_trunc('minute', timestamp) as time,
  first(last_price) as open,
  max(last_price) as high,
  min(last_price) as low,
  last(last_price) as close,
  count(*) as tick_count,
  avg(volume_24h) as volume
FROM coinbase_ticker
WHERE symbol = 'BTC-USD'
  AND timestamp > dateadd('h', -2, now())  -- Last 2 hours
GROUP BY time
ORDER BY time DESC;

Performance and Scalability

The system is designed to handle high-frequency data:

• Ingestion Rate: 1000+ messages per second
• Storage Efficiency: ~1GB per week of tick data
• Query Performance: Sub-millisecond for recent data
• Memory Usage: ~100MB for the Python application

QuestDB’s columnar storage and automatic partitioning ensure queries remain fast even with millions of rows.

Common Pitfalls and Solutions

1. Timezone Issues QuestDB stores all timestamps in UTC. Always convert timezone-aware datetime objects to naive UTC:

timestamp = datetime_obj.replace(tzinfo=None)

2. Connection Handling Always use connection pooling for production systems. The example uses a single connection for simplicity.

3. Memory Management For very high-frequency data, consider batching inserts:

execute_values(cursor, query, batch_data)

Extending the System

This foundation opens up numerous possibilities:

• Technical Indicators: Calculate moving averages, RSI, and MACD directly in SQL
• Multi-Exchange Support: Add Binance, Kraken, or other exchanges
• Alerts: Trigger notifications based on price movements
• Machine Learning: Export data for training prediction models

Conclusion

We’ve built a complete real-time cryptocurrency analytics system with just a few hundred lines of Python and some SQL. QuestDB’s performance and simplicity make it an excellent choice for financial time-series data.

The combination of QuestDB’s speed, PostgreSQL compatibility, and built-in time-series functions creates a powerful platform for financial analytics. No complex setup, no PostgreSQL installation required—just download, extract, and start building.

The complete source code is available on GitHub

Happy coding, and may your candles always close green!