Simple Ziwei‑Doushu Astrology Engine API with Express & SQLite

Phase 1: Scaffold Project

Create a new folder for the project and initialise it with npm. This gives us a package.json to track dependencies and scripts.

Install Express for the web server and better‑sqlite3 for fast, native SQLite access. Both are lightweight and perfect for a minimal clone.

setup.sh

#!/usr/bin/env bash
set -e
# Create project directory
mkdir -p ziwei-clone && cd ziwei-clone
# Initialise npm (accept defaults)
npm init -y
# Install runtime dependencies
npm install express better-sqlite3
# Enable ES module support
npm pkg set type=module
# Add a start script for convenience
npm pkg set scripts.start="node index.js"
echo "Setup complete. Run 'npm start' after creating index.js."

Phase 2: Define Data Model

We’ll create a tiny SQLite database that stores placeholder positions for three celestial bodies: Sun, Moon, and Mercury. This mirrors the real engine’s massive tables but keeps things simple for learning purposes.

The script uses better‑sqlite3 to create the file, define a bodies table, and seed it with static coordinates. Running it once prepares the data layer for the API.

init_db.js

import Database from 'better-sqlite3';
const db = new Database('charts.db');
// Create table if it doesn't exist
db.exec(`
CREATE TABLE IF NOT EXISTS bodies (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  longitude REAL NOT NULL,
  latitude REAL NOT NULL
);
`);
// Insert placeholder positions (degrees)
const insert = db.prepare('INSERT INTO bodies (name, longitude, latitude) VALUES (?, ?, ?)');
const bodies = [
  ['Sun', 120.0, 0.0],
  ['Moon', 45.0, 0.0],
  ['Mercury', 80.0, 0.0]
];
for (const b of bodies) insert.run(...b);
console.log('Database initialized with placeholder bodies.');

Phase 3: Build API Endpoint

In this step we expose a simple HTTP GET endpoint that will serve the static planetary positions we stored earlier. The route /chart?date=YYYY-MM-DD reads the optional date query (we ignore it for now) and returns the rows from the SQLite bodies table as JSON. Keeping the logic tiny makes it easy to swap in real calculations later.

We use Express' json middleware so the response is automatically serialized, and we wrap the DB query in a promise for clean async/await handling. This file becomes the heart of our clone: a minimal but functional API.

src/index.mjs

import express from 'express';
import Database from 'better-sqlite3';

const app = express();
const db = new Database('charts.db', { verbose: console.log });

app.use(express.json());

// GET /chart?date=YYYY-MM-DD -> returns static bodies as JSON
app.get('/chart', async (req, res) => {
  try {
    const rows = db.prepare('SELECT name, position FROM bodies').all();
    // In a full engine we'd compute positions based on req.query.date
    res.json({ date: req.query.date || null, bodies: rows });
  } catch (err) {
    console.error('DB error:', err);
    res.status(500).json({ error: 'Internal Server Error' });
  }
});

const PORT = process.env.PORT || 3000;
app.listen(PORT, () => {
  console.log(`🚀 Server listening on http://localhost:${PORT}`);
});

Phase 4: Run Locally

Now that the server code is ready, we need to make sure the SQLite database exists and then start the application. The npm install step from earlier already pulled in express and better-sqlite3, so we only have to launch the script with Node. If the DB file is missing, SQLite will create an empty one, but our earlier seed script (Phase 2) already populated the bodies table.

Running the server locally lets you interact with the API via curl or a browser, confirming that the endpoint is reachable and returns the expected JSON structure.

# Ensure dependencies are installed (run once)
npm install

# Start the API server
node src/index.mjs

Phase 5: Verify & Extend

With the server running, we can quickly verify the endpoint using curl or by opening the URL in a browser. The response should be a JSON object containing the three static bodies we inserted earlier. This confirms that our Express route, SQLite query, and JSON serialization are all wired together correctly.

From here you can start extending the clone: add more celestial bodies, store actual positions per date, or plug in a lunar‑calendar library to compute real Ziwei‑Doushu charts. The API design already supports a date query parameter, so future calculations can simply replace the static SELECT with a date‑aware query.

# Test the endpoint (should return Sun, Moon, Mars entries)
curl -s http://localhost:3000/chart | grep -i "Sun"

# Or open in a browser:
# http://localhost:3000/chart?date=2024-01-01