How to Create Databases in SQLite: A Step-by-Step Tutorial

SQLite is a lightweight, serverless, and easy-to-use database engine widely preferred for portable and small-scale applications. Creating databases in SQLite is simple and fast, making it a perfect option for beginners and developers who need an embedded database solution. This tutorial guides you through the essentials of creating databases using SQLite, including prerequisites, methods, and troubleshooting tips.

Prerequisites

SQLite Installed: You need SQLite installed on your computer. Check out our How to Install SQLite: A Step-by-Step Tutorial for detailed installation instructions.
Basic Command Line Knowledge: Familiarity with command line or terminal usage helps as SQLite commands are mostly executed there.
Text Editor: Any text editor to create SQL scripts if you prefer scripting over interactive commands.

Understanding SQLite Database Creation

Unlike some database systems where you explicitly create a database before using it, SQLite creates a database when you first connect to a new database file. Essentially, the database is a single file on disk.

Step-by-Step: Creating a SQLite Database

Open your terminal/command prompt.
Run the SQLite3 Command: Type sqlite3 database_name.db replacing database_name with your desired database file name. For example:

sqlite3 mydatabase.db

This command creates the file mydatabase.db if it doesn’t exist and opens an interactive SQLite shell connected to it.

Verify the database file: Exit the SQLite shell by typing .exit or pressing Ctrl+D. Check your directory for the new file.

Creating a Table to Confirm Database Functionality

After creating the database file, you’ll want to add tables and data. Here’s how:

sqlite3 mydatabase.db
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL
);
.exit

This creates a simple users table with columns for ID, name, and email.

Alternate Method: Creating a Database via Script

You can also create a database and tables via an SQL script file. For example, create a file init.sql with the following content:

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  price REAL
);

Run the script with the command:

sqlite3 products.db < init.sql

This creates products.db and applies the schema from your script.

Troubleshooting Tips

Database File Not Created: Verify you have write permissions in the current directory.
SQLite Not Installed: Ensure SQLite is installed and the sqlite3 command is available in your PATH.
Syntax Errors: Ensure SQL commands end with semicolons and follow correct SQLite syntax.
File Location Confused: Provide absolute paths if unsure where SQLite creates files.

Summary Checklist

Install SQLite and verify sqlite3 command works.
Open terminal/command prompt.
Create a new database with: sqlite3 yourdatabase.db.
Create tables with SQL commands.
Use SQL scripts to batch-create databases and tables if preferred.
Check file location and permissions if problems occur.

SQLite’s simplicity and file-based databases make it ideal for many lightweight and embedded database needs. By following these steps, you can start creating and managing your own databases effortlessly.

For further learning, you might want to explore how to use the SQLite CLI in depth by visiting our guide on How to Use SQLite CLI: Complete Beginner’s Guide.