Rails and Postgres

brvarner — Thu, 06 Jun 2024 21:10:09 +0000

I'm slowly grasping the connection between Rails and Postgres. I'm starting to feel like this is the key to the Model-View-Controller system, but I'm still working on fully wrapping my head around this bad boy.

Luckily, I took copious notes during most Postgres/Rails lesson, and you can read them here.

Getting Started

You must first download PostgreSQL, and follow the prompts to learn its console line commands.

You must start by initializing a database and then creating a table.

When you create a table, you must include the fields and data types that all of your entries will have. Here's an example from the exercise in the course, where we created a contact book:

CREATE TABLE contacts (
  id SERIAL PRIMARY KEY,
  first_name TEXT,
  last_name TEXT,
  date_of_birth DATE,
  street_address_1 TEXT,
  street_address_2 TEXT,
  city TEXT,
  state TEXT,
  zip TEXT,
  phone TEXT,
  notes TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

The id field will always be a SERIAL PRIMARY KEY to indicate it should auto-increment and that this will be the primary key of your table.

After creating your table, you can launch it to prime it for editing/access with the psql command, or by using rails db.

To link your Rails project with your database, you have to edit your database.yaml file. Change rails_template_development on line 27 to your database's name and it'll find it on your computer.

Also, you can access Rails' built-in database GUI by launching bin/server and navigating to rails/db in your project. From there, you can perform CRUD operations on data or observe.

Models

You must interface with your database via a Model, which Rails provides as an easy way to structure your database actions. Models are Ruby classes that "talk to the database, store and validate data, perform the business logic, and otherwise do the heavy lifting".

These models inherit methods from Rails's built-in ActiveRecord class, which lets them do many different moves. Each model represents a single row of data, so they're named singularly after the name of the table (i.e. a model named Contact for a table named contacts). This naming convention helps Rails automatically locate the table that the model represents.

We can operate on the data several ways once we complete setup, but I'm calling it on Rails/Postgres until we do it next week as a class.

Database Design

brvarner — Thu, 30 May 2024 21:59:57 +0000

Today we focused on database design, specifically exploring the concepts that drive relational databases.

What's a relational database?

It's a collection of tables storing different data pieces that refer to one another to form a complete picture of something. By referencing other tables, we can stick to one value per field design principles while adding organized complexity to our entries.

What?

Today, we used a film database as an example. This database had tables for films, actors, and directors.

You can't just include a list of every actor in a movie in the films table, so you need a way to keep track of each film's cast. This is where a one-to-many join table comes into play.

You can create a table where each record is a film ID, an actor's ID, and the name of the role they played. A query to this table can then return each actor ID matched with a single film's ID and provide a comprehensive list of every actor in that film.

This way, you can keep track of the dozens of actors that make up a film's cast without bogging down every table.

Conclusion

This form of database design applies to SQL, it's been a while since I've studied SQL, so it was nice to get a refresher.