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 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.
Table of Contents
Getting Started with SQLite Server
Verify SQLite Version
Once logged in, you’re ready to start using SQLite. To confirm the current version, run the following command:
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:
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.
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:
SQLite Server Support / Documentation
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.