Complete CRUD API with Express + SQLite

1. Setup

mkdir my-api && cd my-api
npm init -y
npm install express cors helmet better-sqlite3

2. Main Server

const express = require('express');
const cors = require('cors');
const helmet = require('helmet');
const Database = require('better-sqlite3');

const app = express();
const db = new Database('app.db');

// Middleware
app.use(helmet());
app.use(cors());
app.use(express.json());

// Create table
db.exec(`CREATE TABLE IF NOT EXISTS articles (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL,
  content TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
)`);

3. API Routes

// GET all articles
app.get('/api/articles', (req, res) => {
  const articles = db.prepare('SELECT * FROM articles ORDER BY created_at DESC').all();
  res.json(articles);
});

// GET single article
app.get('/api/articles/:id', (req, res) => {
  const article = db.prepare('SELECT * FROM articles WHERE id = ?').get(req.params.id);
  if (!article) return res.status(404).json({ error: 'Article not found' });
  res.json(article);
});

// POST create article
app.post('/api/articles', (req, res) => {
  const { title, content } = req.body;
  if (!title) return res.status(400).json({ error: 'Title is required' });

  const result = db.prepare('INSERT INTO articles (title, content) VALUES (?, ?)').run(title, content);
  res.status(201).json({ id: result.lastInsertRowid, title, content });
});

// PUT update article
app.put('/api/articles/:id', (req, res) => {
  const { title, content } = req.body;
  const result = db.prepare('UPDATE articles SET title = ?, content = ? WHERE id = ?').run(title, content, req.params.id);
  if (result.changes === 0) return res.status(404).json({ error: 'Article not found' });
  res.json({ message: 'Updated successfully' });
});

// DELETE article
app.delete('/api/articles/:id', (req, res) => {
  const result = db.prepare('DELETE FROM articles WHERE id = ?').run(req.params.id);
  if (result.changes === 0) return res.status(404).json({ error: 'Article not found' });
  res.json({ message: 'Deleted successfully' });
});

4. Start Server

const PORT = process.env.PORT || 3000;
app.listen(PORT, () => {
  console.log(`API server running on port ${PORT}`);
});

5. Test

curl http://localhost:3000/api/articles
curl -X POST -H "Content-Type: application/json" -d '{"title":"Hello","content":"World"}' http://localhost:3000/api/articles

Features