At first glance databases seem like a pain to learn.
It doesn’t seem that important or “cool” to learn as you would a programming language.
But, you will find there are some important use cases for databases. After implementing these use cases you likely would wonder, “How in the world did I program without databases?”
Databases are not just for large applications. They are for anyone who wants to save data.
Right now, without saving on disk, when you restart an app all the data/variables saved within the app disappear.
As a remedy, without database programming, you are likely saving your application data to a local file.
Local file storage is the only way to easily save data between app restarts.
This is the primary use case of a database; to save information between app restarts.
What is crazy, databases can be saved on a computer that is not local to you.
It can be saved in Germany, for example, if you are renting a remote computer in that country.
All the way from home your application can ask for data from Germany while it is running.
What is really awesome, even in Germany the response time or time to get the data is in milliseconds.
Getting data from across the internet is extremely fast.
When we have a database, multiple applications can read/write to the database, still across the internet.
You are able to have consistent data between multiple applications and still run the app at nearly identical speed.
If you have multiple pieces of software and you need to have them sync data, databases are the solution.
Of course, a local database or a nearby database will be faster.
Every database call has a differing response time. If you make lots of database calls for small amounts of data from across the globe on lets say a website, there will be inconsistencies.
If, for example, you ask for pictures from a database and call every picture one a time, you will see the website calls for the pictures one at a time, in order of the calls, sporadically.
100ms for one, 300ms for another, 120ms for the next image. This is essentially how it works.
From a local database you will see 30ms, 50ms, 80ms, 35ms response times, as an example.
But, this is the best thing about databases. Any data can be saved.
Data can be; player files in an MMORPG, healthcare records for a hospital, blog posts on let’s say WordPress or pretty much anything else.
Btw, that is how this blog post is saved.
Table of Contents
Database vs file structure
A database is a file structure. Just like a folder on your local PC, it stores data in some structured way.
The difference between a database and a computer directory is just; it is remotely accessible (which you can do with a file structure), it has a built-in software with its structure, and it has a language for calling to the database.
The built-in software of the database is the main difference between a file structure and a database.
The software allows you to ask for data by some pattern, set filters and change the data inside.
The software of a database is stuff like, MySQL, MongoDB, Amazon S3, PostgreSQL, etc.
Here is an example of a filter and search:
SELECT * FROM hospitals WHERE hospital_name = 'St. Peters Hospital';Let us say you have data on a list of hospitals and you want to ask for information about a hospital ‘St. Peters hospital’.
This is an example statement where we are asking for that information from the built-in software of the database.
You can’t do this with a file structure. Folders and files cannot be filtered no where near as precisely as a database.
Databases have a URL/URI
In reality, every device connected to the internet is connected together.
This means, if it can access the internet, you can access the database. With the right credentials of course.
If you knew the password and username for any database directly connected to the internet, you could access it.
How would you do this?
Well we use a URL, just like a website. Okay, okay, if you are a developer you would get upset at that simplification. Let me explain.
A URL is a child type of a URI. A URI is an internet address and a URL is just a type of internet address.
So, if we look at a “URL” aka “URI” of a database address on the internet, it would be something like this:
postgresql://username:password@localhost:5432/database_nameIf we were to fill in that template with credentials, it would look like this…
mongodb://Jesse:lovetocode@72.158.123.1:27017/mydatabaseWith “Jesse” as the username and “lovetocode” as the password we are accessing a remote database on IP address 72.158.123.1 on port 27017 with the database name “mydatabase”.
With these credentials anyone can access your database.
Often, companies hide the database behind a local network for this reason.
Query languages
When you access data from a database, you use a query language. Query, meaning an ask for data from the database.
It may seem complicated and not worth learning. However, once you have gotten to a point where you can use it easily, it is super freakin cool!
You can organize your data in some way that your application accesses it in ridiculously organized ways.
Take a look at a query statement like this one:
SELECT * FROM patients
WHERE doctor = 'Dr Andry'
ORDER BY 'diagnosisID' DESC
OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY;Look, I know this looks complicated but hear me out. You can’t do this with a file structure.
Here we are filtering patients by doctor, ordering by their diagnosis in a descending manner and only asking for every other patient.
I mean, BOOM man. This cannot be done any other way but through a database or some long winded script through a file structure.
This is a query language, a language for asking for data.
What is a database client?
A client, or a DBMS is a software which allows you to see and ask for data directly on screen.
Take a look at this example of a database client:
This is an auction house for an MMORPG. We are essentially saving every item in the auction house by itemID and Owner of the item.
We can use the database query language to get this data and view/manipulate the data live from the client.
And, as we change the data in-game I can see it changing in the client.
Freakin cool right?
Here is an example query:
darkan-server.grandexchange.find({
"selling": true,
"price": { $gte: 50000 }
})Here we are asking for selling items with a price “greater than or equal” to 50,000 gold.
And we can change the price of these items to 50,000 gold using the query language:
darkan-server.grandexchange.updateMany(
{
"selling": true,
"price": { $gt: 72000 }
},
{
$set: { "price": 50000 }
}
)And we would see this reflected in the Mongo Client.
No-SQL vs SQL
The two main differences between No-SQL & SQL are the structure of the database and the query language.
The structure of SQL databases are pretty similar across database software.
There are differences between say MySQL and PostgreSQL but the foundational ideas between them is the same.
You use tables within databases to access data in a structured way.
Tables are related to other tables.
We can get into SQL or you can look it up online. The main thing I want to tell you, is that between SQL softwares most of the essentials are the same.
No-SQL, on the other hand, can be completely different across database software.
MongoDB is nothing like Amazon S3 or Redis, or the many others out there.
For No-SQL the query language is different, the data structure is different and there are all kinds of ways and reasons to set them up.
So, basically SQL is a more standardized way to setup a database and No-SQL is a mess of choosing what software and client to use.
Should I learn No-SQL or SQL first?
Loads of companies use SQL and once you learn one SQL software you learn mostly all of them.
For this reason, I would always say learn SQL first. It doesn’t matter which SQL software you choose, just use the one in the tutorial, course or class you are taking.
What is cool, query languages are the same across all programming languages.
Take a look at this example of SQL in Python & C++:
import mysql.connector
db_connection = {
"host": "72.158.123.1",
"user": "Jesse",
"password": "lovetocode",
"database": "United Healthcare",
}
conn = mysql.connector.connect(**db_connection)
cursor = conn.cursor()
query = "SELECT * FROM docters WHERE hospital_name = 'St. Peters Hospital'"
cursor.execute(query)
results = cursor.fetchall()
for result in results:
print(result)Here we are asking for a query of which doctors exist at St. Peters hospital. Once we get this data we convert into data inside the programming application.
We then print the resulting doctors files.
Now take a look at the same thing in C++:
#include <mysql_driver.h>
#include <mysql_connection.h>
#include <cppconn/driver.h>
#include <cppconn/resultset.h>
#include <cppconn/statement.h>
int main() {
// Replace these with your actual database connection details
const std::string host = "72.158.123.1";
const std::string user = "Jesse";
const std::string password = "lovetocode";
const std::string database = "United Healthcare";
sql::mysql::MySQL_Driver* driver;
sql::Connection* con;
driver = sql::mysql::get_mysql_driver_instance();
con = driver->connect(host, user, password);
con->setSchema(database);
sql::Statement* stmt;
stmt = con->createStatement();
const std::string query = "SELECT * FROM doctors WHERE hospital_name = 'St. Peter''s Hospital'";
sql::ResultSet* res = stmt->executeQuery(query);
while (res->next()) {
std::string name = res->getString("name");
std::cout << "Name: " << name << std::endl;
}
}Now, yes, I agree its longer and seems more complicated in C++. But, that is because of the C++ language and not SQL.
It is essentially the same thing and if you learn C++ just as well as you know Python, you won’t see the difference between the two.
What is super cool, you can convert these code statements into functions and do all kinds of neat things automations with a database, in any programming language.
Anywho, I hope you learned something…
Happy coding!
Resources
SQL Tutorial: https://www.youtube.com/watch?v=5OdVJbNCSso&ab_channel=BroCode
