Node.js RESTful API with SQLite

A comprehensive RESTful API built with Node.js, Express, and SQLite. This project implements account and media management with a service layer architecture, featuring HTTP-only testing with Mocha and performance reporting with mochawesome.

Project Overview

This project is a Node.js REST API that manages accounts and their associated media records. Each account can have multiple media entries linked to it. The API follows best practices with:

• Service Layer Architecture: Separation of business logic from route handlers
• HTTP-Only Testing: Complete end-to-end testing via HTTP requests (no direct database testing)
• Performance Monitoring: Built-in performance metrics and reporting
• Turkish Localization: Test descriptions and error messages in Turkish
• Professional Reporting: HTML test reports with mochawesome

Project Structure

├── app.js                  # Main Express application
├── db.js                   # SQLite database configuration
├── package.json            # Dependencies and scripts
├── routes/
│   ├── account.js          # Account endpoints
│   └── media.js            # Media endpoints  
├── services/
│   ├── accountService.js   # Account business logic
│   └── mediaService.js     # Media business logic
├── test/
│   └── api.test.js         # HTTP-based test suite
├── mochawesome-report/     # HTML test reports
└── clear-tables.js         # Database utility script

API Endpoints

Account Management

• GET /accounts - Retrieve all accounts
• GET /accounts/:id - Get a specific account by ID
• GET /accounts/:id/with_media - Get account with associated media
• POST /accounts - Create a new account

Media Management

• GET /media - Retrieve all media records
• GET /media/:id - Get a specific media record by ID
• POST /media - Create a new media record linked to an account

Core Implementation

app.js

const express = require('express');
const accountRoutes = require('./routes/account');
const mediaRoutes = require('./routes/media');
const app = express();

app.use(express.json());
app.use('/accounts', accountRoutes);
app.use('/media', mediaRoutes);

module.exports = app;

if (require.main === module) {
  app.listen(3000, () => console.log('API running on port 3000'));
}

routes/account.js (Service Layer Integration)

const express = require('express');
const accountService = require('../services/accountService');
const router = express.Router();

// GET /accounts/:id/with_media - returns account with associated media
router.get('/:id/with_media', async (req, res) => {
  try {
    const accountId = Number(req.params.id);
    const account = await accountService.getAccountById(accountId);
    const db = require('../db');
    db.all('SELECT * FROM media WHERE account_id = ?', [accountId], (err, media) => {
      if (err) {
        return res.status(500).json({ error: 'Error retrieving media data' });
      }
      res.json({ ...account, media: media || [] });
    });
  } catch (err) {
    res.status(err.httpStatus || 500).json({ error: err.message || 'Internal server error' });
  }
});

// POST /accounts - create a new account
router.post('/', async (req, res) => {
  const { balance, phone_number } = req.body;
  try {
    const result = await accountService.createAccount(balance, phone_number);
    res.status(201).json(result);
  } catch (err) {
    res.status(err.httpStatus || 500).json({ error: err.message || 'Internal server error' });
  }
});

// GET /accounts - get all accounts
router.get('/', async (req, res) => {
  try {
    const result = await accountService.getAllAccounts();
    res.json(result);
  } catch (err) {
    res.status(err.httpStatus || 500).json({ error: err.message || 'Internal server error' });
  }
});

module.exports = router;

Database Schema (db.js)

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database('./database.sqlite');

db.serialize(() => {
  db.run(`CREATE TABLE IF NOT EXISTS account (
    account_id INTEGER PRIMARY KEY AUTOINCREMENT,
    balance REAL,
    phone_number TEXT
  )`);
  db.run(`CREATE TABLE IF NOT EXISTS media (
    media_id INTEGER PRIMARY KEY AUTOINCREMENT,
    account_id INTEGER,
    create_date TEXT,
    expiery_date TEXT,
    FOREIGN KEY (account_id) REFERENCES account(account_id)
  )`);
});

module.exports = db;

Installation & Setup

1. Clone the repository:

   git clone <repository-url>
   cd new_app_with_api

2. Install dependencies:

   npm install

3. Start the API server:

   node app.js

   The API will be available at http://localhost:3000

Testing

Running Tests

# Run tests with standard output
npm test

# Run tests with HTML report generation
npm run test-html

Test Features

• HTTP-Only Testing: All tests use actual HTTP requests, no direct database access
• Performance Monitoring: Built-in timing and memory usage tracking
• Turkish Localization: Test descriptions in Turkish for local development
• mochawesome Reports: Professional HTML test reports with performance metrics

Viewing Test Reports

After running npm run test-html, open mochawesome-report/mochawesome.html in your browser to view detailed test results with performance data visualization.

Database Management

Clear Database Tables

node clear-tables.js

This utility script will clear all data from the account and media tables while preserving the schema.

Development Features

• Service Layer Pattern: Business logic separated from HTTP handling
• Async/Await: Modern asynchronous JavaScript patterns
• Error Handling: Comprehensive error responses with appropriate HTTP status codes
• Performance Metrics: Real-time monitoring of response times and system resources
• Professional Testing: Complete test coverage with visual reporting

Technology Stack

• Backend: Node.js + Express.js
• Database: SQLite3 with auto-schema creation
• Testing: Mocha + Chai + chai-http + Supertest
• Reporting: mochawesome for HTML test reports
• Architecture: Service layer pattern with route separation