SQLite regularly ranks in the Top 10 In the DB Engines Rankings. I didn’t know anything about this database or how it worked, so I wanted to try it out.
Overview:
SQLite is an embedded relational database engine. It doesn’t run as a service, it’s a library you call to interact and modify data.
There’s nothing to install with SQLite, you just need to download the files and copy them to you machine. There’s no server process that runs, you just make calls to the library. SQLite uses SQL, a large number of the standard commands work here, although there certainly are a few things that take a non-standard approach.
Download:
SQLite Download:
I downloaded the Windows – 64 bit (Version 3.41.0) files. The ZIP is under a MB in size. When the file is unzipped, there’s just two files: A DLL and a DEF (Export Definition File). Just save the files somewhere on your PC.
There’s also a link to download command line tools for SQLite. I’m going to skip that step, and use the DBeaver tool to connect to and manage this instance.
Connect With DBeaver:
In DBeaver, I’ll create a New Database Connection for SQLite. I’m using the Community edition, version 23.0.0.
For the Connect By value, I’ll use the default of Host.
On the Driver Properties tab, you’ll be prompted to download additional files for driver. Download any required files.
Back on the Main tab, in Path, I navigate to where I want to create the database file. I’ll use database.db for the name for the new file, then Open. Once we’re back to the Connection dialog, click Finish.
Things To Know Before Using Sqlite:
There’s several things with SQLite that are going to be different from most other relational databases.
We don’t have to explicitly create a database, just the database file. When we created the database.db file in the Connect step, we also created a database. There’s one database per file. There’s no concept of a schema or namespace in SQLite.
There aren’t database users in SQLite. If a user has access to the db file, then they can use the database. There’s aren’t roles or permissions either, having access to the database means the user can perform any valid action.
There are a limited number of Data Types:
Integer, Real, Text, Blob – NULL can be also be used, and there’s also an ANY datatype that can take any value. There’s no boolean or datetime data types, although dates can be stored as strings, and booleans as integer values (0 or 1).
You can create Indexes, Foreign Keys and Views. There are natural joins and left outer joins, but no right or full outer joins.
There aren’t stored procedures, functions or sequences.
There are foreign keys, but they are disabled by default. This command will enable the constraints:
PRAGMA foreign_keys ON;
There’s a page to cover some SQLiteQuirks.
Using SQLite:
CREATE TABLE customer (
customer_id integer not null primary key,
first_name text not null,
last_name text not null
) STRICT;
INSERT INTO customer(first_name, last_name)
VALUES ('Jane', 'Doe');
INSERT INTO customer(customer_id, first_name, last_name)
VALUES (2, 'John','Doe');
SELECT * FROM customer;
Here’s SQL to create a simple table in SQLite, with some inserts. Most of it is straightforward SQL, but with a few exceptions.
You’ll notice that the first insert doesn’t include an explicit value for customer_id while the 2nd insert does. Declaring an integer column as a primary key will implicitly add auto-increment to that column. So we can add our own value if we want, or if we omit it, an ID value will be generated for us. There’s also an autoincrement keyword we can add to the column definition if we always want a value generated for us.
There’s also the STRICT keyword as part of the table definition. As the default behavior, SQLite will pretty much accept any value in any column with very loose standards. I could define a column as integer, but still insert ‘Customer’ in that fields, if I wanted. The STRICT keyword will force the system to enforce these constraints.
Links:
SQLite
SQL Rob 