Getting started with SQLite — Pros, cons, and tips

What is SQLite?

SQLite is one of my favorite go-to SQL languages when I have limited time and a lot of data to go through. It is a relational database management system similar to MySQL, PostgreSQL, Oracle, or Microsoft SQL Server.

That said, it differs from all other SQL languages since it uses a dynamic type system, meaning a value stored in a column determines its data type, and not the column’s data type. You can also interact with an SQLite database using Java, Python, PHP, and Node.js.

I’d like to share some useful tips and tricks for using SQLite. While the language has big advantages, it also has some limitations. Let’s review the pros and cons!

Pros of SQLite

The biggest advantage of SQLite is ease of use — you can set it up on any machine (even a cellphone!), and it doesn’t require much configuration. Setup is fast and easy, and using the language is simple. You don’t need to worry about a data center or a powerful network, and it runs very fast.

SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter “.help” for usage hints.
Connected to a transient in-memory database.
Use “.open FILENAME” to reopen on a persistent database.
sqlite> .separator ‘,’
sqlite> .import SQLite_Testdata.csv test
sqlite> select country, count(user_id) n from test group by country limit 5;
AU,2
FR,2
ID,2
IN,3
Sqlite>

In the code above I use the SQLite3 command-line shell application, and I’m constantly amazed at how easy it is to import a file with any type of large dataset and query against it. After you open a file you already can query it without declaring specific data type for a column.

Another pro of SQL is that a column in SQLite can store different data types. Therefore, if your dataset hasn’t been cleaned, you still can open and query it. Be mindful with sorting the values though, because different data types might affect the order of your results.

To read the full article and learn more about SQLite, click here: https://www.logic2020.com/insight/tactical/getting-started-with-sqlite?utm_source=social&utm_medium=Medium&utm_campaign=SQLite