Aug 17, 2021
The Differences Between SQL vs SQLite — Massive/Centralised versus Lightweight
SQL vs SQLite and what aspects are important
Can you name the differences between lightweight & massive solutions? If not you should definitively read on!
Relational databases and SQL (Structured Query Language) aren’t proprietary. The market is full of possible solutions to choose from
- MySQL
- Microsoft SQL Server
- Oracle
- PostgreSQL
- MariaDB
- SQLite Studio
- DB Browser for SQLite.
It could be possibly any of them but instead of listing the differences. I take the better approach of finding out your use case and then choose the right product!
I asked you about the differences between lightweight and massive solutions.
Lightweight, the One With Less Fat
Lightweights are the simple ones, called SQLite instead of SQL. Storing a database into a single file (file with the ending of *.db) and having reduced complexity in terms of working with them.
The database is accessible by a small number of users simultaneously.
Further reasons are if you just want to quickly set up a database or just learning about SQL and the topic of relational databases. They are a good place to start (almost no overhead).
There is no server to be set up, and for some software, you don’t even have to install a program. They tend to be very nimble and you can share your database with others easily.
It is just a file!
I can prefer SQLite Studio because it is intuitive and doesn’t even need to be installed. Just download it and run your first query.
I made an SQL course, where I use SQLite Studio and kidnap you, like over 10k before you, into a pirate adventure.
Besides, SQLite offers you virtually no size limitation and works best in environments used by one person (or a few people). For example, an offline app that collects a lot of data locally.
Ideal to learn SQL because of the ease of “installation” of SQLite Studio or DB Browser for SQLite
On the downside, they break down when multiple people access/edit simultaneously. If you have such a case, then better use the centralized solution works better here.
Massive (Centralised) — The 200 Pounder
Massive solutions use the entire SQL complexity but in the core, SQLite and SQL are the same.
As long as you stay at default queries. Dig really deep and want more convenience in terms of query creation you are better of using the full one.
Full SQL solutions run on servers to support thousands of users simultaneously. It should be used by applications with accesses of users happening parallel to this database.
Because of that, you need a server to handle such high-volume traffic efficiently.
Some classical programs you can hit up are: MySQL, Microsoft SQL Server, Oracle, PostgreSQL, MariaDB
Note: „SQL“ is the language to query those databases, the phrase is inside the names for marketing reasons and some are free, some require commercial licensing.
As I said, they aren’t so easy to use or to operate with. You need to set up a server and installing one of the mentioned products on your own computer turns it into a server & other computers being connected to this database are called clients.
Clients send SQL statements to request specific data sets. The server then processes all requests and returns the answers. It is a classical Client-Server setup — Client requests, the server responds.
Turn your private PC into a server is possible but large traffic needs specialized computers a.k.a servers. Servers are optimized for those specific tasks. Obviously high amount of traffic and parallel task handling.
Having a server means also maintaining it sooner or later. This can be outsourced to an IT department.
Maintaining the server is one part, on the other hand, having a lot of traffic means also a lot of data and then another question comes up: Who should maintain the database itself? — This is a question you have to answer also.
Conclusion
Whereas lightweight databases are for single users or a small number of users, full SQL solutions are massive and need a server to be run on.
The queries and the experience of the topics are almost the same for both.
That’s, because both work with SQL, that’s a fact. Both got tables to be interacted with and all SQL products got nuances in their way of working with them but the overall way of operation is the same.
If you do need a centralized database MySQL is free/open source and a good starting point.
Companies like Facebook / Google, eBay, Twitter, and multiple other named companies are using MySQL.
If you enjoyed the examples inside this article, a treasure chest full of them is awaiting you if you subscribe to my newsletter to get an hour-long taste of freedom!
The SQL Pirate Adventure, where you set sails to SQisLand to meet some shady characters and have a pleasurable time together!
Already over 10000 Pirates have visited SQisLand with great reviews!
