The Differences Between SQL vs SQLite — Massive/Centralised versus Lightweight

SQL vs SQLite and what aspects are important

Picture via KinsgwaySoft

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

SQLite official logo

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

Picture via Propatel

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.

Picture via madooei.github.io

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.

Illustration Made By Author

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!

--

--

--

Are you someone who is curious about programming languages and who wants to start coding? Are you a beginner or intermediate coder? Well if all the answers are positive, you have come to the right place — Welcome to the Arnold Code Academy. Newsletter: https://bit.ly/3g9mHQ2

Recommended from Medium

How to start an application in remote docker containers using .NET Core

[Searching] Binary Search — Recursion/Iteration(Full Implementation/C++)

Jewel Crafting in Earth2

WebAssembly, Sounding More Confident, & More…

YieldShield — Summary for the outgoing year

Why you should not use Firebase and Google Cloud?

Custom Banking apps,with Low code composable banking

About technology

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Arnold Abraham

Arnold Abraham

German software engineer who helps aspiring coders to learn software development enjoyably where fun & emotions come first to learn on the fly 😉 arnoldcode.com

More from Medium

Git Workflow Comparison

Level Up as a Software Engineer by Writing a Chess Engine

Towards Cleaner Code — A Practical Example

You don’t need unit tests