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

1

Create Database File

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

Import SQL File

.read sqlite/world.sql
SQLite import is typically the fastest due to its lightweight nature and single-file architecture.
3

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

1

Install Dependencies

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

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()
3

Run Installation

python install_world_db.py

​
Node.js Implementation

1

Install Dependencies

npm install sqlite3
2

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();
3

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

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']}")

​
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

Problem: database is locked errorSolutions:
  1. Close all connections to the database
  2. Check for zombie processes: fuser world.db
  3. Remove WAL files: rm world.db-wal world.db-shm
  4. Use connection timeout: sqlite3 -timeout 10000 world.db
Problem: database disk image is malformedSolutions:
  1. Run integrity check: .integrity_check
  2. Try to repair: .recover world_recovered.db
  3. Restore from backup if available
  4. Re-import from original SQL file
Problem: Queries are slowSolutions:
  1. Create appropriate indexes (see Performance Optimization)
  2. Enable WAL mode: PRAGMA journal_mode = WAL;
  3. Increase cache size: PRAGMA cache_size = 10000;
  4. Use prepared statements
  5. Consider using FTS for text searches
Problem: High memory consumptionSolutions:
  1. Reduce cache size: PRAGMA cache_size = 2000;
  2. Disable memory mapping: PRAGMA mmap_size = 0;
  3. Use disk-based temp storage: PRAGMA temp_store = file;
  4. 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:
  1. Create indexes for your specific use cases
  2. Implement connection pooling for web applications
  3. Set up backup strategy using the provided scripts
  4. Consider FTS for text search functionality
  5. Monitor database size and performance

Need Help?

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