How to create a SQLite table and insert records with the Command Line

Have you ever wondered if SQLite has command-line tools? If you're like me, you've been using SQLite for years, maybe even a decade, but somehow never touched its CLI. Well, this note is for you! Let me show you how SQLite’s CLI can change the way you interact with databases.

Getting Started with SQLite CLI

First things first, open up your terminal. To create or open a database, just type:

sqlite3 my_database.db

This command creates a file called my_database.db (if it doesn’t already exist) and drops you right into the SQLite CLI. You'll see something like this:

SQLite version 3.39.2 2024-09-06 12:34:56
Enter ".help" for usage hints.

Why CLI?

You might be wondering, "Why would I use the CLI instead of some GUI tool?" The answer is simple: speed and precision. The CLI lets you create, modify, and query your databases directly from the command line, without the distractions of a graphical interface. Plus, once you get the hang of it, it’s incredibly efficient.

Creating Tables Like with CLI

Let’s say you need a table for user data. Here’s how you’d do it in the CLI:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL
);

Type this directly into the CLI, and you’ve just created a table! The beauty of the CLI is that you get immediate feedback. If there's an error, the CLI will tell you exactly where you went wrong. No waiting for a GUI to update—just pure, instant results.

Inserting Data

Now, let’s add some data. This is where the CLI really shines. You can insert rows with a simple command:

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com');

Quick and painless, right? The CLI makes it easy to run these commands in sequence, allowing you to batch-process data with ease.

Querying Data

Once your data is in, querying is a breeze. Here’s a basic example:

SELECT * FROM users;

The CLI will output all the rows in your users table. This is where things get fun—you can experiment with complex queries, joins, and filters all within the same session, tweaking as you go. Pro Tip: Saving Queries

One of the CLI's lesser-known tricks is the ability to save your queries to a file. Imagine you’ve crafted the perfect query and want to reuse it later. Just write it out to a .sql file and then run it with:

sqlite3 my_database.db < my_query.sql

This can be a huge time-saver when working on larger projects or automating tasks.

Using SQLite CLI Commands

SQLite CLI comes with a set of built-in commands that go beyond SQL. For example, typing .tables lists all the tables in your current database. Typing .schema shows the schema of your tables. These commands make navigating your database much easier.

Here are a few useful ones:

.tables — List all tables in the database.
.schema [table_name] — Show the schema of a specific table.
.headers on — Show column headers in query results.
.mode column — Format query results as columns.
.quit — Exit the SQLite CLI.

Example: Combining CLI Commands

Let’s say you want to see a nicely formatted output of your users table:

.headers on
.mode column
SELECT * FROM users;

This will give you a clean, readable table view directly in your terminal.

Exiting and Reconnecting

When you’re done with your session, just type .quit to exit. The beauty of SQLite’s CLI is that you can reconnect to your database anytime by running sqlite3 my_database.db again. All your data and structures are saved in that one file—portable and ready to go. Final Thoughts

The SQLite CLI is an underappreciated gem. It’s simple, lightweight, and gives you full control over your databases without the overhead of a GUI. Whether you're running quick tests, building small projects, or even automating tasks, the CLI is a powerful tool to have in your toolkit.

So, next time you're working with SQLite, skip the GUI and dive into the command line. You might just find it's your new favorite way to interact with databases!

Happy coding, and welcome to the world of SQLite CLI! 🎉