Intro to Databases (Rails Girls PHL)

A quick intro to databases for Rails Girls PHL.

Transcript

1. Intro to Databases Rails Bridge Philadelphia December 2016
2. Ernesto Tagwerker @etagwerker
3. Spreadsheets
5. From spreadsheets to databases
9. Primary Keys
10. Primary Keys
12. Relational Databases
13. Relations (Tables)
14. Relational Database Management Systems (DBMS)
15. MySQL Postgres SQLite* MS SQL Server and more … DBMS Alternatives
16. Databases in Rails
17. $ rails new --help # database # (options: mysql/oracle/postgresql/sqlite3/ frontbase/ibm_db/sqlserver/jdbcmysql/ jdbcsqlite3/jdbcpostgresql/jdbc) $ rails new polls —database=postgresql $ rails new polls # defaults to sqlite3
18. Where is the database?
19. SQL (for data) • Create • Retrieve • Update • Delete
20. Rails Migrations # db/migrate/20161124163416_create_topics.rb class CreateTopics < ActiveRecord::Migration def change create_table :topics do |t| t.string :title t.text :description t.timestamps null: false end end end
21. Rails Migrations $ rake db:migrate == 20161124163416 CreateTopics: migrating ===================================== -- create_table(:topics) -> 0.0496s == 20161124163416 CreateTopics: migrated (0.0497s) ============================
22. Rails Migrations CREATE TABLE `topics` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(255) DEFAULT NULL, `description` text, `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL, PRIMARY KEY (`id`) )
23. SQL (for table definitions) • Create table • Modify table • Drop table
24. Models # app/models/vote.rb class Vote < ActiveRecord::Base belongs_to :user belongs_to :topic end # app/models/topic.rb class Topic < ActiveRecord::Base has_many :votes end # app/models/user.rb class User < ActiveRecord::Base has_many :votes end
25. Models (create) $ rails console > Topic.create(title: 'Intro to Database', description: 'This is awesome!') SQL (1.4ms) INSERT INTO `topics` (`title`, `description`, `created_at`, `updated_at`) VALUES ('Intro to Database', 'This is awesome!', '2016-11-24 16:57:13', '2016-11-24 16:57:13') => #<Topic id: 1, title: "Intro to Database", description: "This is awesome!", created_at: "2016-11-24 16:57:13", updated_at: "2016-11-24 16:57:13">
26. Models (retrieve) > topic = Topic.find 1 Topic Load (1.6ms) SELECT `topics`.* FROM `topics` WHERE `topics`.`id` = 1 LIMIT 1 => #<Topic id: 1, title: "Intro to Database", description: "This is awesome!", created_at: "2016-11-24 16:57:13", updated_at: "2016-11-24 16:57:13">
28. Models (update) > topic.title = "Intro to Databases" => "Intro to Databases" > topic.save SQL (2.3ms) UPDATE `topics` SET `title` = 'Intro to Databases', `updated_at` = '2016-11-24 17:00:47' WHERE `topics`.`id` = 1 => true
29. Models (destroy) > topic.delete SQL (10.0ms) DELETE FROM `topics` WHERE `topics`.`id` = 1 => #<Topic id: 1, title: "Intro to Databases", description: "This is awesome!", created_at: "2016-11-24 16:57:13", updated_at: "2016-11-24 17:00:47">
30. Models (count) > Topic.count (2.1ms) SELECT COUNT(*) FROM `topics` => 0
31. How to use models
32. Topic.find 123 Topic.first Topic.last Topic.count To interact with tables
33. topic = Topic.find 123 topic.title = “New title” topic.save To modify records
34. Thank you!
35. Questions?