08 Dec

How to Setup SQLite Server on Azure/AWS/GCP

To setup and install SQLite server on any of the cloud platforms (Azure, AWS, Google GCP), the best way is to use the SQLite server image from the cloud marketplaces. Deploy SQLite database server on Ubuntu Server from the following links below. SQLite is a public-domain software package that provides a relational database management system, or RDBMS, used to store user-defined records in large tables. Great use cases for SQLite are for websites, applications, data analysis, cache for enterprise data, server-side database and much more.

SQLite Server

SQLite Azure

Deploy SQLite on Ubuntu Server 20.04 Lts

SQLite AWS

Deploy SQLite on Ubuntu Server 20.04

SQLite GCP

Deploy SQLite on Ubuntu Server 20.04 on Google GCP

SQLite Features

SQLite is a lightweight, command-line and cross-platform database management system RDBMS. SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. SQLite is the most used database engine in the world. SQLite is built into all mobile phones and most computers and comes bundled inside countless other applications that people use every day

Transactional – Consistent, atomic, isolated and durable (ACID), allowing safe access from multiple processes or threads.
Full-featured SQL implementation – Advanced capabilities like partial indexes, indexes on expressions, JSON, common table expressions, and window functions.
Zero-configuration – Easy to use with no setup or administration needed.
Storage in a single cross-platform disk file – Used as an application file format that supports terabyte-sized databases and gigabyte-sized strings and blobs.
Written in ANSI-C and very fast – Appears faster than direct filesystem I/O.
Cross-platformed DBMS – Android, BSD, iOS, Linux, Mac, Solaris, VxWorks, and Windows (Win32, WinCE, WinRT) with easily portable to other systems.
Provides large number of API’s: – Large range Net languages (Visual Basic, C#), PHP, Java, Objective C, Python and a lot of other programming language.
Comes with a standalone command-line interface (CLI) client that can be used to administer SQLite databases.
Small Runtime Footprint – Less than a megabyte of a code and only a few megabytes of memory requirement.
Very flexible – Variables are dynamically typed and not defined in advance, and can work on multiple databases at the same time.

Getting Started with SQLite Server

Once your SQLite server has been deployed, the following links explain how to connect to a Linux VM:

Once connected and logged in, the following section explains how to configure SQLite

Verify SQLite Version

Once logged in, you’re ready to start using SQLite. To confirm the current version, run the following command:

				
					sqlite3 --version

Open SQLite Terminal Console

To get started with using SQLite, we use the sqlite3 command line. Run the following command to open the SQLite console command line:

				
					sqlite3

Create a SQLite Database

To get started with creating your first SQLite database, we use the sqlite3 command. Lets create a new database called cars that will contain different types of cars and their attributes.

				
					sqlite3 cars.db

This will create a new database named cars. If the file cars.db already exists, SQLite will open a connection to it; if it does not exist, SQLite will create it.

If the file cars.db does not already exist and if you exit the sqlite promote without running any queries the file cars.db will not be created. To make sure that the file gets created, you could run an empty query by typing ; and then pressing “Enter”. That way you will make sure that the database file was actually created.

With your car database created, you will now create a new table and populate it with data.

Create a SQLite Table

SQLite tables store information in SQLite databases. Tables are structured in rows and columns. SQLite commands are uppercase and user information is lowercase. Lines must end with a semicolon.

In this example i will create a table with columns for different types of data:

An ID
The cars make
The cars model
The cars size (in centimetres)

The following command creates the table:

				
					CREATE TABLE cars(id integer NOT NULL, make text NOT NULL, model text NOT NULL, length integer NOT NULL);

Using NOT NULL makes that a required field.

After creating the cars table, an empty prompt will return. Now let’s insert some values into it.

Adding Values into SQLite Tables

In SQLite, the command for inserting values into a table follows this general structure:

				
					INSERT INTO tablename VALUES(values go here);

The tablename is the name of your SQLite table, and values go inside parentheses.

Now insert three rows of VALUES into our cars table:

				
					INSERT INTO cars VALUES (1, "Porsche", "911", 451);
INSERT INTO cars VALUES (2, "BMW", "M3", 471);
INSERT INTO cars VALUES (3, "BENTLEY", "Continental", 481);

As we entered earlier we specified that entries were NOT NULL, so we must enter a vale for each.

If you try to add a value without an entry you will receive an error.

Reading SQLite Table Entries

To view data in your SQLite tables, we use the SELECT command

				
					SELECT * FROM cars;

You should see an output of the entries we added earlier

				
					1|Porsche|911|451
2|BMW|M3|471
3|BENTLEY|Continential|481

Exit SQLite3 Database Command Line

To exit out of sqlite3 terminal, simply type the following command:

				
					.quit

SQLite Server Support / Documentation

Read the SQLite Office Documentation on using SQLite Server database.

https://www.sqlite.org/docs.html

Also checkout their support forum for any help with support in using SQLite.

https://sqlite.org/forum/forum

Firewall Ports

If you need to configure any firewall ports depending on your cloud deployment, refer to the following guides:

To setup AWS firewall rules refer to – AWS Security Groups

To setup Azure firewall rules refer to – Azure Network Security Groups

To setup Google GCP firewall rules refer to – Creating GCP Firewalls

Disclaimer: SQLite is registered trademark by Hipp, Wyrick & Company Inc and licensed under Microsoft Public License (MS-PL). No warrantee of any kind, express or implied, is included with this software. Use at your risk, responsibility for damages (if any) to anyone resulting from the use of this software rest entirely with the user. The author is not responsible for any damage that its use could cause.