Sakila database + Postgres + Rails application

🐘 Postgres · 💎 Ruby 3.3 · 🛤 Rails 7 · ⚡️ Hotwire · Phlex

---

Rails application based on a schema and data of the Sakila Sample Database, also used in a great book Learning SQL by Alan Beaulieu

Postgres-based source for inspiration: fspacek/docker-postgres-sakila

Motivation

The goal was to create a Rails/Postgres application with a schema & data from the Sakila Sample Database, to practice both SQL from the book and Rails with ActiveRecord.

Setup

bundle install
rails db:create
rails db:migrate
rails db:seed

Important notices

ⓘ Important: table names are singualar (actor, store) in the original database, but plural in the project (actors, stores)

ⓘ Important: primary keys in the original database look like actor.actor_id, and in this project they look like actor.id.

ⓘ Important: tables only have last_update field in the original database, but in this project I use created_at/updated_at.

ⓘ Important: geospacial columns (like store.location in the original database) and binary columns (like staff.picture in the original database) are not implemented, at least yet.

ⓘ Views and functions/triggers are dumped to schema.rb with the help of scenic and fx gems. Migrations look nicer, but I don't like external files for each SQL piece 🥲.

Before I found out about those two, I created by own SQL view migration/model generator, it's here: lib/generators/sql_view

TODO:

• Procedures / functions:
  • inventory_in_stock
  • inventory_held_by_customer
• Triggers (not sure about doing it yet)
• Tables & Rails models
• Views
• Rewrite SQL views using scenic-views/scenic
• For functions and procedures use teoljungberg/fx
• Check ON UPDATE and ON DELETE for existence

Step by step implementation

1. Create Actor
2. Create Category
3. Create Language
4. Create Film
5. Create FilmActor
6. Create FilmCategory
7. Cross-reference Films with Actors, Films with Categories
8. Create Country
9. Create City
10. Cross-reference City and Country
11. Create Address
12. Cross-reference Address and City
13. Create Customer
14. Cross-reference Customer and Address
15. Create Inventory
16. Set Language.name limit to 20
17. Create Rental
18. Create Payment
19. Cross-reference Payment and Customer
20. Create Store
21. Create Staff
22. Add manager_staff to Store
23. Add original_language_id to Film
24. Rename original_language_id and rental_rate columns
25. Remove uniqueness index from city
26. Add store_id to Inventory
27. Add staff_id to Rentals
28. Add staff_id to Payments
29. Fix Film <-> Actor associations in models
30. Fix Film <-> Category associations in models
31. Fix Film <-> Language associations in models
32. Fix Store <-> Staff associations in models
33. Cross-reference Store and Customer
34. Cross-reference Inventory with Store and Rental
35. Cross-reference Payment with Rental and Staff
36. Cross-reference Rental with Customer and Staff
37. Reference Store in Address

Create Actor

First, generate: rails generate model Actor

Then in migration:

  def change
    create_table :actors do |t|
      t.string :first_name, null: false
      t.string :last_name, null: false

      t.timestamps
    end

    add_index :actors, :first_name
    add_index :actors, :last_name
  end

Then in model:

class Actor < ApplicationRecord
  validates :first_name, presence: true
  validates :last_name, presence: true

Create Category

First, generate: rails generate model Category

Then in migration:

  def change
    create_table :categories do |t|
      t.string :name, null: false

      t.timestamps
    end

    add_index :categories, :name, unique: true
  end

Then in model:

class Category < ApplicationRecord
  validates :name, presence: true, uniqueness: true

Create Language

First, generate: rails generate model Language

Then in migration:

  def change
    create_table :languages do |t|
      t.string :name, null: false

      t.timestamps
    end

    add_index :categories, :name, unique: true
  end

Then in model:

class Language < ApplicationRecord
  validates :name, presence: true, uniqueness: true

Create Film

First, generate: rails generate model Film

Then in migration:

  def change
    create_enum :rating, %w[G PG PG-13 R NC-17]

    create_table :films do |t|
      t.string :title, null: false
      t.text :description
      t.date :release_year
      t.integer :rental_duration, null: false
      t.decimal :rantal_rate, precision: 4, scale: 2, null: false
      t.integer :length, limit: 3
      t.decimal :replacement_cost, precision: 5, scale: 2, null: false
      t.enum :rating, enum_type: 'rating', default: 'G'
      t.string :special_features, array: true

      t.timestamps

Another migration to reference language_id in Film:

class AddLanguageToFilm < ActiveRecord::Migration[7.0]
  def change
    add_reference :films, :language, foreign_key: true

Then in Film model:

class Film < ApplicationRecord
  has_one :language

  validates :rental_rate, :rental_duration, :replacement_cost,
            :title, :language_id, presence: true

Create FilmActor

First, generate: rails generate model FilmActor

Then in migration:

  def change
    create_table :film_actors do |t|
      t.references :film, null: false, foreign_key: true
      t.references :actor, null: false, foreign_key: true

      t.timestamps

Then in model:

class FilmActor < ApplicationRecord
  belongs_to :film
  belongs_to :actor

  validates :film, :actor, presence: true

Create FilmCategory

First, generate: rails generate model FilmCategory

Then in migration:

  def change
    create_table :film_categories do |t|
      t.references :film, null: false, foreign_key: true
      t.references :category, null: false, foreign_key: true

      t.timestamps

Then in model:

class FilmCategory < ApplicationRecord
  belongs_to :film
  belongs_to :category

  validates :film, :category, presence: true

Cross-reference Films with Actors, Films with Categories

In models/actor.rb:

class Actor < ApplicationRecord
+ has_many :films, through: :film_actors

  validates :first_name, presence: true
  validates :last_name, presence: true
end

In models/film.rb:

class Film < ApplicationRecord
  has_one :language
+ has_many :categories, through: :film_categories
+ has_many :actors, through: :film_actors

  validates :rental_rate, :rental_duration, :replacement_cost,
            :title, :language_id, presence: true
end

Create Country

First, generate: rails generate model Country

Then in migration:

  def change
    create_table :countries do |t|
      t.string :country, null: false

      t.timestamps
    end

    add_index :countries, :country, unique: true

Then in model:

class Country < ApplicationRecord
  validates :country, presence: true, uniqueness: true

Create City

First, generate: rails generate model City

Then in migration:

  def change
    create_table :cities do |t|
      t.string :city
      t.references :country, null: false, foreign_key: true

      t.timestamps
    end

    add_index :cities, :city, unique: true

Then in model:

class City < ApplicationRecord
  belongs_to :country

  validates :city, presence: true, uniqueness: true

Cross-reference City and Country

In models/country.rb:

class Country < ApplicationRecord
+ has_many :cities

  validates :country, presence: true, uniqueness: true
end

Create Address

First, generate: rails generate model Address

Then in migration:

  def change
    create_table :addresses do |t|
      t.string :address, null: false, limit: 50
      t.string :address2, limit: 50
      t.string :district, null: false, limit: 20
      t.references :city, null: false, foreign_key: true
      t.string :postal_code, limit: 10
      t.string :phone, null: false, limit: 20

      t.timestamps

Then in model:

class Address < ApplicationRecord
  belongs_to :city

  validates :address, :district, :phone, presence: true

Cross-reference Address and City

In models/city.rb:

class City < ApplicationRecord
  belongs_to :country
+ has_many :addresses

  validates :city, presence: true, uniqueness: true

Create Customer

First, generate: rails generate model Customer

Then in migration:

  def change
    create_table :customers do |t|
      t.string :first_name, null: false, limit: 45
      t.string :last_name, null: false, limit: 45
      t.string :email, limit: 50
      t.references :address, null: false, foreign_key: true
      t.integer :active

      t.timestamps

Then in model:

class Customer < ApplicationRecord
  belongs_to :address

  validates :first_name, :last_name, presence: true
  validates :email, length: { maximum: 50 }

Cross-reference Customer and Address

In models/address.rb:

class Address < ApplicationRecord
  belongs_to :city
+ has_many :customers

Create Inventory

First, generate: rails generate model Inventory

Then in migration:

  def change
    create_table :inventories do |t|
      t.references :film, null: false, foreign_key: true

      t.timestamps
    end

Then in model:

class Inventory < ApplicationRecord
  belongs_to :film

Set Language.name limit to 20

First, generate: rails g migration ChangeLanguageNameLengthLimit

Then in migration:

  def change
    change_column :languages, :name, :string, limit: 20

In models/language.rb:

class Language < ApplicationRecord
+ validates :name, presence: true, uniqueness: true, length: { maximum: 20 }

Create Rental

First, generate: rails generate model Rental

Then in migration:

  def change
    create_table :rentals do |t|
      t.timestamp :rental_date, null: false
      t.references :inventory, null: false, foreign_key: true
      t.references :customer, null: false, foreign_key: true
      t.timestamp :return_date

      t.timestamps

Then in model:

class Inventory < ApplicationRecord
  belongs_to :film

Create Payment

First, generate: rails generate model Payment

Then in migration:

  def change
    create_table :payments do |t|
      t.references :customer, null: false, foreign_key: true
      t.references :rental, null: false, foreign_key: true
      t.decimal :amount, precision: 5, scale: 2, null: false
      t.timestamp :payment_date, null: false

      t.timestamps

Then in model:

class Payment < ApplicationRecord
  belongs_to :customer
  belongs_to :rental

  validates :amount, :payment_date, presence: true

Cross-reference Payment and Customer

In models/customer.rb:

class Customer < ApplicationRecord
  belongs_to :address
+ has_many :payments

Create Store

First, generate: rails generate model Store

Then in migration:

  def change
    create_table :stores do |t|
      t.references :address, null: false, foreign_key: true

      t.timestamps

Then in model:

class Store < ApplicationRecord
  belongs_to :address

Create Staff

First, generate: rails generate model Staff

Then in migration:

  def change
    create_table :staff do |t|
      t.string :first_name, null: false, limit: 45
      t.string :last_name, null: false, limit: 45
      t.references :address, null: false, foreign_key: true
      t.string :email, limit: 50
      t.references :store, null: false, foreign_key: true
      t.boolean :active, null: false
      t.string :username, null: false, limit: 16
      t.string :password, limit: 40

      t.timestamps

Then in model:

class Staff < ApplicationRecord
  self.table_name = 'staff'

  belongs_to :address
  belongs_to :store

  validates :first_name, :last_name, presence: true, length: { maximum: 45 }
  validates :email, length: { maximum: 50 }
  validates :username, presence: true, length: { maximum: 16 }
  validates :password, length: { maximum: 40 }

Add manager_staff to Store:

First, generate rails g migration AddManagerStaffToStore

Then in migration:

class AddManagerStaffToStore < ActiveRecord::Migration[7.0]
  def change
    add_reference :stores, :manager_staff, index: true, foreign_key: { to_table: :staff }

In models/store.rb:

class Store < ApplicationRecord
  belongs_to :address
+ has_one :manager_staff, class_name: 'Staff', foreign_key: :manager_staff

Add original_language_id to Film:

First, generate rails g migration AddOriginalLanguageIdToFilm

Then in migration:

class AddOriginalLanguageIdToFilm < ActiveRecord::Migration[7.0]
  def change
    add_reference :films, :original_language_id, foreign_key: { to_table: :languages }

Rename original_language_id and rental_rate columns

I made a couple of typos in films table that needed to be fixed:

    rename_column :films, :original_language_id_id, :original_language_id

    rename_column :films, :rantal_rate, :rental_rate

Remove uniqueness index from city

Put by mistake, to remove:

    remove_index :cities, :city

Cross-reference Customer and Store

Missed a reference of store_id in customers:

    add_reference :customers, :store, foreign_key: true

Add store_id to Inventory

  def change
    add_reference :inventories, :store, null: false, foreign_key: true

Add staff_id to Rentals

  add_reference :rentals, :staff, null: false, foreign_key: { to_table: :staff }

Add staff_id to Payments

  add_reference :payments, :staff, null: false, foreign_key: { to_table: :staff }

Fix Film <-> Actor associations in models

In models/actor.rb:

class Actor < ApplicationRecord
+ has_many :film_actors
  has_many :films, through: :film_actors

In models/film.rb:

class Film < ApplicationRecord
  # ...
+ has_many :film_actors
  has_many :actors, through: :film_actors

Fix Film <-> Category associations in models

In models/film.rb:

+ has_many :film_categories

In models/category.rb:

+ has_many :film_categories
+ has_many :films, through: :film_categories

Fix Language <-> Film associations in models

In models/film.rb:

- has_one :language
+ belongs_to :language

Fix Store <-> Staff associations in models

In models/store.rb:

- has_one :manager_staff, class_name: 'Staff', foreign_key: :manager_staff
+ belongs_to :manager_staff, class_name: 'Staff', foreign_key: :manager_staff_id
+ has_many :staff

Cross-reference Store and Customer

In models/store.rb:

+ has_many :customers

In models/customer.rb:

+ belongs_to :store

Cross-reference Inventory with Store and Rental

In models/inventory.rb:

+  belongs_to :store
+  has_many :rentals

In models/store.rb:

+  has_many :inventories

Cross-reference Payment with Rental and Staff

In models/rental.rb:

+ has_many :payments

In models/staff.rb:

+ has_many :payments

In models/payments:

+ belongs_to :staff

Cross-reference Rental with Customer and Staff

In models/rental.rb:

+ belongs_to :staff

In models/customer.rb:

+ has_many :rentals

In models/staff.rb:

+ has_many :rentals

Reference Store in Address

In models/address.rb:

+ has_one :store