Databases & ORMS

You will learn about databases, the different types of databases and the language used to get data from them.

Brian Long
Senior Developer

What’s a Database?

Think of a database as a super-organized digital filing cabinet. But instead of holding paper files, it stores data—lots of it. This data could be anything from your favorite songs on Spotify, your posts on Instagram, or even your online shopping history. Basically, any app or website you use that involves storing and retrieving information probably uses a database behind the scenes.

Why Use a Database?

Imagine you own a small bakery, and you keep all your recipes, customer orders, and ingredient inventory in a bunch of notebooks scattered around your kitchen. At first, it’s manageable. But as your business grows and you start getting more orders, it gets harder to keep track of everything. Maybe you can’t remember where you wrote down that special cake recipe, or you accidentally double-order ingredients because you lost track of what you already have.

This is where a database comes in. It’s like moving all those notebooks into one super-organized digital system. You can easily find what you’re looking for, update things without flipping through pages, and even search for info in a split second.

Types of Databases

There isn’t just one kind of database—there are several types, depending on what you need. Here are a few of the big ones:

Relational Databases (SQL):

• What they are: These are the most common type and are kind of like spreadsheets on steroids. They organize data into tables with rows and columns. Each table is related to others through keys, which is why they’re called relational databases.
• When to use: If you need to store a lot of structured data that has clear relationships—like customer orders, product listings, or inventory—this is your go-to.
• Examples: MySQL, PostgreSQL, SQLite

NoSQL Databases:

• What they are: These are more flexible and are designed to handle unstructured or semi-structured data. Instead of tables, data is stored in formats like JSON or documents.
• When to use: When your data doesn’t fit neatly into tables or if you need to handle a massive amount of data quickly. Think of social media feeds, big data analytics, or content management systems.
• Examples: MongoDB, Cassandra, CouchDB

Key-Value Databases:

• What they are: Imagine a giant, super-fast dictionary. Every piece of data is stored as a key-value pair, where each key is unique, and it points to some value.
• When to use: Great for when you need super-fast lookups of specific data, like caching frequently accessed data or managing session info in a web app.
• Examples: Redis, Amazon DynamoDB

Graph Databases:

• What they are: These are all about relationships. Instead of just storing data, they store data with connections, like a network of dots and lines where each dot (node) is data, and each line (edge) is the relationship.
• When to use: If you’re dealing with data that’s highly interconnected, like social networks, recommendation engines, or fraud detection.
• Examples: Neo4j, Amazon Neptune

When Do You Need a Database?

You don’t always need a database. If you’re just jotting down a grocery list, a simple note on your phone will do. But let’s say you’re building a website where users can sign up, log in, and save their favorite recipes. Now you’ve got user data, recipes, and probably a bunch of other related info—this is when you’d want a database.

The Right Tool for the Job

Choosing the right database is kind of like picking the right tool from a toolbox. If you’re hanging a picture, you use a hammer. If you’re assembling furniture, you need a screwdriver. Similarly, different types of databases are better suited for different tasks.

• Relational databases are great for structured data that has lots of clear relationships, like an e-commerce site with products, customers, and orders.
• NoSQL databases are awesome when your data is messy, rapidly changing, or doesn’t fit neatly into tables.
• Key-value databases are like that go-to tool for quick tasks—fast and efficient for storing simple pairs of data.

SQL

SQL stands for Structured Query Language, and it’s the standard language used to communicate with relational databases (the spreadsheet-like ones we talked about earlier).

Think of SQL as a universal language for databases. When you want to add new data, find something specific, update existing info, or delete data, you write SQL commands to do it. For example, let's say you had a database that has tables for Users, Comments and Posts:

User

id
first_name
last_name

Comments

user_id
post_id
text

Post

id
text

Select everything from the USER table

SELECT * from User

Select certain columns from the USER table

SELECT id,first_name,last_name from User

Select users with the first name of "John"

SELECT * from User where first_name =='John'

Select users with the first name of "John" in ascending order

SELECT * from User where first_name =='John' ORDER BY first_name ASC

Select users with the first name of "John" in ascending order and group them by the last name

SELECT * from User where first_name =='John' GROUP BY last_name ORDER BY first_name ASC

SELECT table1.column_1, table2.column_2…
FROM table1
INNER JOIN table2
ON table1.column_1 = table2.column_2;​

Many to Many

SELECT User.id, User.first_name, Post.text
  FROM User
LEFT OUTER JOIN comment
  ON User.id = comment.user_id

ORMs (Object-Relational Mappers)

ORMs, or Object-Relational Mappers, are a way to make working with databases easier, especially for developers who aren’t database experts. Imagine you’re coding an app, and you need to interact with a database a lot. Writing raw SQL can get tedious, especially if you’re working with a lot of data and complex relationships. ORMs come to the rescue by allowing you to work with your database using your preferred programming language instead of SQL.

Here’s how it works:

• Objects in Code: In programming, you often deal with objects—think of these as mini-models of real-world things. For example, in a social media app, you might have a User object, a Post object, etc.
• Mapping to the Database: ORMs map these objects to database tables. So, your User object in code would correspond to a users table in the database, and the properties of the User object (like name, email, etc.) would correspond to columns in that table.
• Automatic SQL Generation: Instead of writing SQL queries, you just interact with these objects. The ORM translates your actions into SQL under the hood. For example, when you save a new User object, the ORM generates an INSERT SQL command to add that user to the users table.

Some popular ORMs include:

• SQLAlchemy (Python)
• Hibernate (Java)
• ActiveRecord (Ruby on Rails)
• Entity Framework (C#/.NET)

Why Use ORMs?

Using an ORM can speed up development, reduce errors, and make your code more maintainable. You can focus on writing code in the language you’re comfortable with, while the ORM takes care of the database interactions.

ACID

ACID, in the context of databases, refers to a set of four properties that guarantee the integrity and consistency of data despite errors, system crashes, or other mishaps. These properties are crucial for ensuring reliable data manipulation in database transactions.

Here's a breakdown of each ACID property:

Atomicity: This ensures that a transaction is treated as a single, indivisible unit. Either all the operations within the transaction succeed, or none of them do. Imagine transferring money between two accounts. ACID guarantees that either both accounts are updated successfully, or neither is.

Consistency: This property maintains the data's validity by ensuring that each transaction transitions the database from one valid state to another. It prevents the database from ending up in an inconsistent state due to partially completed operations.

Isolation: This ensures that concurrent transactions do not interfere with each other. Even if multiple transactions are happening simultaneously, ACID guarantees that each transaction is isolated and operates on a consistent snapshot of the data.

Durability: This property guarantees that once a transaction is committed (marked as successful), the changes are permanent and persist even in case of system failures or crashes. The database ensures the updates are written to permanent storage.

ACID transactions are essential for maintaining data integrity in applications that rely heavily on accurate and consistent information. These properties are especially important for financial transactions, inventory management systems, and other applications where data accuracy is critical.

Wrapping It Up

So, to sum it all up: Databases are where you store data in a way that makes it easy to retrieve, update, and manage. Whether you’re building a small app or running a huge company, using a database helps keep everything organized and accessible. There are different types of databases out there, and picking the right one depends on what you need to do with your data.

In the end, databases are the backbone of most apps and websites, quietly working behind the scenes to keep everything running smoothly.

Popular Databases

PostgreSQL

SQLite

MongoDB

MySQL

Popular ORMS

SQLAlchemy

PeeWeee

SQLmodel

Tortoise ORM

Backend as a Service (BaaS)

Supabase

Pocketbase

Firebase

Appwrite

SQLITE Example

customers.py

import sqlite3

# Create a connection to the database (or create it if it doesn't exist)
conn = sqlite3.connect('mydatabase.db')

# Create a cursor object to execute SQL statements
cursor = conn.cursor()

# Create a table named 'customers' with columns for 'name' and 'email'
cursor.execute('''CREATE TABLE IF NOT EXISTS customers (name text, email text)''')

# Insert some data into the table
cursor.execute("INSERT INTO customers VALUES (?, ?)", ('John Doe', 'johndoe@example.com'))
cursor.execute("INSERT INTO customers VALUES (?, ?)", ('Jane Smith', 'janesmith@example.com'))

# Save the changes
conn.commit()

# Read data from the table
cursor.execute("SELECT * FROM customers")
rows = cursor.fetchall()

# Print each row of data
for row in rows:
  print(f"Name: {row[0]}, Email: {row[1]}")

# Close the connection
conn.close()

NO SQL (coming)

Your Turn

import sqlite3
DATABASE = 'data.db'

def get_db():
  conn = sqlite3.connect(DATABASE)
  conn.row_factory = sqlite3.Row
  return conn

def initialize():
  conn = get_db()
  conn.execute("""CREATE TABLE IF NOT EXISTS user(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL
  )""")
  conn.commit()
  conn.close()

def insert():
  conn = get_db()
  conn.execute("INSERT INTO user (name) VALUES ('Raphael')")
  conn.commit()

initialize()
insert()

Resources

Sqlite