How to setup and use Sqlite 3 on Debian 9.4 and Ubuntu 16.04

Created by Jordy Leffers at 06-12-2017 11:05:35 +0100

Sqlite is chosen by many of its users for several reason, such as better performance, as it's a very lightweight database management system; It also doesn't need to be configured or started and stopped: it makes use of just a flat .db database file, which makes the whole database much more portable; This is also why Sqlite doesn't necessarily need to run on a server, it just uses the file it creates when creating a database and that way stays self-contained. In this guide I'll go over how to install and use Sqlite3 step by step.

Before we start, we need a Linux Debian or Ubuntu installation. We'll skip that step in this tutorial since you can easily get a default Linux installation on one of your containers on the www.cloudcontainers.net website. This tutorial is based on the cloud containers created on the my.cloudcontainers.net page. This means that you are by default the root user, so all of the commands below don't make use of sudo. If however, you are not the root user on your system, you'll have to add "sudo" in front of the commands found in the guide below.

 

First, it's always good practice to start off by updating our current packages:

apt update && apt upgrade -y

 

Install Sqlite3

On Debian and Ubuntu, by default, two different installations will be installed using the package manager. If you want, you could add a repository so you'll get the one that's appropriate for your project.

Debian will by default install Sqlite 3.16. And Ubuntu will by default install 3.11.

Both will install Sqlite 2,8.

 

Since we won't be using Sqlite 2, we will use the following command to install only Sqlite 3:

apt install sqlite3

 

And that's how easy it is.

 

How to use Sqlite3

We enter the Sqlite3 by typing in Sqlite3 followed by the database name we want to either open, or create.

sqlite3 new_database.db

Now you'll have entered the Sqlite terminal, here you can modify your database after your liking. In this tutorial, I'll be doing a quick example on how to make a new table inside this database.

 

CREATE TABLE test_table (id VARCHAR(20), name VARCHAR(20), PRIMARY KEY(id));  
INSERT INTO test_table VALUES (69, "Hilbert");

 

Now we have created our test_table and added a single record in it.

You can now exit the database by typing .quit. All Sqlite commands need a dot in front of them.

.quit

 

Back in the root folder, if we use the list command (ls), we'll find our database file:

 

We can move this file as we would any other, note that connecting to it will need you to specify the file path.

If you're in the same folder as your .db file, you'll reconnect using this command:

sqlite3 new_database.db;

 

 

Congratulations, you've now installed, and know how to use Sqlite3!

Comments

Comments are turned off.