Introduction to Databases

Great... If you are here then we have:

• discussed some backend jargon
• learnt some JavaScript and wrote some projects
• created an api and gradually added to it

At this point what we need is persistence. Also known as storage. The means of storage that we want, in this case, is a database. We want to save the user data that we create and we want to also save the expenses that we create and update. For that to occur, there are a couple of ways to do that. These include but are not limited to:

• Memory: The data is assigned to a (global) variable and manipulated to achieve our storage goal. However, it is that the data stored in memory are cleared when the server restarts. We will face what is termed as Data loss. If you have heard of the phrase, "Time is money" then here, "Data is business".
• File: Saving (writing) data into a file is quite better than in-memory. The content of the file isn't lost when the server restarts. However, that storage and its management via file becomes inefficient when there are multiple write operations. This means that when we have several users, not that the users are accessing the same data but they are managing their data, simultaneously, then the is a case of we facing data loss due to simultaneously writing to the same file. Can't we use several files (with a schema - structure)? There is an issue with creating relations. We want to map or associate some set of data with another. With file, as a means of persistence, this may become inefficient.
• Database: A database is better than a file. We can create and manage complex relationships.

Database Concepts

• Database: A database is used to organize and manage (store, retrieve, update and delete) the data our applications depend on. A database can be seen (imagined) as a folder or a book. Our application will use a database to organize (in a well-structured manner) our users' data and expenses. We can create a simple relationship between a user and an expense.
• Tables: A table is a (structured) collection of data organized in rows (values) and columns (keys/properties/fields). If a database is a book, each table is like a chapter or page containing related information.. If a database is a folder, a table will be a file. We will have tables that will represent users and expenses. A table has a name. In a table, there are rows and columns.
• Column: A column is (or represents) a property/key/field in a given table. For example, for the user we had in the expense api, it had an id, email and password properties. These become columns in a table. Practically a column is the same as a property or field.

• Row: A row in a table represents an instance of a record of the said table, having values that map to the columns they fall under. For example, for the user table, each row represents a "user" record.

  id	email	password
  c96b08b8-f92f-4e27-8f04-c4f3604907d6	[e-mail]	passwordhash1
  decec694-6d97-4199-8544-f4bbe7ea753b	[e-mail]	passwordhash2
  b58bceeb-4041-4a50-b204-85caa2ad6c20	[e-mail]	passwordhash3

• Primary: In a database, there is a column which is used to uniquely identify a row. This column or columns are referred to as a primary key.

• Relational Database: This is a database that is designed to favour creating and manipulating data with relations in mind. Examples of these are, but not limited to PostgreSQL, MySQL, SQLite, etc (etc is not a database). We might end up using SQLite and PostgreSQL. These databases favour or are defined using rows as records and columns as properties.

• Document Database: Also known as No-SQL database, is a database designed with data format flexibility in mind. The data format is non-structured unlike in a relational database, for which it is called a No-SQL database. Practically, it more or less semi-structured. Examples of these are but are not limited to MongoDB, Redis, CouchDB, Cassandra, etc. We might end up using MongoDB and Redis.

SQL

SQL stands for Structure Query Language. It is the language of Relational databases. We will start with SQLlite and whatever we would learn here is "transferable". So, do not stress and enjoy the journey. You would want to check out this article, 7-best-sql-books-for-beginners-54gi. There are some great books and resources. Check them out and finally, there is Beekeeper Studio, a database client. We will use it to view and sometimes manage and interact with the databases we create. There is a community edition so read the docs and install whatever version of the applications you want.

In the next section, we are going to create a cheat sheet that we can reference when we are developing. Hold on tight.

Comments

[Nevo David]

been tinkering with this kinda stuff for a bit now - persistence always throws me off. you think there's ever a time where simple files make more sense over spinning up a full db?

[Michael Otu]

There are cases that using a file over a full db is better....

• Once we were integration an api and there was some static values that the front end and the 3rd relied on... We mapped and placed those static value into a file... exported it (export default 🥳) and via an endpoint made it available for the front end
• Mostly I write my crash logs into a file
• I think we can use a file in cases where there is no multiple writing to... in which case I'd use sqlite (file based db)
• I usually use sqlite when prototyping... a lot... I will use sqlite over a file in some cases where there is a single user or I am building an app that I am the only user and the app requires a backend api which need some data persistence... a file will do but using sqlite is better
• I remember there was a feature we implement but we have to create a file afterwards... The file served as flag that this action took place...

In all, I used sqlite in cases where it seems a fully db (postgres, mysql, mongodb, etc) isn't required rather than a file