Skip to main content

SQLite3 databases

Creating a database sounds like it should be complicated — servers, configuration, passwords, the works. With SQLite, it's none of that. A SQLite database is just... a file. That's it. This guide walks through exactly how to make one, in plain English.

The Simplest Way: One Line in the Terminal

Open your terminal and type:

sqlite3 mydatabase.db

That's the whole process. If mydatabase.db doesn't exist yet, SQLite creates it for you on the spot. You'll land inside something called the "SQLite shell" — basically a little command box where you can type SQL commands directly.

One small thing worth knowing: the file doesn't technically get saved to your disk until you actually write something to it, like creating a table or adding data. So if you open the shell and immediately close it without doing anything, don't be surprised if the file isn't there yet.

Creating a Database and a Table at the Same Time

Want to skip a step? You can create the database and set up a table in one single command, right from the terminal:

sqlite3 company.db "CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, department TEXT);"

This does two things at once: it creates company.db if it doesn't already exist, and it builds an employees table inside it. No need to open the shell separately first.

Creating a Database from Code

If you're writing a program, you probably don't want to shell out to the terminal every time — you want your code to handle it. Here's what that looks like in Python:

import sqlite3

# Create/connect to database
connection = sqlite3.connect('example.db')
cursor = connection.cursor()

# Create a table
cursor.execute('''CREATE TABLE IF NOT EXISTS users 
                  (id INTEGER PRIMARY KEY, username TEXT, email TEXT)''')

# Commit and close
connection.commit()
connection.close()

Here's what's actually happening, step by step:

  1. sqlite3.connect('example.db') — this either opens the database if it already exists, or creates a brand new one if it doesn't. Either way, you get a connection to work with.
  2. cursor — think of this as your messenger. It's what actually sends your SQL commands to the database.
  3. cursor.execute(...) — this runs your CREATE TABLE command. The IF NOT EXISTS part just means "don't panic if this table is already there — just skip creating it again."
  4. connection.commit() — this saves your changes. Skip this step and your changes might not actually stick.
  5. connection.close() — closes the connection cleanly, like hanging up the phone properly instead of just walking away.

Databases That Live Only in Memory

Sometimes you don't want a database file at all — you just want somewhere temporary to work with data while your program runs. SQLite has you covered:

connection = sqlite3.connect(':memory:')

Instead of a filename, you just type :memory:, and SQLite builds the database entirely inside your computer's memory instead of on disk. The moment your program stops running, that database disappears completely — nothing is left behind. This is great for testing things quickly or crunching temporary data you don't need to keep.

Treating Your Database Like Any Other File

Because a SQLite database really is just one file, you get to treat it like any other file on your computer. You can:

  • Copy it — just like copying a photo or a document
  • Back it up — literally just duplicate the file somewhere safe
  • Move it to another computer — drag it over, email it, upload it, whatever works
  • Open it in different apps — as long as the app supports SQLite, it can open the same file

No exporting, no special transfer process. It's just a file doing file things.

Double-Checking What You Built

Once you've created your database, it's smart to check that everything actually worked. Two quick commands help with that:

sqlite3 mydatabase.db ".tables"  # List all tables
sqlite3 mydatabase.db ".schema"  # Show table structures

.tables gives you a quick list of every table inside the database, so you can confirm they were actually created. .schema goes a step further and shows you the full structure of each table — every column, every rule you set up.

A Few Habits Worth Building

  • Use full file paths, not shortcuts, especially in real projects. Typing just mydatabase.db works fine when you're testing things on your own computer, but in a real application it can get confusing about which mydatabase.db you mean. Spelling out the full path avoids that headache entirely.
  • Group multiple changes together using transactions. If you're making several changes at once, wrapping them in a transaction means they all succeed together or all fail together — you won't end up with half-finished, inconsistent data.
  • Remember: no write, no file. An empty database that hasn't had anything written to it yet might not actually exist on disk. Don't panic if you don't see the file right away — write something to it first.

The Bottom Line

Making a SQLite database really is as simple as it sounds. There's no server to configure, no accounts to set up, no installation wizard to click through. Whether you're doing it from the terminal in one line or setting it up inside your own code, you're just creating a file — and that file is your entire database, ready to go.

Labels:

Popular posts from this blog

How to Check if Someone is Connected to Your Machine in Linux

Picture this: you glance at your system monitor and notice your CPU is humming along even though you're not running anything demanding. Or maybe your internet feels sluggish for no obvious reason. A small, uneasy thought creeps in — is someone else on my machine right now? For Linux users, this isn't something you have to wonder about. Linux ships with a powerful set of built-in tools that let you see exactly who's connected, who's logged in, and what your network is doing at any given moment. You don't need to be a security expert to use them — you just need to know where to look. This guide walks you through the practical, no-nonsense steps to check for unauthorized connections on your Linux system, with real commands you can run right now. Why Monitoring Network Connections Matters Every device on a network — including your own Linux machine — communicates using an IP address. When another device or user connects to your system, that connection shows up as a trac...
Read more

How to Set Up a Linux Web Server and Host an HTML Page Easily

Setting up a web server on Linux means spending a fair amount of time in the terminal — Linux leans heavily on the command line rather than clicking through menus, so you'll be typing out instructions more often than not.  If you're new to this, it can feel a little intimidating at first, but the good news is you don't need to become a Linux wizard overnight. A handful of core commands will get you surprisingly far. A few you'll lean on constantly: cd — move between directories ls — see what's in the current directory mkdir — create a new folder nano or vim — edit files right there in the terminal sudo — run something with administrator privileges Get comfortable with these and you'll be able to navigate around, tweak configuration files, and install software without much trouble. You don't need to memorize everything — you just need to be confident enough to follow along with clear instructions, which is exactly what this guide aims to give you....
Read more

Linux Network Troubleshooting

If you've spent any time as a sysadmin — or honestly, just as someone who's had to fix their own home network at 11pm — you know that connectivity issues are one of the most common headaches out there. The good news is that a handful of core tools and a methodical approach can take you from "why isn't this working" to a root cause pretty quickly.  This guide walks through the essentials: configuring interfaces, managing routes, and diagnosing problems when things go sideways. Configuring Network Interfaces Your network interfaces are the actual bridge between your machine and the outside world, so getting them configured correctly is step one for any kind of reliable connectivity. Doing It Manually ifconfig is the old-school, tried-and-true tool for this on Unix-like systems. To see everything currently configured, run: ifconfig -a If you need to manually set up a specific interface — assigning an IP, a netmask, and bringing it online — it looks like this: ifconf...
Read more