SQLite for beginners: an embedded database that is always with you

Imagine: you are writing an application and you need a database. This usually means installing MySQL or PostgreSQL, configuring the server, creating users, configuring security... Stop. What if I say that there is a database that works without all this?

Meet SQLite. It's not just a lightweight alternative — it's a completely different approach to working with data. And if you're a beginner developer, SQLite can be your best friend at the start of your career.

What is SQLite and why is it special

SQLite is an embedded relational database. But what does "embedded" mean? Unlike MySQL or PostgreSQL, which run as separate servers, SQLite is just a library that you connect to your application. The entire database is stored in a single file on disk.

Sounds simple? Because it is. SQLite does not require server installation, port configuration, or user creation. You just create a file with the extension .db or .sqlite — and you have a full-fledged database.

Here's what makes SQLite unique: it's the most common database in the world. It is built into every Android and iOS smartphone, Chrome and Firefox browsers, Windows and macOS operating systems. According to the developers, there are more than a trillion active SQLite databases. Yes, a trillion — with twelve zeros.

Where SQLite is used

SQLite is great for tasks where you don't need multi-user work at the database level. These are desktop applications, mobile applications, embedded systems, prototypes of web applications, and local data storage in browsers.

Specific examples?

Messengers like WhatsApp use SQLite to store correspondence history on your phone. Browsers store bookmarks, history, and cookies in SQLite. Games save the player's progress in SQLite. Even Dropbox uses SQLite to synchronize file metadata.

When is SQLite not suitable?

If you have a high-load web service with thousands of simultaneous connections, it is better to choose PostgreSQL. If you need data replication between servers, SQLite will not be able to handle it. If the data is measured in terabytes, look for other solutions.

Installation and first steps

The good news is that SQLite may already be installed on your computer. Open the terminal and enter the command sqlite3 --version. Did you see the version? Great, you can start working.

If SQLite is not installed, the installation process takes a minute. On Windows, download the precompiled binaries from the official website. On Linux, use the package manager: sudo apt install sqlite3 for Ubuntu or sudo yum install sqlite for CentOS. On macOS, SQLite is usually already pre-installed, but you can update it via Homebrew with the brew install sqlite3 command.

Let's create the first database. Open the terminal and enter the sqlite3 myapp.db command. You will see an SQLite prompt with the prefix sqlite>. Congratulations — you have just created a database. Yes, it was that easy.

Basics of working through the command line

SQLite has an interactive shell with useful commands. All special commands begin with a dot. The .help command will display a list of available commands. The .databases command will display the connected databases. The .tables command will show all tables in the current database. The .schema command will display the structure of all tables. The .quit or .exit command will end the work with SQLite.

Let's try to create the first table. Let's say we're making an application for tracking tasks:

CREATE TABLE tasks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    description TEXT,
    completed INTEGER DEFAULT 0,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

What's going on here? We created a table with five columns. Column id is an auto-increment primary key, it will be generated automatically. Column title is text, required field. Column description — text, can be empty. Column completed — number, default 0 (used as a Boolean flag). Column created_at — text, automatically filled with the current date.

Pay attention to the data types. SQLite uses dynamic typing and supports five basic storage types: NULL (no value), INTEGER (integers), REAL (floating-point numbers), TEXT (text strings), and BLOB (binary data). This is simpler than in other databases, but it is quite sufficient for most tasks.

Basic data operations

Now let's add a few tasks to our table:

INSERT INTO tasks (title, description) 
VALUES ('Learn SQLite', 'Read the article and try the examples');

INSERT INTO tasks (title, description) 
VALUES ('Write code', 'Create a simple application with SQLite');

INSERT INTO tasks (title) 
VALUES ('Mark task as completed');

Let's see what happened:

SELECT * FROM tasks;

You will see three records with automatically generated IDs and creation dates. Note that in the third entry, the description field will be NULL — we did not specify it.

Let's mark the first task as completed:

UPDATE tasks 
SET completed = 1 
WHERE id = 1;

Now let's select only the uncompleted tasks:

SELECT id, title, completed 
FROM tasks 
WHERE completed = 0;

And if you need to delete a task:

DELETE FROM tasks 
WHERE id = 3;

These four operations — SELECT, INSERT, UPDATE, DELETE — form the basis for working with any relational database. The SQLite syntax is almost identical to standard SQL, so knowledge is easily transferred to other DBMSs.

Working with SQLite from Python

The best part of SQLite is that support is built right into the Python standard library. There is no need to install additional packages — the sqlite3 module is already there.

Here is a minimal example of the work:

import sqlite3

# Connecting to the database (the file will be created automatically)
conn = sqlite3.connect('myapp.db')
cursor = conn.cursor()

# Creating a table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT NOT NULL UNIQUE,
        email TEXT NOT NULL,
        created_at TEXT DEFAULT CURRENT_TIMESTAMP
    )
''')

# Adding a user
cursor.execute(
    'INSERT INTO users (username, email) VALUES (?, ?)',
    ('john_doe', 'john@example.com')
)

# Saving changes
conn.commit()

# Getting all users
cursor.execute('SELECT * FROM users')
users = cursor.fetchall()

for user in users:
    print(f"ID: {user[0]}, Username: {user[1]}, Email: {user[2]}")

# Closing the connection
conn.close()

Pay attention to the question marks in the INSERT query. These are placeholders for parameters — the correct way to pass data to SQL queries. Never use f-strings or concatenation to insert data into SQL — this opens the door to SQL injections.

A more convenient way to work is to use the context manager:

import sqlite3

with sqlite3.connect('myapp.db') as conn:
    cursor = conn.cursor()
    
    # We get results as dictionaries instead of tuples
    conn.row_factory = sqlite3.Row
    
    cursor.execute('SELECT * FROM users WHERE username = ?', ('john_doe',))
    user = cursor.fetchone()
    
    if user:
        print(f"User found: {user['username']}, {user['email']}")

The with construct will automatically close the connection and save the changes. And row_factory = sqlite3.Row allows you to refer to columns by name instead of index — much more convenient.

Working with SQLite from JavaScript

In Node.js, the most popular library for working with SQLite is better-sqlite3. It is synchronous and very fast.

First, install the package:

npm install better-sqlite3

Now we can work with the database:

const Database = require('better-sqlite3');
const db = new Database('myapp.db');

// Creating a table
db.exec(`
    CREATE TABLE IF NOT EXISTS products (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        price REAL NOT NULL,
        stock INTEGER DEFAULT 0
    )
`);

// Prepared request for insertion
const insert = db.prepare(
    'INSERT INTO products (name, price, stock) VALUES (?, ?, ?)'
);

// Adding several products
const products = [
    ['Laptop', 45999.99, 5],
    ['Mouse', 599.00, 20],
    ['Keyboard', 1299.00, 15]
];

for (const product of products) {
    insert.run(product);
}

// We receive all the goods
const allProducts = db.prepare('SELECT * FROM products').all();
console.log('All products:', allProducts);

// We get one product
const laptop = db.prepare('SELECT * FROM products WHERE name = ?').get('Laptop');
console.log('Product found:', laptop);

// Closing the database
db.close();

The advantage of better-sqlite3 is that it is synchronous — you don't have to mess with promises and async/await for simple operations. For more complex scenarios, the browser uses Web SQL (deprecated) or IndexedDB, which work on different principles.

Practical advice and best practices

The first rule: always use transactions for multiple related operations. This not only ensures data integrity, but also significantly speeds up the work:

conn = sqlite3.connect('myapp.db')
cursor = conn.cursor()

try:
    cursor.execute('BEGIN TRANSACTION')
    
    # Multiple insert operations
    for i in range(1000):
        cursor.execute('INSERT INTO logs (message) VALUES (?)', (f'Log {i}',))
    
    cursor.execute('COMMIT')
except Exception as e:
    cursor.execute('ROLLBACK')
    print(f'Error: {e}')
finally:
    conn.close()

Without a transaction, a thousand inserts can take several seconds. With a transaction, it takes a fraction of a second.

Second tip: use indexes for frequently requested columns. If you often search for users by email, create an index:

CREATE INDEX idx_users_email ON users(email);

Indexes speed up SELECT queries, but slow down INSERT and UPDATE. Use them wisely.

Third point: use the VACUUM command regularly to optimize the database. SQLite does not free up space automatically when deleting records:

VACUUM;

This command rebuilds the database, freeing up unused space and optimizing the file structure.

Fourth: make backups. SQLite is just a file, so backing up is easy. Just copy the .db file to a safe place. But do it when the database is not actively used, or use special commands for online backup.

Restrictions to be aware of

SQLite is great, but it has its limitations. The size of the database can theoretically reach 281 terabytes, but in practice it is recommended not to exceed several gigabytes. SQLite uses database-wide locking — other processes cannot read when writing. This is normal for local applications, but not for high-load servers.

There is no built-in support for users and access rights — security is provided at the file system level. No replication and clustering out of the box. Some advanced SQL functions like window functions are not available (although basic support has been added in new versions).

But for 90% of the tasks of a novice developer, these limitations are not critical. SQLite is an ideal tool for learning, prototyping, and creating local applications.

Tools for working with SQLite

The command line is good, but sometimes you want a visual interface. There are many GUI tools for SQLite. DB Browser for SQLite is a free, open source, cross-platform tool that is great for beginners. SQLiteStudio is another free option with plugin support. For professionals, there is DataGrip from JetBrains or extensions for VS Code like SQLite Viewer.

Personally, I recommend starting with DB Browser — it is intuitive, allows you to visually create tables, view data in tabular form, perform SQL queries with syntax highlighting and export data to various formats.

Practical assignment

The best way to learn is to practice. Try creating a simple expense tracking app. The database should contain a table with expenses (date, category, amount, description) and a table with categories (name, color for visualization). Implement the addition of a new expense, viewing all expenses for a certain period, grouping expenses by category and calculating the total amount.

This is a small project, but it will cover all the basic operations with the database and give you practical experience with SQLite.

Conclusion

SQLite is not just a database for beginners. It is a powerful tool that is used in production by millions of applications. Its ease of installation and use makes it ideal for training, and its reliability and performance make it an excellent choice for real projects.

Start with SQLite, master the basics of working with databases, understand the principles of SQL — and then the transition to PostgreSQL or MySQL will be simple and natural. After all, the concepts are the same everywhere, only the scale and the number of additional opportunities change.

So install SQLite, create your first tables and start experimenting. It's easier than it seems and more interesting than you can imagine.

Code — an educational platform for beginner programmers with courses in Python, JavaScript and web development.

Join our Telegram community developers, where you can ask questions, get help with the code and find like-minded people for joint learning.