SQLite

SQLite is a C library that provides a lightweight, disk-based database. It doesn’t require a separate server process and allows access to the database using a nonstandard variant of the SQL query language. SQLite is a popular choice for local/client storage in application software such as web browsers and mobile apps.

Key characteristics of SQLite:

• Serverless: SQLite doesn’t require a separate server process or system.
• Zero Configuration: No setup or administration needed.
• Cross-Platform: Accessible on various systems and languages.
• Self-Contained: A single ordinary disk file holds the complete database.

SQLite is often used for:

• Local data storage for mobile apps.
• Stand-in for a traditional database during prototyping and development.
• Internal or temporary databases.
• Small to medium-sized websites.

SQLite with Python

Installation

Python’s standard library includes SQLite3, which means you don’t need to install anything additional to start using SQLite.

Basic Usage

import sqlite3

# Connect to SQLite Database
conn = sqlite3.connect('example.db')

# Create a Cursor Object
c = conn.cursor()

# Create Table
c.execute('''CREATE TABLE IF NOT EXISTS stocks
             (date text, trans text, symbol text, qty real, price real)''')

# Insert a Row of Data
c.execute("INSERT INTO stocks VALUES ('2024-01-23','BUY','RHAT',100,35.14)")

# Save (commit) the changes
conn.commit()

# Query Data
for row in c.execute('SELECT * FROM stocks ORDER BY price'):
    print(row)

# Close the Connection
conn.close()

SQLite with Node.js

Installation

Node.js doesn’t include SQLite in its standard library, so you need to install it using npm.

npm install sqlite3

Basic Usage

const sqlite3 = require('sqlite3').verbose();

// Connect to SQLite Database
let db = new sqlite3.Database('./example.db', (err) => {
  if (err) {
    console.error(err.message);
  }
  console.log('Connected to the SQLite database.');
});

// Create Table
db.run('CREATE TABLE IF NOT EXISTS stocks (date text, trans text, symbol text, qty real, price real)');

// Insert a Row of Data
db.run("INSERT INTO stocks VALUES ('2024-01-23','BUY','RHAT',100,35.14)");

// Query Data
db.all("SELECT * FROM stocks", [], (err, rows) => {
  if (err) {
    throw err;
  }
  rows.forEach((row) => {
    console.log(row);
  });
});

// Close the Database
db.close((err) => {
  if (err) {
    console.error(err.message);
  }
  console.log('Close the database connection.');
});

SQLite is an excellent choice for applications needing the flexibility of a relational database without the overhead of a full-scale database management system. It’s widely appreciated for its simplicity, reliability, and ease of integration.