Back-end Engineering Articles

I write and talk about backend stuff like Ruby, Ruby On Rails, Databases, Testing, Architecture / Infrastructure / System Design, Cloud, DevOps, Backgroud Jobs, and more...

Twitter:
@daniel_moralesp

2019-10-11

Introduction to Databases

So far, we've been studying the details of Ruby and Oriented-object programming. All of this with the idea of starting working with Rails. We are one step closer to that. But before we begin studying Rails, we have to understand a bit about Databases. 

Why are databases so essential for us right now?

Is simple. With Rails as a back-end framework, we'll be dealing with data all the time, and as we saw in the last blog post about the communication between Clients and Servers, we have to deal with persisted data on the server-side. 

In that blog post, we did an example about launching our own e-commerce. Just think about that example for a while: if you have to store inventories about the t-shirts you're going to sell, you have to persist that data in someplace. The right place to store that data is in a database, and that's the importance of knowing about it. Then, you have to create, read, update or delete that data.

CRUD

CRUD stands for Create, Read, Update and Delete. As you can see, these are the main actions we have to do with our database. In addition, CRUD is sometimes used to describe user interface conventions that facilitate viewing, searching, and changing information using computer-based forms and reports. 

CRUD is also relevant at the user interface level of most applications. For example, the primary storage unit in an eCommerce software is an individual product. As a bare minimum, the software must allow the user to: 

  • * Create, or add new products
  • * Read, retrieve, search, or view existing products
  • * Update or edit existing products
  • * Delete, deactivate, or remove existing products

Types of databases

There are tons of different kinds of databases. It's actually an entire field of study. Just to mention a few, we have Relational databases, Non-relational databases, key-value databases, graph databases, document databases, and so on. This is an excellent resource to see more details about each one of them,

The type of problem we'll be dealing with in Rails are primarily Relational databases. But what is that?

Relational databases

A relational database is a collection of data items with pre-defined relationships between them. These items are organized as a set of tables with columns and rows. Tables are used to hold information about the objects represented in the database. For example, each column in a table contains a certain kind of data, and a field stores the actual value of an attribute. 

The rows in the table represent a collection of related values of one object or entity. For example, each row in a table could be marked with a unique identifier called a primary key, and rows among multiple tables can be made related using foreign keys. This data can be accessed in many ways without reorganizing the database tables themselves. You can find more info here.

As you can see, here we have many new different terms. What you can imagine here is a spreadsheet: 

  • * Database == Folder containing all the Tables
  • * Tables == Spreadsheet file



  • * Columns == Spreadsheet column
  • * Rows == Spreadsheet row
  • * Objects == Data inside each row that belongs to a column
  • * Primary Key == A spreadsheet column with an unique number as an identifier
  • * Foreign key == A spreadsheet column with an identifier that belongs to other tab or file
  • * Related values == Different spreadsheet tabs (or files), related between them





Later we'll be clarifying these concepts and doing exercises. So let's recap.

  • * A database is a set of data stored in a machine.
  • * A relational database is a type of database that uses a structure that allows us to identify and access data concerning another dataset within the database.
  • * It is organized in tables

Note: So far, we've been using spreadsheets as an example because if this is your first approach to database concepts, it is easier to imagine the tables, columns, rows, and other ideas right from there. However, databases are usually run using a database management system. 

Database Management Systems (DBMS)

DBMS works a little bit like spreadsheets but with many advantages and specializations. DBMS are software systems used to store, retrieve, and run queries on data. A DBMS serves as an interface between an end-user and a database, allowing users to create, read, update, and delete data in the database.

DBMS manages the data, the database engine, and the database schema, allowing users and other programs to manipulate or extract data. This helps provide data security, integrity, concurrency, and uniform administration procedures.

DBMS optimizes data organization by following a database schema design technique called normalization, which splits a large table into smaller tables when any of its attributes have redundancy in values. As a result, DBMS offers many benefits over traditional file systems, including flexibility and a more complex backup system.

Database management systems can be classified based on various criteria, such as the data model, the database distribution, or user numbers. The most widely used types of DBMS software are relational, distributed, hierarchical, object-oriented, and network. More info here.



Examples of DBMS

A wide range of database software solutions, including enterprise and open source solutions, are available for database management. Here are some of the most popular database management systems:

  • * PostgreSQL: The open-source DBMS solution gets attention because of the invigorating indexing and configuration options. PostgreSQL is ideal if your daily business activities require importing or exporting data. As of now, PostgreSQL supports Python and JSON programming languages. Although it is a relational database solution, users can create NoSQL databases. Besides, the open-source community has developed a wide array of plug-ins to boost the software's functionality.

  • * MySQL: MySQL is a high-speed data processing and data productivity tool with comprehensive features. The device is designed to increase the security and scalability of your databases. A reliably cost-effective tool offers technical support and counteracts potential risks. Furthermore, high-volume business sites can deploy business-oriented complex MySQL applications.

  • * SQLite: SQLite is designed to cater to small and medium-sized businesses (SMEs). Its light structure and layout design help users easily store and manage data. In addition, the SQL engine of the tool is highly reliable and self-contained. In fact, the DBMS program is available on several mobile applications.

  • * Microsoft SQL Server: Microsoft's SQL Server is one of the most effective DBMS in existence. The free tag of the tool certainly attracts a large user base. Its custom-built graphical integration of the best database designs has saved users valuable time for years. Similarly, the diagrams that you can make with the help of this tool can be easily added to a new or existing project library.

As you can see, we have different options, but it is fair to say that we'll be working primarily with PostgreSQL in Ruby on Rails. 

Installing PostgreSQL

Now it's time to install the DBMS selected in your local machine. In this case, PostgreSQL. This installation will depend on the operating system you have. So I encourage you to search on Google for the following keywords:

  • * Install PostgreSQL in Linux
  • * Install PostgreSQL in macOS
  • * Install PostgreSQL in Windows

Of course, you'll see different tutorials and different ways to install it. For instance, if you have Ubuntu, this tutorial can help you. There are two ways to install it, any of them works fine:

The same thing happens for other operating systems. For instance, you can find how to do it in Windows. As you can see, there are different steps, basically following the pop-up window that guides you through the whole installation.

In any operating system, the important thing is to connect to PostgreSQL via a console like this:

$ sudo su - postgres
[email protected]:~$ psql
psql (13.3 (Ubuntu 13.3-1.pgdg16.04+1), server 9.6.22)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
postgres=#



You can see that we connect and access PostgreSQL via the console. Everything needs to be done using the console when we need to create a new table or new rows. If you are not familiar with the console, probably you'll be fighting with it. However, my advice is to be comfortable with it because we'll be using console interfaces a lot as developers! But if you resist doing this, we have a visual option: pgAdmin

pgAdmin

It's a trendy open-source platform dedicated to PostgreSQL and has graphical user interface administration tools to manage your relational databases. Some features include a query tool for SQL statements and importing/exporting CSV files.

pgAdmin

What’s Covered:
  • * Download pgAdmin & PostgreSQL
  • * pgAdmin Overview
  • * Connect an Existing Database(AWS)

If you want to start working with it, this is an excellent resource to start.

If you follow that post, it is easy to do everything from the UI, like creating tables data or even monitoring other things like performance. 

I think we have enough context about databases, and next post, we'll be creating our first database and tables. 

I hope you learned a lot.

Thanks for reading
Daniel Morales





@daniel_moralesp