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 Facebook, Twitter, Tumblr. 

Comments

Post a Comment

Popular posts from this blog

Calculate Your CTC Salary Hike Percentage - Online Calculator to find your New Salary

-
Image
  Did you receive a Salary Hike? Congratulations!  Using this CTC Hike Percentage calculator you can calculate: Percentage Hike Received New Expected Salary Old Salary (yes, you already know it! But if you want to compare, we've included that feature as well!) Receiving a Salary Hike is a really happy occasion. It means that your work is valued in your organization and the organization is willing to invest in you and your career growth. But as any human being, you are interested in exactly how much you have received. Most organizations provide either a lump sum amount in the hike letter or mention a percentage. Employees are left wondering that if I have received a certain amount of hike, what exactly is the percentage when compared to my old or previous salary. In the same way, if percentage is mentioned, then the employee is left wondering what will be my new salary.  So, we have a very simple solution for y...
Read more

Find the Longest Streak of 1's in a Binary Array using Java Code

-
Image
  You have been given an Integer Array which contains only 1s and 0s, and asked to find the longest running streak of 0 or 1 in the array. Sound familiar? Its a common interview question. Let's try to find a solution using Java.  We would required two counters here, 1 counter to keep track of the running count, and the second one to keep track of the maximum running streak found.  We'll loop through the array using a for loop.  Here is the code for your reference: /**  * Find the longest Streak of 1s  * @author computengine.com  * @param a  * @return the length of the longest streak  */ public int findLongestStreak ( int [ ] b ) { System . out . println ( " Array for processing: " + Arrays . toString ( b ) ) ; int count = 0 , max = 0 ; //loop through the array for ( int i = 0 ; i < b . length ; i + + ) { //increment count when 1 is found if ( b [ i ] = = 1 ) count + = 1 ; //if count is greater...
Read more

Java Program to read Excel File and Load into Array

-
Image
Excel is one of the most important productivity tools used in offices. It is also a great organizational tool, and its really easy to create tables in excel to store different kinds of data.  When doing automation, excel is an important tool to store data for automation scripts, which makes reading Excel data using Java code an important Interview Question. So, today we'll learn how to read an excel sheet using Java, and return the contents as a String Array. The Array can then be utilized as per your requirement.  We are reading an xlsx file, the most recent and most popular excel format. We need to import the below classes: import java . io . File ; import java . io . FileInputStream ; import org . apache . poi . xssf . usermodel . XSSFCell ; import org . apache . poi . xssf . usermodel . XSSFRow ; import org . apache . poi . xssf . usermodel . XSSFSheet ; import org . apache . poi . xssf . usermodel . XSSFWorkbook ; java.io.File and java.io.FileInputStream will ...
Read more

Java Program to Read a Properties file and return a Property Value

-
Image
  Properties file in Java is used to store configurations, data or settings for a project. They have been utilized as Object Repositories for Automation Projects in the past (and still are used in legacy projects) until the Page Object Model came along.  This makes reading of Properties files an important concept in Java. This Tutorial will cover the below topics: 1. Create  Properties file in a Java Project 2. Set Properties in the file 3. Read the Properties file using Java code and return the value Let's first see how to create a Properties file for your Project. Its really easy.  In Eclipse, right click on the folder where you want to create the file, and navigate to New -> File. Now enter the file name, and make sure to enter the extension as .properties The file has been created! Now, the next step, i.e. enter some data in the file that we'll read using Java. The properties stored in the properties file have to follow a certain format.  You can enter l...
Read more

4 ways to add New Line Character to a String in Java

-
Image
When you are required to output Strings with your Java code, more often than not, there is a requirement to format the output correctly.  Adding a newline character between two strings in order to separate them, or create a paragraph is one of the most common requirements. Let's look at a few ways today, on how to output a newline between two Strings. 1. System.out.println inbuilt function One of the easiest ways, is to use the function System.out.println(). The println() function will automatically add a new line to the output, if using two strings.  Sample: String s1 = "This is a String." ; String s2 = "This is another String." ; System . out . println ( s1 ) ; System . out . println ( s2 ) ; If you don't want to use a separate System.out.println for every String, there are other ways as well. Read on! 2. Add a newline character "\n" You can simply append newline character yourself to the strings, and use a single System.out.print command...
Read more