Practical SQLite3 Reference For Developers In A Hurry

SQLite is a terrific, lightweight, general purpose tool. But, as it is with all computer related things, I occasionally find myself needing a reminder on how to achieve certain database related tasks.

Let this be a living reference for working with SQLite3. No guarantees are made regarding completeness.

Installing SQLite3 #

With Homebrew:

brew install sqlite3

With MacPorts:

port install sqlite3

Or, from a precompiled binary:

• Download from the SQLite Download Page.

How to create a database #

sqlite3 awesome-project-database.db

How to create a table #

create table user (
    name text not null,
    email text not null unique
);

How to get all rows in a table #

select * 
    from user;

How to get a row from a table #

select email
    from user
    where name = 'Skye';

How to insert a row into a table #

insert into user (name, email)
    values('Skye', '[email protected]');

How to update a row in a table #

update user
    set email = [email protected]'
    where name = 'Skye';

How to delete a row in a table #

delete from user
    where name = 'Skye';