How to create a database in SQLite

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 .db, or .sqlite3, for example a file named finances.db.

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:

touch finances.db

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 New -> Text File.

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:

sqlite3 finances.db

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.

Python

import sqlite3
con = sqlite3.connect('finances.db')
con.close() # connection closed, database created.

NodeJS

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).

• MacOS: open finances.db
• Linux: xdg-open finances.db
• Windows: start finances.db

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.

Download Beekeeper Studio here