Join Kevin Skoglund for an in-depth discussion in this video Creating a database, part of Ruby on Rails 4 Essential Training.
In this movie, we're going to create the database that we're going to be using for our sample project. Rails gives us command tool that we could use to create our database. However I don't recommend it. I found it to be buggy in the past. And it doesn't do much for you that can't do for yourself. So I think it's better to just create the database directly in MySQL, using MySQL commands. I'll walk you through it. There are four SQL database commands that you need to learn. SHOW DATABASES, CREATE DATABASE with whatever database name you want to create, USE followed by the database name that you want to use, and DROP DATABASE with whatever name of the database you want to remove.
Now let's log into MySQL from the command line and try this. Here I am in my command line utility. I've already installed MySQL back in the installation chapter. I'm in the root of my Rails application, but I don't have to be. I should have access to MySQL from anywhere in the command line, and to access it I'll type MySQL. The space followed by -u to let it know that I want to specify the user to log in as. A space and I'll type root and then a space and the -p option, which will tell it that I want to provide the password to log in. Remember during installation we set a default MySQL password.
If you didn't do that step you may not need the -p. Then hit Return, and enter your root MySQL password. For me, I entered secret password. Now I'm logged into MySQL. So the first thing, let's do show databases. SHOW DATABASES followed by a ;. You don't have to do it in all caps, it works in lowercase too, but the convention in MySQL is to use upper case for the MySQL commands. We want to make sure we put a semicolon at the end of all these MySQL commands. That's the way MySQL knows that we're done typing.
Then hit Return. MySQL then shows you a list of the databases that you have. These are the default databases that MySQL installed. The next thing we want to do is create our project database. So we're going to type create space database for that. And then a space and then the name of our database. Which I'm going to call, simple_cms_development. You should call yours the exact same thing. Then put a semi-colon at the end, and hit return. It tells us that one row was affected, which lets us know that it did create it for us.
We type SHOW DATABASES again. We'll see now we have simple_cms_development in the list. I'll explain why we called it _development in the next movie. Now, I'm not going to demonstrate the DROP DATABASE command, but I think it's pretty obvious what that would do. If you want to try it, you can, and then you can recreate that database again. And we don't need to do it at the moment, but we can type use and then simple cms development. And that will switch us into that database so that now MySQL thinks that all the commands that we're issuing refer to just this single database.
Just now, we connected to MySQL, as root. And we could do that for our Rails application, too. But I think that's a bad habit. I think it's much better to create a new MySQL user, that will then have permission to access our database. And we won't have to use our root user, or use our root password in our configuration file. What we want to do is create a user and then give that user privileges to use our database. The way we do that in MySQL is with the first statement there. Grant all privileges on and then whatever the database name is, a period, and then an asterisks after it.
Then a space, To space, and then the username that we want in single quotes. At and then we're going to use localhost, say that it's going to be, just when they're on this local computer, and then a space after that, identified by, and the password, that they would be identifying themselves as, when they try and login. So we're essentially saying, take this database, and use this username and password, to get access, and they'll get all privileges on that. The dot asterisk is a wild card. It means that all tables on that database have access.
Now this is not a preexisting user. We are creating the user in the same step as we grant the privileges. It's wrapped across three lines. But you can type it all as one line. Or hit return between lines. It doesn't matter. The semicolon at the end is what let MySQL know when you're done typing. The second command you see is a way for us to check and make sure that those privileges were added for the user. We're just looking at the granted privileges for a user. Let's create a user for our Rails app and grant them permission. Rather than have you watch me type it all out, I'm going to paste in the MySQL for this. Just take your time, pause the movie if you need to, copy it down, make sure that you don't have any typos in it, and make sure especially that you've got the name of the user, which I've called simple_cms as the username and then whatever password you want to use.
I've used secret password. That's a terrible password, and you can certainly think of something better. When I hit Return, it will create the user and grant them all privileges on our database. Then when I enter SHOW GRANTS FOR that user, it will come up and it will tell me that they have those privileges on the database. GRANT USAGE ON, TO simple_cms identified by a password, and then you see the password, which has been encrypted. To make sure that this user does have access, we can exit MySQL and then log back in as that user. MySQL -u and this time our user name is going to be simple cms, we're going to provide the password.
And, instead of saying use database, we can tell it specifically which database we want to give them access to by providing that database name next. So we're saying log into MySQL as the simple CMS user with a password. And connect to the simple cms development database. Hit Return. It asks for my password which was secret password and now I'm logged in. So, now we have our simple cms development database and we have a user with permissions to access it. In the next movie we'll configure our Rails project to connect to our new database with the correct username and password.
- Why use Ruby on Rails?
- Installing Ruby on Rails on Mac and Windows
- Rendering templates and redirecting requests
- Generating and running database migrations
- Creating, updating, and deleting records
- Understanding association types
- Using layouts, partials, and view helpers
- Incorporating assets using asset pipeline
- Validating form data
- Authenticating users and managing user access
- Architecting RESTful applications
- Debugging and error handing
Skill Level Intermediate
1. What Is Ruby on Rails?
2. Installing Ruby on Rails on a Mac
3. Installing Ruby on Rails on a Windows Machine
4. Getting Started
5. Controllers, Views, and Dynamic Content
6. Databases and Migrations
7. Models, ActiveRecord, and ActiveRelation
9. Controllers and CRUD
10. Layouts, Partials, and View Helpers
13. Data Validation
14. User Authentication
15. Improving the Simple CMS
16. REST and RESTful Routes
17. Debugging and Error Handling
18. Introducing More Advanced Topics
Next steps1m 40s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.