SQLite is the lightweight database engine used for everything from Android applications through to high performance web servers. Recently highly available, distributed, high-throughput versions of SQLite have started cropping up too.
A big reason for all the developer attention is the simplicity of how SQLite works. SQLite doesn’t require a software installation, in fact you don’t need to install anything at the system-level. One can simply install the
sqlite3 driver for your programming language of choice, and start using SQLite without any other work. All the database logic is handled in the driver code.
What is a database anyway?
In the SQLite model, each database is a file, usually a file that ends in
.sqlite3, for example a file named
This is different to traditional database engines like PostgreSQL, or MySQL where a
database is just another ‘entity’ to interact with, just like a table, function, or view.
In traditional database engines, you create a database in much the same way you create any other type of entity.
For example in PostgreSQL you execute
CREATE DATABASE finances, in much the same way you execute
CREATE TABLE employees, or
CREATE FUNCTION calculate_salaries.
How to SQLite CREATE DATABASE
So if you can’t create a database from the SQLite prompt, how can you create a database?
Well remember that SQLite databases are files? Well a new database is simply an empty file that ends in
.db. We don’t need a SQLite client to create one of those, although we can use the sqlite3 client, as you’ll see below.
Creating a SQLite database Using a *nix Terminal
On Linux, MacOS, or WSL, open the terminal and touch a new database file to create it:
This creates a 0-byte database file which is totally valid for SQLite! Try opening it (see below).
Creating a SQLite database on Windows
There are a couple of ways to do this on Windows - using the file explorer, or using the command prompt.
Creating a database using Explorer
Open the folder you want to create a database in, right click, select
Then rename the file to
finances.db, make sure to replace the
.txt extension with
.db (you will need to enable view file extensions first).
Creating a database using the command prompt
Open the command prompt or PowerShell and run:
copy NUL finances.db
This creates an empty database file in much the same way as
touch does on Mac and Linux.
Creating a SQLite database using the SQLite Cli
If you have the sqlite3 cli tool, you can run:
This creates the database and immediately connects to it.
Creating a SQLite database using Python, NodeJS, or other language
You can also create a database directly from your programming language of choice. Much like the SQLite3 cli you don’t need to actually create the
.db file before connecting to it, the driver will create the database and connect to it all in one go.
import sqlite3 con = sqlite3.connect('finances.db') con.close() # connection closed, database created.
const sqlite3 = require('sqlite3') const db = new sqlite3.Database('finances.db'); db.close() // connection closed, database created.
Opening your newly created SQLite database file
If you have Beekeeper Studio installed, you can just double click the
finances.db file in your file browser.
Beekeeper’s default file-associations also work in the terminal (I usually have a terminal open, so sometimes this is easier).
All three of these will open the
.db file in Beekeeper Studio if you have it configured as your default SQLite database handler (this is the default).
SQLite is amazing
I’m a huge fan of SQLite, in fact Beekeeper Studio stores all of it’s configuration data in a SQLite database in your home directory.
Hopefully now you know how to create a new database, you can use it even more. :-).
Beekeeper Studio - DB Browser for SQLite
Beekeeper Studio is an easy to use DB browser for SQLite. Simply open the file and edit your database contents in the same way you would edit a spreadsheet.