SQL & Active record:

Exploring your database

So many sqls...

SQLite3

Postgres

MySQL

Oracle

SQL Servers

How crud-y is sql?

CRUD

Create

Read

Update

Delete

HTML Verbs

post

get

patch/put

delete

SQL Commands

insert

select

update

delete

SQL project is go!

Let's start a new project...

$ rails new db_explore
$ cd db_explore

...and create a Resource called User...

$ rails g scaffold User name:string location:string age:integer
$ rake db:migrate

Then we need some data to explore: create five Users!

Console-ation prize

Let's use SQL querying to take a look inside our database...

Open the dbconsole:

$ rails dbconsole

SQLite version 3.7.12 2012-04-03 19:43:07

Enter ".help" for instructions

Enter SQL statements terminated with a ";"

sqlite>

Table roll call!

Perhaps you've simply forgotten what databases you even have on this project.

Don't worry! You can check for tables...

sqlite> .tables

schema_migrations users

sqlite>

Tell me *

In SQL, the asterisk (*) means "all". Let's pull all the data from the User table!


sqlite> select * from users;

1|Jude|Atlanta|19|2014-08-18 12:55:45.899020|2014-08-18 12:56:37.077430

2|Ellen|San Diego|65|2014-08-18 12:56:00.461203|2014-08-18 12:56:00.461203

3|Norah|Venice|32|2014-08-18 12:56:18.187149|2014-08-18 12:56:18.187149

4|Jason|Indianapolis|22|2014-08-18 12:56:31.751468|2014-08-18 12:56:31.751468

5|Leila|Edmonton|29|2014-08-18 12:56:54.591946|2014-08-18 12:56:54.591946

sqlite>

Tell Me What I

Want to Hear

Maybe we don't want to see everything.

Maybe we just want to see certain attributes (columns).

sqlite> select name, age from users;

Jude|19

Ellen|65

Norah|32

Jason|22

Leila|29

sqlite>

I don't do well in crowds...

What if we had hundreds of rows in our table?

Do we want to see all of those at once?

Nah – so let's “limit” what's returned...

sqlite> select * from users limit 2;

1|Jude|Atlanta|19|2014-08-18 12:55:45.899020|2014-08-18 12:56:37.077430

2|Ellen|San Diego|65|2014-08-18 12:56:00.461203|2014-08-18 12:56:00.461203

sqlite>

Notice, that's the first two users in our table.

let's get specific

What if we're looking for a certain person?


sqlite> select * from users where name = 'Jason';

4|Jason|Indianapolis|22|2014-08-18 12:56:31.751468|2014-08-18 12:56:31.751468

sqlite>

In this case, we use the 'where' clause to get specific.

You can also get a little more complex:

sqlite> select * from users where name = 'Leila' and age = 29;

5|Leila|Edmonton|29|2014-08-18 12:56:54.591946|2014-08-18 12:56:54.591946

sqlite>

Using 'or' is also an option.

he's between this tall and this short...

You can use greater and less than, too (with integers or floats, of course):

sqlite> select * from users where age > 30;

2|Ellen|San Diego|65|2014-08-18 12:56:00.461203|2014-08-18 12:56:00.461203

3|Norah|Venice|32|2014-08-18 12:56:18.187149|2014-08-18 12:56:18.187149

sqlite>

Wild card!

A cool feature of SQL querying is the 'wild card' (%).

Here's a case where we look up all the names that begin with 'J':

sqlite> select * from users where name like 'J%';

1|Jude|Atlanta|19|2014-08-18 12:55:45.899020|2014-08-18 12:56:37.077430

4|Jason|Indianapolis|22|2014-08-18 12:56:31.751468|2014-08-18 12:56:31.751468

sqlite>

make a change,

with sql

Just looking at the data is all well and good, but what about changing the data?

sqlite> select * from users where id = 1;
1|Jude|Atlanta|19|2014-11-10 23:31:26.585649|2014-11-10 23:31:26.585649
sqlite> update users set age = 25 where id = 1;
sqlite>

sqlite> select * from users where id = 1;
1|Jude|Atlanta|25|2014-11-10 23:31:26.585649|2014-11-10 23:31:26.585649
sqlite>

Nothing seems to happen when we "update", but check again!

sql makes it so!

sqlite> insert into users (name, location, age) values ('Sam', 'Pensacola', 33);
sqlite> select * from users where name = 'Sam'
6|Sam|Pensacola|33||
sqlite>

We can create new entries with SQL:

The only problem:

SQL will not provide 'created_at' and 'updated_at' like Rails does.

:-(

Sql, dump it!

sqlite> delete from users where name = 'Jason';

sqlite> select * from users;

1|Jude|Atlanta|25|2014-08-18 12:55:45.899020|2014-08-18 12:56:37.077430

2|Ellen|San Diego|65|2014-08-18 12:56:00.461203|2014-08-18 12:56:00.461203

3|Norah|Venice|32|2014-08-18 12:56:18.187149|2014-08-18 12:56:18.187149

5|Leila|Edmonton|29|2014-08-18 12:56:54.591946|2014-08-18 12:56:54.591946

6|Sam|Pensacola|33||

sqlite>

We can delete an entry via SQL...

Note that not only is Jason gone, but ID #4 is gone & the table hasn't been re-ordered. #4 is forever gone from this DB!

SQl, get me out of here!

You can't spend your whole life in dbconsole.

How do you get out of this place?

sqlite> .exit
$

Yup...that's all it takes.

get active! (record)

SQL is hard.

ActiveRecord is easy.

You've seen ActiveRecord name-checked in your Models:

class User < ActiveRecord::Base

end

  def index
    @users = User.all
  end

  def new
    @user = User.new
  end

And you've seen it at work in your Controllers:

dbconsole, you've met your match

ActiveRecord has its own special hideout:

$ rails console
Loading development environment (Rails 4.1.4)
1.9.3-p392 :001 >

That's my Ruby version (yes, I'm behind the times), followed by a line number for this session of rails console.

1.9.3-p392 :001 > User.all
  User Load (3.7ms)  SELECT "users".* FROM "users"
 => #<ActiveRecord::Relation [#<User id: 1, name: "Jude", location: "Atlanta", age: 25, 
created_at: "2014-11-10 23:31:26", updated_at: "2014-11-10 23:31:26">, #<User id: 2, 
name: "Ellen", location: "San Diego", age: 65, created_at: "2014-11-10 23:31:36", 
updated_at: "2014-11-10 23:31:36">, #<User id: 3, name: "Norah", location: "Venice", 
age: 32, created_at: "2014-11-10 23:31:46", updated_at: "2014-11-10 23:31:46">, 
#<User id: 4, name: "Jason", location: "Indianapolis", age: 22, created_at: 
"2014-11-10 23:31:58", updated_at: "2014-11-10 23:31:58">, #<User id: 5, name: "Leila", 
location: "Edmonton", age: 29, created_at: "2014-11-10 23:32:10", updated_at: 
"2014-11-10 23:32:10">, #<User id: 6, name: "Sam", location: "Pensacola", age: 33, 
created_at: nil, updated_at: nil>]>
1.9.3-p392 :002 >

ActiveRecord, let's see it all...

The output format may be uglier than SQL's, but the command is so much easier!

(Also, notice how it shows you the SQL query.)

1.9.3-p392 :002 > User.first
  User Load (0.2ms)  SELECT  "users".* FROM "users"   ORDER BY "users"."id" ASC LIMIT 1
 => #<User id: 1, name: "Jude", location: "Atlanta", age: 25, 
created_at: "2014-11-10 23:31:26", updated_at: "2014-11-10 23:31:26">
1.9.3-p392 :003 > User.last
  User Load (0.2ms)  SELECT  "users".* FROM "users"   ORDER BY "users"."id" DESC LIMIT 1
 => #<User id: 6, name: "Sam", location: "Pensacola", age: 33, created_at: nil, 
updated_at: nil>
1.9.3-p392 :004 >

We can look at specific entries...

We can give ActiveRecords specifics...

1.9.3-p392 :004 > User.find(3)
  User Load (0.3ms)  SELECT  "users".* FROM "users"  WHERE "users"."id" = ? LIMIT 1  [["id", 3]]
 => #<User id: 3, name: "Norah", location: "Venice", age: 32, created_at: "2014-11-10 23:31:46", 
updated_at: "2014-11-10 23:31:46">
1.9.3-p392 :005 > User.where(name: "Jason")
  User Load (0.2ms)  SELECT "users".* FROM "users"  WHERE "users"."name" = 'Jason'
 => #<ActiveRecord::Relation [#<User id: 4, name: "Jason", location: "Indianapolis", age: 22, 
created_at: "2014-11-10 23:31:58", updated_at: "2014-11-10 23:31:58">]>
1.9.3-p392 :006 >

Update in rails console

1.9.3-p392 :007> x = User.find(3)
  User Load (0.1ms)  SELECT  "users".* FROM "users"  WHERE "users"."id" = ? LIMIT 1  [["id", 3]]
 => #<User id: 3, name: "Norah", location: "Venice", age: 32, created_at: "2014-11-10 23:31:46", 
updated_at: "2014-11-10 23:31:46">
1.9.3-p392 :008 > x.location = "Chicago"
 => "Chicago"
1.9.3-p392 :009 > User.find(3)
  User Load (0.1ms)  SELECT  "users".* FROM "users"  WHERE "users"."id" = ? LIMIT 1  [["id", 3]]
 => #<User id: 3, name: "Norah", location: "Venice", age: 32, created_at: "2014-11-10 23:31:46", 
updated_at: "2014-11-10 23:31:46">
1.9.3-p392 :010 > x.save
   (0.1ms)  begin transaction
  SQL (0.6ms)  UPDATE "users" SET "location" = ?, "updated_at" = ? WHERE "users"."id" = 3  
[["location", "Chicago"], ["updated_at", "2014-11-11 11:52:22.885042"]]
   (0.9ms)  commit transaction
 => true
1.9.3-p392 :011 > User.find(3)
  User Load (0.1ms)  SELECT  "users".* FROM "users"  WHERE "users"."id" = ? LIMIT 1  [["id", 3]]
 => #<User id: 3, name: "Norah", location: "Chicago", age: 32, created_at: "2014-11-10 23:31:46", 
updated_at: "2014-11-11 11:52:22">

Updating Rails Console is a bit more complicated than in SQL, but it's exactly how you'd update an entry in your Rails project.

The change isn't saved until we call 'x.save'.

The Power to create...

1.9.3-p392 :012 > newbie = User.new
 => #<User id: nil, name: nil, location: nil, age: nil, created_at: nil, updated_at: nil>

You can create a new instance of the Resource in Rails Console, but be careful...

Give your newbie a name, location and age. Active Record will take care of the rest....

1.9.3-p392 :013 > newbie.name = "Josephine"
 => "Josephine"
1.9.3-p392 :014 > newbie.location = "Santiago"
 => "Santiago"
1.9.3-p392 :015 > newbie.age = 41
 => 41
1.9.3-p392 :016 > newbie.save
   (0.1ms)  begin transaction
  SQL (0.3ms)  INSERT INTO "users" ("age", "created_at", "id", "location", "name", "updated_at") 
VALUES (?, ?, ?, ?, ?, ?)  [["age", 41], ["created_at", "2014-11-11 12:51:03.809027"],  
["location", "Santiago"], ["name", "Josephine"], ["updated_at", "2014-11-11 12:51:03.809027"]]
   (1.1ms)  commit transaction
 => true
1.9.3-p392 :017 > User.where(name: "Josephine")
  User Load (0.3ms)  SELECT "users".* FROM "users"  WHERE "users"."name" = 'Josephine'
 => #<ActiveRecord::Relation [#<User id: 7, name: "Josephine", location: "Santiago", age: 41, 
created_at: "2014-11-11 12:51:03", updated_at: "2014-11-11 12:51:03">]>

...and the power to destroy

1.9.3-p392 :018 > User.find(7).destroy
  User Load (0.1ms)  SELECT  "users".* FROM "users"  WHERE "users"."id" = ? LIMIT 1  [["id", 7]]
   (0.1ms)  begin transaction
  SQL (0.2ms)  DELETE FROM "users" WHERE "users"."id" = ?  [["id", 7]]
   (0.8ms)  commit transaction
 => #<User id: 7, name: "Josephine", location: "Santiago", age: 41, created_at: "2014-11-11 13:02:34", 
updated_at: "2014-11-11 13:02:34">
1.9.3-p392 :045 >

You cannot combine the .where() query and destroy.

You can assign the entry as a variable and then destroy:

1.9.3-p392 :045 > x = User.where(id: 2)
  User Load (0.2ms)  SELECT  "users".* FROM "users"  WHERE "users"."id" = ? LIMIT 1  [["id", 2]]
 => #<User id: 2, name: "Ellen", location: "San Diego", age: 65, created_at: "2014-11-10 23:31:36", 
updated_at: "2014-11-10 23:31:36">
1.9.3-p392 :046 > x.destroy
   (0.1ms)  begin transaction
  SQL (0.2ms)  DELETE FROM "users" WHERE "users"."id" = ?  [["id", 2]]
   (0.9ms)  commit transaction
 => #<User id: 2, name: "Ellen", location: "San Diego", age: 65, created_at: "2014-11-10 23:31:36", 
updated_at: "2014-11-10 23:31:36">
1.9.3-p392 :047 > x.save
   (0.1ms)  begin transaction
   (0.0ms)  commit transaction
 => true