How to create a SQLite Database for your Project and why it is the Perfect Choice for a $5 VPS Hosting

 


If you are looking to create a Database of your own for your own side project, or a hobby project on the World Wide Web, there are many options available. The really common ones are using a MySQL database, or some Tech Gurus will tell you to use Cloud based databases. Now, technically all that is fine, but in reality that costs money! $$ is the thing that you need to pull out of your pockets if you want a decent database up and running for your project. Why? Because, a decent database requires an installation and a fair amount of RAM needs to be allocated to it in order for it to be running successfully.

Now, if your project is a small or hobby project, there are fair chances that you are using a cost effective hosting solution for it. The reason that these hosting solutions are cost effective is, they are not very high on power. 

Again, if you are using a $5 VPS (i.e. the lowest end Virtual Machine available or most hosting providers) on a Digital Ocean, Google Cloud, Azure or AWS platform, they are very basic machines, mostly with 512 MB of RAM available on them. If you try to install a Database on it along with an Apache or Ngnix server, the end result is not going to be very performant. 

I was running computengine.com on a Google Cloud free tier f1-micro VM using Django architecture before migrating to blogger. It was my hobby project of learning Django, HTML, CSS and JavaScript and hosting a website on an Apache server. 

And before that, computengine.com was built in php using a MySQL database, running off the same f1-micro VM that Google cloud free tier offers. 

So, that gave me experience of actually running a dynamic website fetching data from a database for page content. 

Now, Django framework by default uses SQLite database, it creates a sqlite db for you if go by the default options. That was what prompted me to check the performance differences when using an sqlite database instead of a conventional MySQL or MongoDb for your website. 

SQLite is really lightweight, doesn't consume any extra resources and is a perfect choice for small applications which do not require high concurrent updates of data. 

In case you are interested, here is some more information on why to use SQLite:

So, enough of the fundas, now let's take a look at an example of how to create and use a sqlite database. You need to download the required files, which can be downloaded from here.

We are utilizing the Windows platform, so we'll be using the Pre-compiled Windows binaries. Recommendation is to use the SQLite-Tools which include some command line tools for database creation and management. 


When you unzip the contents of the sqlite-tools, there are 3 files in it.

We'll open sqlite3.exe file, which looks like below.


Now you can type .help to checkout the commands available. 


In order to create a database, we'll use the below command

sqlite> .open test.db

If the database exists, it will be opened. If it does not exist, it will be created. 



We'll quickly create a table 

sqlite> create table tbl1(one varchar(10), two smallint);


Let's insert some data there.

sqlite> insert into tbl1 values('hello!',10);

sqlite> insert into tbl1 values('goodbye', 20);


Let's checkout the data we just inserted. 

sqlite> select * from tbl1;


Super! so we are able to create a database, a table in it, and able to insert and read the data in the table. 

Now, is there a way to bulk load the data into this database? Yes! We'll take a look into that as well. 

Create a file named test.sql

Paste the contents as follows:

CREATE TABLE [TAGS](

[ID] INTEGER NOT NULL PRIMARY KEY,

[TAGNAME] TEXT  NOT NULL,

[SLUG] TEXT  NOT NULL

);

INSERT INTO TAGS VALUES(1,'TAG_1','This is Tag1');

INSERT INTO TAGS VALUES(2,'TAG_2','This is Tag2');

INSERT INTO TAGS VALUES(3,'TAG_3','This is Tag3');

Save and Close the file. 

Now, enter the commands as follows: 

sqlite> .read test.sql

This will load the file contents one by one. So, we are creating a table, and inserting 3 rows of data into the table with one shot. Let's see if it worked. 

sqlite> select * from tags;


Super! So we have successfully created a SQLite database on our system, created tables on it, and inserted data into the tables. 

Just one more thing. Let's cross check if the tables created via the two methods are available. 

sqlite> .tables


As you can see, both the tables are available in the database!

That's the end of this tutorial. If you are interested in more information, you can refer to official documentation


Thanks for Reading the Article. If you have reached this far, we hope that the article was useful to you! Please Like/Share/Follow us on FacebookTwitterTumblr. 

Comments

Popular posts from this blog

Calculate Your CTC Hike Percentage

Java Program to Find a List of Prime Numbers (Step by Step)

Java Program to check Palindrome

What is my IP Address - How to find your IP address on Local Network using Command Prompt, and External Network using JavaScript Code

Reduce Server Load from Dynamic Page Search - Create a Dynamic JavaScript based Table filter