🪶SQLite

SQLite Home Page

SQLite (code) is the most widely used database in the world. It's actually built into all mobile phones and most computers. There are over 1 trillion SQLite databases in active use.

SQLite is a C-language library that implements a small, self-contained, serverless SQL database engine. And the code is in the public domain, so it’s free for anyone to use, with no restrictions.

Usually, you embed SQLite into your existing program – this means that the C code is linked into your program's runtime/execution.

SQLite has world-class documentation, and its developers plan to support SQLite through the year 2050 (!).

🤖 As described by AI

(written by AI, edited by humans)

SQLite is a software library that provides a relational database management system (RDBMS) implemented as a small, self-contained, and server-less engine. It’s a popular choice for embedded database applications due to its simplicity, efficiency, and reliability. SQLite is designed to be embedded into applications and requires minimal configuration or administration.

Due to its simplicity and versatility, SQLite is often a preferred choice for applications that need a local, embedded database without the complexity of a client-server architecture.

🌠 Features

Let's get into some of the reasons why SQLite is one of the most used databases around.

• Widely used - SQLite is one of the most-deployed databases in the world and is used in a wide range of applications, including mobile apps, web browsers, embedded systems, and desktop software
• Source code in the public domain - anyone is free to copy, modify, publish, use, compile, sell, or distribute the original SQLite code, either in source code form or as a compiled binary, for any purpose
• Serverless - unlike traditional databases, SQLite does not have a separate server process. SQLite is an embedded SQL database engine; it reads and writes directly to ordinary disk files, making it easy to integrate into applications
• Full-featured SQL - despite the "Lite," SQLite actually has a full-featured SQL implementation, including tables, indices, triggers, and views in unlimited quantities
• Self-contained - the entire SQLite database engine is contained in a single library file, usually just a few megabytes in size. SQLite is designed to be lightweight and efficient, ideal for resource-constrained environments

• ACID compliance - SQLite ensures data integrity through atomicity, consistency, isolation, and durability (ACID) properties. It supports transactions, which allow multiple database operations to be grouped together and either succeed or fail as a unit
• Reliable - SQLite is carefully tested prior to each release. Most of the SQLite source code is devoted purely to testing and verification.
• Zero-configuration - no setup or administration needed

👟 Getting started with SQLite

For the vast majority of programming languages, getting started with SQLite is as easy as including a package/module that exposes the bindings to you. For example, in Python using sqlite3:

import sqlite3

# Connect to the DB (a local file)
connection = sqlite3.connect("tutorial.db")

# Create a cursor
cursor = con.cursor()

# Execute a query (that creates a table)
cursor.execute("CREATE TABLE movie(title, year, score)")

And here's what it looks like in NodeJS, using better-sqlite3:

const sqlite3 = require('better-sqlite3');
const db = sqlite3('foobar.db');

// Create a prepared statement for selecing from the users table
const stmt = db.prepare('SELECT * FROM users WHERE id = ?');

// Run the prepared statement
const row = stmt.get(userId);
console.log(row.firstName, row.lastName, row.email);

You can also use SQLite from the command line:

1. Download the code: Get a copy of the prebuilt binaries for your machine, or get a copy of the sources and compile them yourself
2. Create a new database:

$ sqlite3 test.db # starts prompt & creates a database file called 'test.db'

SQLite version 3.42.0 2023-05-16 12:36:15
Enter ".help" for usage hints.
sqlite> 

Once you're in the prompt you can run SQL:

sqlite> CREATE TABLE movie(title, year, score);
sqlite> INSERT INTO movies VALUES ('The Matrix', '1999', '10');
sqlite> .mode table
sqlite> SELECT * FROM movies;
+------------+------+-------+
|   title    | year | score |
+------------+------+-------+
| The Matrix | 1999 | 10    |
+------------+------+-------+

See more details on the Getting Started page.