SQLite with Node.js on Ubuntu 24 is a lightweight yet powerful combination for building data-driven applications without the overhead of a full database server. This setup is particularly valuable for development environments, small to medium applications, and scenarios where you need a serverless database solution that performs exceptionally well. You’ll learn how to install, configure, and optimize SQLite with Node.js on Ubuntu 24, including practical examples, performance optimization techniques, and solutions to common issues that developers encounter when working with this stack.

Why SQLite with Node.js Makes Sense

SQLite offers a unique advantage as a self-contained, serverless database engine that requires zero configuration. Unlike PostgreSQL or MySQL, SQLite doesn’t need a separate server process, making it perfect for applications where simplicity and performance matter more than concurrent write operations. When paired with Node.js on Ubuntu 24, you get a modern JavaScript runtime with excellent package management and a rock-solid Linux distribution that provides long-term support until 2029.

The combination excels in scenarios like content management systems, IoT data logging, development prototypes, desktop applications with Electron, and any application where you need structured data storage without the complexity of traditional database servers. Many successful applications including Firefox, Skype, and WhatsApp use SQLite for local data storage.

Installing SQLite and Node.js on Ubuntu 24

Ubuntu 24.04 LTS comes with updated repositories that make installation straightforward. Start by updating your system and installing the necessary components:

sudo apt update && sudo apt upgrade -y
sudo apt install sqlite3 libsqlite3-dev nodejs npm build-essential -y

Verify your installations:

sqlite3 --version
node --version
npm --version

For Node.js projects, you’ll primarily use the sqlite3 or better-sqlite3 npm packages. The better-sqlite3 package generally offers superior performance for synchronous operations:

mkdir sqlite-node-project && cd sqlite-node-project
npm init -y
npm install better-sqlite3
# Alternative: npm install sqlite3

Basic SQLite Integration with Node.js

Here’s a practical example that demonstrates database creation, table setup, and basic CRUD operations:

const Database = require('better-sqlite3');
const path = require('path');

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

// Create table
const createTable = `
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE NOT NULL,
    email TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )
`;

db.exec(createTable);

// Prepared statements for better performance
const insertUser = db.prepare('INSERT INTO users (username, email) VALUES (?, ?)');
const getUserById = db.prepare('SELECT * FROM users WHERE id = ?');
const getAllUsers = db.prepare('SELECT * FROM users ORDER BY created_at DESC');

// Insert sample data
try {
  const insert = db.transaction((users) => {
    for (const user of users) {
      insertUser.run(user.username, user.email);
    }
  });
  
  insert([
    { username: 'john_doe', email: 'john@example.com' },
    { username: 'jane_smith', email: 'jane@example.com' }
  ]);
  
  console.log('Users inserted successfully');
} catch (error) {
  console.error('Insert error:', error.message);
}

// Query data
const users = getAllUsers.all();
console.log('All users:', users);

// Close database connection
process.on('exit', () => db.close());
process.on('SIGHUP', () => process.exit(128 + 1));
process.on('SIGINT', () => process.exit(128 + 2));
process.on('SIGTERM', () => process.exit(128 + 15));

Performance Optimization and Best Practices

SQLite performance can be significantly improved through proper configuration and coding practices. Here are essential optimizations for production use:

const Database = require('better-sqlite3');

const db = new Database('optimized.db', {
  verbose: console.log // Remove in production
});

// Performance optimizations
db.exec(`
  PRAGMA journal_mode = WAL;
  PRAGMA synchronous = NORMAL;
  PRAGMA cache_size = 1000000;
  PRAGMA foreign_keys = true;
  PRAGMA temp_store = memory;
`);

// Use transactions for bulk operations
const bulkInsert = db.transaction((items) => {
  const insert = db.prepare('INSERT INTO items (name, value) VALUES (?, ?)');
  for (const item of items) {
    insert.run(item.name, item.value);
  }
});

// Performance comparison
const startTime = Date.now();
bulkInsert(generateTestData(10000)); // Your data generation function
const endTime = Date.now();
console.log(`Inserted 10,000 records in ${endTime - startTime}ms`);

Configuration	10,000 Inserts (ms)	10,000 Selects (ms)	Use Case
Default settings	2,500	45	Development
WAL mode + transactions	180	35	Production
Memory temp store + cache	160	25	High performance

Real-World Application Examples

Here’s a practical web API example using Express.js with SQLite for a simple blog system:

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

app.use(express.json());

const db = new Database('blog.db');

// Initialize schema
db.exec(`
  CREATE TABLE IF NOT EXISTS posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    author TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
  );
  
  CREATE INDEX IF NOT EXISTS idx_posts_author ON posts(author);
  CREATE INDEX IF NOT EXISTS idx_posts_created ON posts(created_at);
`);

// Prepared statements
const createPost = db.prepare(`
  INSERT INTO posts (title, content, author) 
  VALUES (?, ?, ?)
`);

const getPosts = db.prepare(`
  SELECT id, title, content, author, created_at 
  FROM posts 
  ORDER BY created_at DESC 
  LIMIT ? OFFSET ?
`);

const getPostById = db.prepare('SELECT * FROM posts WHERE id = ?');

// API endpoints
app.post('/api/posts', (req, res) => {
  try {
    const { title, content, author } = req.body;
    const result = createPost.run(title, content, author);
    res.status(201).json({ 
      id: result.lastInsertRowid, 
      message: 'Post created successfully' 
    });
  } catch (error) {
    res.status(400).json({ error: error.message });
  }
});

app.get('/api/posts', (req, res) => {
  const page = parseInt(req.query.page) || 1;
  const limit = parseInt(req.query.limit) || 10;
  const offset = (page - 1) * limit;
  
  try {
    const posts = getPosts.all(limit, offset);
    res.json(posts);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

app.get('/api/posts/:id', (req, res) => {
  try {
    const post = getPostById.get(req.params.id);
    if (!post) {
      return res.status(404).json({ error: 'Post not found' });
    }
    res.json(post);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

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

SQLite vs Alternative Databases

Feature	SQLite	PostgreSQL	MySQL	MongoDB
Setup Complexity	Zero config	Moderate	Moderate	Low
Concurrent Writes	Single writer	Excellent	Good	Excellent
Read Performance	Excellent	Very Good	Good	Good
File Size	~1MB library	~200MB install	~180MB install	~300MB install
Memory Usage	Very Low	High	Moderate	High

Common Issues and Troubleshooting

Several issues frequently occur when working with SQLite and Node.js. Here are the most common problems and their solutions:

• Database locked errors: Usually caused by not closing connections properly or long-running transactions
• Performance degradation: Often resolved by enabling WAL mode and using prepared statements
• Node-gyp compilation failures: Typically require build-essential and python3 packages
• Concurrent access issues: SQLite handles multiple readers but only one writer at a time

// Database connection pool for better resource management
class SQLitePool {
  constructor(filename, options = {}) {
    this.filename = filename;
    this.options = options;
    this.connections = new Map();
    this.maxConnections = options.maxConnections || 5;
  }
  
  getConnection(threadId = 'main') {
    if (!this.connections.has(threadId)) {
      if (this.connections.size >= this.maxConnections) {
        throw new Error('Maximum connections reached');
      }
      
      const db = new Database(this.filename, this.options);
      db.exec('PRAGMA journal_mode = WAL;');
      this.connections.set(threadId, db);
    }
    
    return this.connections.get(threadId);
  }
  
  closeAll() {
    for (const [threadId, db] of this.connections) {
      db.close();
      this.connections.delete(threadId);
    }
  }
}

// Usage example
const pool = new SQLitePool('app.db');
const db = pool.getConnection();

// Graceful shutdown
process.on('SIGINT', () => {
  console.log('Closing database connections...');
  pool.closeAll();
  process.exit(0);
});

Advanced Features and Integration

SQLite supports advanced features that make it suitable for complex applications. Full-text search, JSON operations, and custom functions can be implemented:

// Full-text search implementation
db.exec(`
  CREATE VIRTUAL TABLE IF NOT EXISTS search_posts 
  USING fts5(title, content, author);
`);

// Custom function for text processing
db.function('highlight_search', (text, term) => {
  return text.replace(new RegExp(term, 'gi'), `$&`);
});

// JSON data handling (SQLite 3.38+)
const createJsonTable = `
  CREATE TABLE IF NOT EXISTS user_preferences (
    user_id INTEGER PRIMARY KEY,
    settings JSON,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )
`;

db.exec(createJsonTable);

const insertPreferences = db.prepare(`
  INSERT INTO user_preferences (user_id, settings) 
  VALUES (?, json(?))
`);

// Store complex preferences
insertPreferences.run(1, JSON.stringify({
  theme: 'dark',
  notifications: { email: true, push: false },
  privacy: { profileVisible: true, shareData: false }
}));

// Query JSON data
const getPreference = db.prepare(`
  SELECT json_extract(settings, '$.theme') as theme
  FROM user_preferences 
  WHERE user_id = ?
`);

console.log(getPreference.get(1)); // { theme: 'dark' }

For production deployments, consider using VPS hosting that provides the flexibility to configure your Node.js and SQLite environment exactly as needed. If you’re handling larger datasets or expecting high concurrent loads, dedicated servers offer the resources needed for optimal SQLite performance with Node.js applications.

Additional resources for deep-diving into SQLite optimization include the official SQLite documentation and the Node.js API documentation. The better-sqlite3 GitHub repository contains extensive examples and performance benchmarks that can help optimize your specific use case.

---

---

This article incorporates information and material from various online sources. We acknowledge and appreciate the work of all original authors, publishers, and websites. While every effort has been made to appropriately credit the source material, any unintentional oversight or omission does not constitute a copyright infringement. All trademarks, logos, and images mentioned are the property of their respective owners. If you believe that any content used in this article infringes upon your copyright, please contact us immediately for review and prompt action.

This article is intended for informational and educational purposes only and does not infringe on the rights of the copyright owners. If any copyrighted material has been used without proper credit or in violation of copyright laws, it is unintentional and we will rectify it promptly upon notification. Please note that the republishing, redistribution, or reproduction of part or all of the contents in any form is prohibited without express written permission from the author and website owner. For permissions or further inquiries, please contact us.