SQLite

SQLite is public domain software that provides a relational database management system. The “lite” in SQLite means that it is lightweight because it has nothing to install and nothing to configure. It is an embedded database engine that directly reads and writes into ordinary disk files because unlike other databases, it has no separate server. It packages the database into one single file with a cross platform format that contains the layout of the database and the actual data stored in the various tables. It is well suited to embedded systems because of its small size and conservative resource use. Unlike other RDBMS, SQLite uses dynamic-type system for tables which allows us to put any value in any column regardless of type.

SQLite was designed in the spring of 2000 by Dwayne Richard Hipp while he was working for General Dynamics that was on contract with the United States Navy. He based the semantics and syntax of SQLite on PostgreSQL 6.5. His aim was to design SQLite to be operated without installing a database management system so that a database administrator wouldn’t be a need anymore.

SQLite has the following features:

Serverless

• SQLite Library accesses its storage files directly so a separate system or server process is not a requirement.

Zero Configuration

• No server required means no configuration.

Self Contained

• A single library contains the entire database system.

Small Runtime Footprint

• The build is less than a megabytes of code and only a few megabytes of memory are used.

Transactional

• SQLite transactions are ACID-compliant

Full Featured

• Supports most query languages

Reliable

• SQLite’s development team does code testing and verification.

What is SQLite good for?

Some SQLite roles are similar to those client-server RDBMS products and some of its roles were to fill in the gaps of other database management system. For example client-server RDBMS needs a lot of upkeep as a result it limits how and where it can be deployed. SQLite fills in this gap by providing a tool to store, organize and manipulate data to make it smaller. The purpose of SQLite is to complement rather than replace the larger client-server RDBMS in circumstances where simplicity is more essential than capacity.

Here are some advantages of SQLite:

Speed

• SQLite has no socket or TCP/IP overhead. It is 2 to 3 times faster than MySQL and postgresQL and has faster PHP interface.

Functionality

• SQLite data storage is upto 2tb. It has a small footprint and its database is easily moveable.

Security

• Each SQLite user has its own independent database(s).

Examples and Code Snippets

Let’s assume you have already installed SQLite in your computer. Run sqlite3.exe, It will show a Console Application that will allow you to work with SQLite databases.

This command will let you connect, create a new directory, and navigate to the newly created directory.

C:\Users\Demo> mkdir sqlite_databases

C:\Users\Demo> cd sqlite_databases

We will create a database and name it ‘demoDBtest.db’

C:\Users\Demo\sqlite_databases> sqlite3 demoDBtest.db

After creating the database, this information will show:

C:\Users\Demo\sqlite_databases> sqlite3 demoDBtest.db

SQLite version 3.7.15.2 2016-23-06 05:21:21

Enter ".help" for instructions

Enter SQL statements terminated with a ";"

sqlite>

Example to create a table inside the database we created:

sqlite> CREATE TABLE EMPLOYEE (


    ID INT PRIMARY KEY  NOT NULL,


    NAME           TEXT NOT NULL,


    AGE            INT  NOT NULL,

    ADDRESS        CHAR(50)

    SALARY         INT

);

This command creates a table called EMPLOYEE inside the demoDBtest.db database. It has five columns named ID, NAME, AGE, ADDRESS and SALARY.

To show the list of tables in the database, use this command: .table

sqlite> .table

EMPLOYEE

sqlite>

To view the table structure, use this command: .schema <table name> It shows the original CREATE statement including all its indices.

sqlite> .schema EMPLOYEE

CREATE TABLE EMPLOYEE (

    ID INT PRIMARY KEY  NOT NULL,     

    NAME           TEXT NOT NULL,

    AGE            INT  NOT NULL,

    ADDRESS        CHAR(50)     

    SALARY         INT

);

Insert data to the table:

sqlite> INSERT INTO EMPLOYEE (ID, NAME, AGE, ADDRESS, SALARY)


       VALUES (1, 'John Paul', 32, 'California', 20000 ),  


              (2, 'Alladin', 25, 'Texas', 15000 ),

              (3, 'Alvie', 23, 'Norway', 20000 ),

              (5, 'Peter', 27, 'Texas', 85000 ),

              (6, 'Kim', 22, 'South-Hall', 45000.00 );

To fetch all the inserted data:

sqlite> .header on

sqlite> .mode column

sqlite> SELECT * FROM EMPLOYEE

The result would look like this:

ID          NAME        AGE         ADDRESS     SALARY

----------  ----------  ----------  ----------  ----------

1           John Paul     32        California  20000

2           Alladin       25        Texas       15000

3           Alvie         23        Norway      20000

4           Miming        25        Washington  65000

5           Peter         27        Texas       85000

6           Kim           22        South-Hall  45000

To fetch only selected fields/columns:

sqlite> SELECT ID, NAME, SALARY FROM EMPLOYEE:

Above query will produce the following result: 

ID          NAME        SALARY

----------  ----------  ----------

1           John Paul   20000

2           Alladin     15000

3           Alvie       20000

4           Miming      65000

5           Peter       85000

6           Kim         45000

To update a column/field: Example we will update the address of the employee with ID = 6,

sqlite> UPDATE EMPLOYEE SET ADDRESS = 'Philippines' WHERE ID = 6;

Now, EMPLOYEE table would have the following records: 

ID          NAME        AGE         ADDRESS     SALARY

----------  ----------  ----------  ----------  ----------

1           John Paul    32         California   20000

2           Alladin      25         Texas        15000

3           Alvie        23         Norway       20000

4           Miming       25         Washington   65000

5           Peter        27         Texas        85000

6           Kim          22         Philippines  45000

From this,

6           Kim         22          South-Hall        45000

To this, 

6           Kim         22          Philippines       45000

Let's assume that employee Peter is no longer working in the company so we would like to  delete his records to our database. The command should be like this. 

sqlite> DELETE FROM EMPLOYEE WHERE ID = 7;

Now EMPLOYEE table will have following records: 

ID          NAME        AGE         ADDRESS     SALARY

----------  ----------  ----------  ----------  ----------

1           John Paul   32          California  20000

2           Alladin     25          Texas       15000

3           Alvie       23          Norway      20000

4           Miming      25          Washington  65000

6           Kim         22          Texas       45000

Conclusion

This basic overview of SQlite should help clarify whether it is ideal to use SQLite, as opposed to a full-blown RDBMS like Mysql or Postgres. SQLite has wrappers and drivers in all the major languages and can keep running on general systems. In the event that you want to dive in deeper, there are lots of references available on the net. Good luck, and have fun learning SQLite.