Databases and Quick Overview of SQLite
We will be getting familiar with some of the technical terms and then jump into the details of the most widely used & deployed database engine in the world.
Nowadays, you’re getting 2020 wrapped on Spotify or you might have seen the Khatabook ad. The main reason behind the success of these apps is maintaining the databases properly and then performing analysis, serve the user better. The database is present for a long time and the only form of maintenance is the constant change. In ancient times we used to write it on stones than on paper and eventually on digital machines.
Okay, enough with the philosophy🤔, so let’s start !!
Databases
It is a collection of information that is organized so that it can be easily accessed, managed, and updated. You might be thinking of your phone’s contact list or your google photos! Yes, these are also databases.
Broadly speaking databases are mainly of two types relational and non-relational databases
Now let’s dive deep into them one by one!!
What is a DBMS?
A database management system (DBMS) is a software used to store and manage data. It guarantees the quality, durability, and confidentiality of information.
Relational Databases
It is a collection of data items with a pre-defined relationship between them. These items are organized as a set of tables with columns and rows. Tables hold information about the objects to be presented in the database.
How can you forget your attendance sheets? 😬 They can be the finest example of a relational database.
Relational Database Management Systems, or RDBMSs are the most popular type of DBMS.
Some popular examples of standard relational databases management include Microsoft SQL Server, Oracle Database, MySQL, and IBM DB2 but here, mostly we are going to talk about SQLite.
What is SQL?
Structured Query Language (SQL) is the standard language to interact with DBMS. In in simple words its used to talk to the data in a DBMS. Thinks of it as English language.
Note: All the popular RDBMS use slightly different implementation of SQL. But it’s not an issue. People across the globe have their own flavours of English language (different slang, dialect, and accent). But you can easily talk to them if you understand English. Similarly, if you know SQL, you can easily work with all of them.
Non-relational Databases
Often called as NoSQL database stores data in a non-tabular form. Instead, non-relational databases might be based on data structure like documents such as simple key/value pairs, as JSON documents, or as a graph consisting of edges and vertices.
Different Non-relational databases are
- MongoDB
- Cassandra
- Elasticsearch
- Amazon DynamoDB
- HBase
To learn more about relational and non-relational databases refer to this
Now, lets us learn the most widely used & deployed database engine in the world.
SQLite
SQLite is a C-language library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. The lite in SQLite means lightweight in terms of setup, database administration, and required resources
Why SQLite is so popular?
- Self-Contained means it requires minimal support from the operating system or external library. This makes SQLite usable in any environment especially in embedded devices like iPhones, Android phones, game consoles, handheld media players, etc.
- Zero-configuration Because of the serverless architecture, you don’t need to “install” SQLite before using it. There is no server process that needs to be configured, started, and stopped.
- Maintenance: It’s just a single file. So, a normal copy-paste is enough to create a back-up.
- Transactional: All transactions in SQLite are fully ACID-compliant. It means all queries and changes are Atomic, Consistent, Isolated, and Durable.
Many high-profile companies like Apple, Facebook, Google, etc. use SQLite in their products.
Limitations
- Database size is restricted to 2GB in most cases. It might perform well if we use a client/server database engine that spreads its content across multiple disk files, and perhaps across multiple volumes.
- SQLite is used to handle low to medium traffic HTTP requests to be specific it handles about 400 K to 500k HTTP requests per day, about 15–20% of which are dynamic pages touching the database
- It’s row first, so column operations are pretty difficult to perform. For example, adding 10 to a particular column of all the selected records.
Installation of SQLite
To download the setup, open the download page of SQLite.
1.In the case of windows, you have to download this zip file.
2.Create a new folder eg D:\sqlite and then extract the zipped files to this folder.
3. Open the terminal and navigate to your sqlite folder(eg cd\D:\sqlite) and type sqlite3 and press enter, you should see the following output:
- Youtube Video By Programming Knowledge https://youtu.be/wXEZZ2JT3-k
Umm… When to use which Database 🧐
Wouldn’t it be a bit easy if you’re able to figure out what type of database you should be adopting? Though there is no particular rule or formula to decide on the type of database to be used but there are few factors that should be taken care of while choosing the one.
Curious to know about these factors? I know you are :)
Consistency, Availablity, Partition and tolerance combinely are called as CAP !!
We will try to understand this theorem with one simple example. Say you are building a phone directory app but you’re confused about the database to be used so let’s figure it out together. Phone directory app has:
- Scale -limited
- Consistency -Eventual is fine
- Availability requirements: not mission-critical
So, either of the MySQL or SQLite will work fine.
To get a more clear intuition of selecting the database, we will look upon the example of recommending movies to the user. Let us say you are working for some huge company with a massive scale where
- Something needs to vend this data to your web applications
- Downtime is not tolerated
- Must be fast
- Eventual consistency is Ok as it just reads.
Therefore, by looking at the CAP theorem, we can guess whether the Cassandra or Dynamo Db is a better fit.
If you want to learn more about the CAP theorem then visit this by IBM Cloud Education
Now it’s on you to think, analyze, and then comment on which database management tool you’ll use while designing the Stock trading System.
Conclusion
Official documentation mentions that you can use it almost anywhere from your embedded devices and IoT to websites provided low to medium traffic websites.
In case of very large datasets, high concurrency client/server RDBMS works much better then sqlite
So, it totally depends upon the requirement of the user when to use which database engine.
If you are looking for the initial push to start learning SQLite then read this blog (Highly recommended)
And lastly, if you are directed here from Ankur Singh’s “SQLite: Light & Powerful” blog then do write ANKUR OP in the comments!!😂😂
References:
https://sqlite.org/whentouse.html
https://www.sqlite.org/index.html
https://docs.microsoft.com/en-us/azure/architecture/data-guide/big-data/non-relational-data
https://medium.com/swlh/5-reasons-to-use-sqlite-the-tiny-giant-for-your-next-project-a6bc384b2df4
