SQLite Installation

SQLite is a lightweight, file-based database that requires no separate server process. It’s perfect for mobile applications, prototypes, and embedded systems. This guide covers installing the Countries States Cities database in SQLite.

SQLite installation is the fastest option, typically taking less than 1 minute with approximately 50MB file size.

Prerequisites

Before starting, ensure you have:

SQLite 3.25+ installed (SQLite 3.35+ recommended)
At least 100MB free disk space
Downloaded the sqlite/world.sql file from our GitHub repository

SQLite databases are self-contained files that don’t require a separate server installation, making them ideal for development and embedded applications.

Method 1: SQLite Command Line

Create Database File

sqlite3 world.db

This creates a new SQLite database file named world.db in your current directory.

Import SQL File

.read sqlite/world.sql

SQLite import is typically the fastest due to its lightweight nature and single-file architecture.

Verify Installation

-- List all tables
.tables

-- Check table schema
.schema countries

-- Count records in each table
SELECT 'countries' as table_name, COUNT(*) as count FROM countries
UNION ALL
SELECT 'states', COUNT(*) FROM states  
UNION ALL
SELECT 'cities', COUNT(*) FROM cities;

Expected output:

table_name|count
countries|250
states|5038
cities|151024

Method 2: Using Programming Languages

Python Implementation

Install Dependencies

# SQLite3 is included in Python standard library
# No additional installation needed

Create Installation Script

import sqlite3
import os

def install_world_database():
    # Connect to database (creates file if doesn't exist)
    conn = sqlite3.connect('world.db')
    cursor = conn.cursor()
    
    # Read and execute SQL file
    try:
        with open('sqlite/world.sql', 'r', encoding='utf-8') as f:
            sql_script = f.read()
            cursor.executescript(sql_script)
            
        print("Database installation completed successfully!")
        
        # Verify installation
        cursor.execute("SELECT COUNT(*) FROM countries")
        country_count = cursor.fetchone()[0]
        
        cursor.execute("SELECT COUNT(*) FROM cities") 
        city_count = cursor.fetchone()[0]
        
        print(f"Installed {country_count} countries and {city_count} cities")
        
    except FileNotFoundError:
        print("Error: sqlite/world.sql file not found")
    except Exception as e:
        print(f"Error during installation: {e}")
    finally:
        conn.close()

if __name__ == "__main__":
    install_world_database()

Run Installation

python install_world_db.py

Node.js Implementation

Install Dependencies

npm install sqlite3

Create Installation Script

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

function installWorldDatabase() {
    // Open database
    const db = new sqlite3.Database('world.db');
    
    // Read SQL file
    const sqlPath = path.join(__dirname, 'sqlite', 'world.sql');
    
    try {
        const sql = fs.readFileSync(sqlPath, 'utf8');
        
        // Execute SQL
        db.exec(sql, (err) => {
            if (err) {
                console.error('Installation failed:', err);
                return;
            }
            
            console.log('Database installation completed successfully!');
            
            // Verify installation
            db.get("SELECT COUNT(*) as count FROM countries", (err, row) => {
                if (!err) {
                    console.log(`Installed ${row.count} countries`);
                }
            });
            
            db.get("SELECT COUNT(*) as count FROM cities", (err, row) => {
                if (!err) {
                    console.log(`Installed ${row.count} cities`);
                }
                db.close();
            });
        });
        
    } catch (err) {
        console.error('Error reading SQL file:', err.message);
    }
}

installWorldDatabase();

Run Installation

node install_world_db.js

Database Structure Verification

-- List all tables
.tables

-- Show table schemas
.schema countries
.schema states
.schema cities

-- Get table info
PRAGMA table_info(countries);
PRAGMA table_info(states);  
PRAGMA table_info(cities);

Performance Optimization

Create Indexes

-- Essential indexes for query performance
CREATE INDEX IF NOT EXISTS idx_countries_iso2 ON countries(iso2);
CREATE INDEX IF NOT EXISTS idx_countries_iso3 ON countries(iso3);
CREATE INDEX IF NOT EXISTS idx_countries_region ON countries(region);

CREATE INDEX IF NOT EXISTS idx_states_country_id ON states(country_id);
CREATE INDEX IF NOT EXISTS idx_states_name ON states(name);
CREATE INDEX IF NOT EXISTS idx_states_country_name ON states(country_name);

CREATE INDEX IF NOT EXISTS idx_cities_country_id ON cities(country_id);
CREATE INDEX IF NOT EXISTS idx_cities_state_id ON cities(state_id);
CREATE INDEX IF NOT EXISTS idx_cities_name ON cities(name);
CREATE INDEX IF NOT EXISTS idx_cities_coordinates ON cities(latitude, longitude);
CREATE INDEX IF NOT EXISTS idx_cities_country_name ON cities(country_name);

SQLite-Specific Optimizations

-- Optimize SQLite for better performance
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA cache_size = 10000;
PRAGMA temp_store = memory;
PRAGMA mmap_size = 268435456; -- 256MB

Connection Examples

Python
Node.js
C#

import sqlite3
from contextlib import contextmanager

class WorldDatabase:
    def __init__(self, db_path='world.db'):
        self.db_path = db_path
        
    @contextmanager
    def get_connection(self):
        conn = sqlite3.connect(self.db_path)
        conn.row_factory = sqlite3.Row  # Enable column access by name
        try:
            yield conn
        finally:
            conn.close()
    
    def get_countries(self, limit=None):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            query = "SELECT * FROM countries"
            if limit:
                query += f" LIMIT {limit}"
            return cursor.execute(query).fetchall()
    
    def get_cities_by_country(self, country_name, limit=None):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            query = """
                SELECT name, latitude, longitude, state_name 
                FROM cities 
                WHERE country_name = ?
            """
            if limit:
                query += f" LIMIT {limit}"
            return cursor.execute(query, (country_name,)).fetchall()
    
    def search_cities(self, search_term, limit=10):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            query = """
                SELECT name, country_name, state_name, latitude, longitude
                FROM cities 
                WHERE name LIKE ? 
                ORDER BY name 
                LIMIT ?
            """
            return cursor.execute(query, (f'%{search_term}%', limit)).fetchall()

# Usage example
db = WorldDatabase()

# Get all countries
countries = db.get_countries(limit=10)
for country in countries:
    print(f"{country['name']} ({country['iso2']})")

# Get cities in a specific country
us_cities = db.get_cities_by_country('United States', limit=5)
for city in us_cities:
    print(f"{city['name']}, {city['state_name']}")

# Search for cities
london_cities = db.search_cities('London')
for city in london_cities:
    print(f"{city['name']}, {city['country_name']}")

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

class WorldDatabase {
    constructor(dbPath = 'world.db') {
        this.db = new sqlite3.Database(dbPath);
    }
    
    getCountries(limit = null) {
        return new Promise((resolve, reject) => {
            let query = "SELECT * FROM countries";
            if (limit) query += ` LIMIT ${limit}`;
            
            this.db.all(query, (err, rows) => {
                if (err) reject(err);
                else resolve(rows);
            });
        });
    }
    
    getCitiesByCountry(countryName, limit = null) {
        return new Promise((resolve, reject) => {
            let query = `
                SELECT name, latitude, longitude, state_name 
                FROM cities 
                WHERE country_name = ?
            `;
            if (limit) query += ` LIMIT ${limit}`;
            
            this.db.all(query, [countryName], (err, rows) => {
                if (err) reject(err);
                else resolve(rows);
            });
        });
    }
    
    searchCities(searchTerm, limit = 10) {
        return new Promise((resolve, reject) => {
            const query = `
                SELECT name, country_name, state_name, latitude, longitude
                FROM cities 
                WHERE name LIKE ? 
                ORDER BY name 
                LIMIT ?
            `;
            
            this.db.all(query, [`%${searchTerm}%`, limit], (err, rows) => {
                if (err) reject(err);
                else resolve(rows);
            });
        });
    }
    
    close() {
        this.db.close();
    }
}

// Usage example
async function example() {
    const db = new WorldDatabase();
    
    try {
        // Get countries
        const countries = await db.getCountries(5);
        console.log('Countries:', countries);
        
        // Get US cities
        const usCities = await db.getCitiesByCountry('United States', 5);
        console.log('US Cities:', usCities);
        
        // Search for cities
        const londonCities = await db.searchCities('London');
        console.log('Cities named London:', londonCities);
        
    } catch (error) {
        console.error('Database error:', error);
    } finally {
        db.close();
    }
}

example();

using Microsoft.Data.Sqlite;
using System;
using System.Collections.Generic;
using System.Data;

public class WorldDatabase : IDisposable
{
    private readonly SqliteConnection _connection;
    
    public WorldDatabase(string dbPath = "world.db")
    {
        string connectionString = $"Data Source={dbPath}";
        _connection = new SqliteConnection(connectionString);
        _connection.Open();
    }
    
    public List<Country> GetCountries(int? limit = null)
    {
        var countries = new List<Country>();
        string query = "SELECT id, name, iso2, iso3, region FROM countries";
        if (limit.HasValue) query += $" LIMIT {limit}";
        
        using var command = new SqliteCommand(query, _connection);
        using var reader = command.ExecuteReader();
        
        while (reader.Read())
        {
            countries.Add(new Country
            {
                Id = reader.GetInt32("id"),
                Name = reader.GetString("name"),
                Iso2 = reader.GetString("iso2"),
                Iso3 = reader.GetString("iso3"),
                Region = reader.IsDBNull("region") ? null : reader.GetString("region")
            });
        }
        
        return countries;
    }
    
    public List<City> GetCitiesByCountry(string countryName, int? limit = null)
    {
        var cities = new List<City>();
        string query = @"
            SELECT name, latitude, longitude, state_name 
            FROM cities 
            WHERE country_name = @countryName";
        if (limit.HasValue) query += $" LIMIT {limit}";
        
        using var command = new SqliteCommand(query, _connection);
        command.Parameters.AddWithValue("@countryName", countryName);
        using var reader = command.ExecuteReader();
        
        while (reader.Read())
        {
            cities.Add(new City
            {
                Name = reader.GetString("name"),
                Latitude = reader.IsDBNull("latitude") ? null : reader.GetDouble("latitude"),
                Longitude = reader.IsDBNull("longitude") ? null : reader.GetDouble("longitude"),
                StateName = reader.IsDBNull("state_name") ? null : reader.GetString("state_name")
            });
        }
        
        return cities;
    }
    
    public void Dispose()
    {
        _connection?.Dispose();
    }
}

public class Country
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Iso2 { get; set; }
    public string Iso3 { get; set; }
    public string Region { get; set; }
}

public class City
{
    public string Name { get; set; }
    public double? Latitude { get; set; }
    public double? Longitude { get; set; }
    public string StateName { get; set; }
}

// Usage example
using var db = new WorldDatabase();

var countries = db.GetCountries(5);
foreach (var country in countries)
{
    Console.WriteLine($"{country.Name} ({country.Iso2})");
}

var usCities = db.GetCitiesByCountry("United States", 5);
foreach (var city in usCities)
{
    Console.WriteLine($"{city.Name}, {city.StateName}");
}

Advanced Features

Full-Text Search

-- Create FTS virtual table for cities
CREATE VIRTUAL TABLE cities_fts USING fts5(
    name, 
    country_name, 
    state_name, 
    content='cities'
);

-- Populate FTS table
INSERT INTO cities_fts SELECT name, country_name, state_name FROM cities;

JSON Support

-- SQLite 3.38+ supports JSON functions
SELECT 
    name,
    json_object(
        'country', country_name,
        'coordinates', json_array(latitude, longitude),
        'timezone', timezone
    ) as city_json
FROM cities 
WHERE country_name = 'Japan'
LIMIT 5;

Troubleshooting

Database Lock Error

Problem: database is locked errorSolutions:

Close all connections to the database
Check for zombie processes: fuser world.db
Remove WAL files: rm world.db-wal world.db-shm
Use connection timeout: sqlite3 -timeout 10000 world.db

Corrupted Database

Problem: database disk image is malformedSolutions:

Run integrity check: .integrity_check
Try to repair: .recover world_recovered.db
Restore from backup if available
Re-import from original SQL file

Performance Issues

Problem: Queries are slowSolutions:

Create appropriate indexes (see Performance Optimization)
Enable WAL mode: PRAGMA journal_mode = WAL;
Increase cache size: PRAGMA cache_size = 10000;
Use prepared statements
Consider using FTS for text searches

Memory Usage

Problem: High memory consumptionSolutions:

Reduce cache size: PRAGMA cache_size = 2000;
Disable memory mapping: PRAGMA mmap_size = 0;
Use disk-based temp storage: PRAGMA temp_store = file;
Close connections promptly

Backup and Maintenance

Backup Strategies

# SQLite databases can be backed up by copying the file
cp world.db world_backup_$(date +%Y%m%d).db

# Compress backup
gzip world_backup_$(date +%Y%m%d).db

Maintenance Commands

-- Analyze for better query plans
ANALYZE;

-- Vacuum to reclaim space (offline operation)
VACUUM;

-- Check database integrity
PRAGMA integrity_check;

-- Get database info
PRAGMA database_list;
PRAGMA table_list;

SQLite databases are highly reliable but benefit from regular ANALYZE commands to keep query performance optimal.

Next Steps

After successful installation:

Create indexes for your specific use cases
Implement connection pooling for web applications
Set up backup strategy using the provided scripts
Consider FTS for text search functionality
Monitor database size and performance

Need Help?

Join our community discussions for SQLite-specific questions and mobile development tips.

Database Documentation

Installation Guides

Support

Prerequisites

Method 1: SQLite Command Line

Method 2: Using Programming Languages

Python Implementation

Node.js Implementation

Database Structure Verification

Performance Optimization

Create Indexes

SQLite-Specific Optimizations

Connection Examples

Advanced Features

Full-Text Search

JSON Support

Troubleshooting

Backup and Maintenance

Backup Strategies

Maintenance Commands

Next Steps

Need Help?

Database Documentation

Installation Guides

Support

​Prerequisites

​Method 1: SQLite Command Line

​Method 2: Using Programming Languages

​Python Implementation

​Node.js Implementation

​Database Structure Verification

​Performance Optimization

​Create Indexes

​SQLite-Specific Optimizations

​Connection Examples

​Advanced Features

​Full-Text Search

​JSON Support

​Troubleshooting

​Backup and Maintenance

​Backup Strategies

​Maintenance Commands

​Next Steps

Need Help?

Prerequisites

Method 1: SQLite Command Line

Method 2: Using Programming Languages

Python Implementation

Node.js Implementation

Database Structure Verification

Performance Optimization

Create Indexes

SQLite-Specific Optimizations

Connection Examples

Advanced Features

Full-Text Search

JSON Support

Troubleshooting

Backup and Maintenance

Backup Strategies

Maintenance Commands

Next Steps