Beginner Tutorial: Hello Database
A quick tutorial that demonstrates how quickly you can get database connectivity up and running with Wheels.
Wheels's built in model provides your application with some simple and powerful functionality for interacting with databases. To get started, you make some simple configurations, call some functions within your controllers, and that's it. Best yet, you will rarely ever need to write SQL code to get those redundant CRUD tasks out of the way.
Our Sample Application: User Management
We'll learn by building part of a sample user management application. This tutorial will teach you the basics of interacting with Wheels's ORM.
Setting up the Data Source
By default, Wheels will connect to a data source that has the same name as the folder containing your Wheels application. So if your Wheels application is in a folder called C:\websites\mysite\blog\, then it will connect to a data source named blog.
To change this default behavior, open the file at config/settings.cfm. In a fresh install of Wheels, you'll see some commented-out lines of code that read as such:
<!---
If you leave these settings commented out, Wheels will set the data source name
to the same name as the folder the application resides in.
<cfset set(dataSourceName="")>
<cfset set(dataSourceUserName="")>
<cfset set(dataSourcePassword="")>
--->
Uncomment the lines that tell Wheels what it needs to know about the data source and provide the appropriate values. This may include values for dataSourceName, dataSourceUserName, and dataSourcePassword.
<cfset set(dataSourceName="myblogapp")>
<cfset set(dataSourceUserName="marty")>
<cfset set(dataSourcePassword="mcfly")>
Our Sample Data Structure
Wheels supports MySQL, SQL Server, PostgreSQL, and Oracle. It doesn't matter which DBMS you use. We will all be writing the same CFML code to interact with the database. Wheels does everything behind the scenes that needs to be done to work with each DBMS.
That said, here's a quick look at a table that you'll need in your database:
| Column Name | Data Type | Extra |
|---|---|---|
| id | int |
auto increment, primary key |
| name | varchar(100) |
|
varchar(255) |
||
| password | varchar(15) |
Note a couple things about these tables:
- The table name is plural.
- The table has an auto-incrementing primary key named
id.
These are database conventions used by Wheels. This framework strongly encourages that everyone follow convention over configuration. That way everyone is doing things mostly the same way, leading to less maintenance and training headaches down the road.
Fortunately, there are ways of going outside of these conventions when you really need it. But let's learn the conventional way first. Sometimes you need to learn the rules before you can know how to break them, cowboy.
Adding Users
First, let's create a simple form for adding a new user to the users table. To do this, we will use Wheels's form helper functions. Wheels includes a whole range of functions that simplifies all of the tasks that you need to display forms and communicate errors to the user.
Creating the Form
Now create a new file in views/users called add.cfm. This will contain the view code for our simple form.
Next, add these lines of code to the new file:
<cfoutput>
<h1>Create a New User</h1>
#startFormTag(action="create")#
<div>#textField(objectName="user", property="name", label="Name")#</div>
<div>#textField(objectName="user", property="email", label="Email")#</div>
<div>#passwordField(objectName="user", property="password", label="Password")#</div>
<div>#submitTag()#</div>
#endFormTag()#
</cfoutput>
Form Helpers
What we've done here is use form helpers to generate all of the form fields necessary for creating a new user in our database. It may feel a little strange using functions to generate form elements, but it will soon become clear why we're doing this. Trust us on this one… You'll love it!
To generate the form tag's action attribute, the startFormTag() function takes parameters similar to the linkTo() function that we introduced in the Beginner Tutorial: Hello World tutorial. We can pass in controller, action, key, and other route- and parameter-defined URLs just like we do with linkTo().
To end the form, we use the endFormTag() function. Easy peasy, lemon squeezy.
The textField() and passwordField() helpers are similar. As you probably guessed, they create <input> elements with type="text" and type="password", respectively. And the submitTag() function creates an <input type="submit" /> element.
One thing you'll notice is the textField() and passwordField() functions accept arguments called objectName and property. As it turns out, this particular view code will throw an error because these functions are expecting an object named user. Let's fix that.
Supplying the Form with Data
We need to supply our view code with an object called user. Because the controller is responsible for providing the views with data, we'll set it there.
As it turns out, our controller needs to provide the view with a blank user object (whose instance variable will also be called user in this case). In our add action, we will use the model() function to generate a new instance of the user model.
To get a blank set of properties in the model, we'll also call the generated model's new() method.
<cfset user = model("user").new()>
Note: Wheels will automatically know that we're talking about the users database table when we instantiate a user model. The convention: database tables are plural and their corresponding Wheels models are singular.
Why is our model name singular instead of plural? When we're talking about a single record in the users database, we represent that with a model object. So the users table contains many user objects. It just works better in conversation.
The Generated Form
Now when we run the URL at http://localhost/users/add, we'll see the form with the fields that we defined.
The HTML generated by your application will look something like this:
<h1>Create a New User</h1>
<form action="/users/create" method="post">
<div>
<label for="user-name">Name
<input id="user-name" type="text" value="" name="user[name]" /></label>
</div>
<div>
<label for="user-email">Email
<input id="user-email" type="text" value="" name="user[email]" /></label>
</div>
<div>
<label for="user-password">Password
<input id="user-password" type="password" value="" name="user[password]" /></label>
</div>
<div><input value="Save changes" type="submit" /></div>
</form>
So far we have a fairly well-formed, accessible form, without writing a bunch of repetitive markup.
Handling the Form Submission
Next, we'll code the create action in the controller to handle the form submission and save the new user to the database.
A basic way of doing this is using the model object's create() method:
<cffunction name="create">
<cfset model("user").create(params.user)>
<cfset flashInsert(success="User #params.user.name# created successfully.")>
<cfset redirectTo(action="index")>
</cffunction>
Because we used the objectName argument in the fields of our form, we can access the data as a struct in the params struct.
There are more things that we can do in the create action to handle validation, but let's keep it simple in this chapter.
Listing Users
Notice that our create action above redirects the user to the index action using the redirectTo() function. We'll use this action to list all users in the system with "Edit" links. We'll also provide a link to the create form that we just coded.
First, let's get the data that the listing needs. Create an action named index in the users controller like so:
<cffunction name="index">
<cfset users = model("user").findAll(order="name")>
</cffunction>
This call to the model's findAll() method will return a query object of all users in the system. By using the method's order argument, we're also telling the database to order the records by name.
In the view at views/users/index.cfm, it's as simple as looping through the query and outputting the data.
<cfoutput>
<h1>Users</h1>
<cfif flashKeyExists("success")>
<p class="success">#flash("success")#</p>
</cfif>
<p>#linkTo(text="+ Add New User", action="add")#</p>
<table>
<thead>
<tr>
<th>Name</th>
<th>Email</th>
<th colspan="2"></th>
</tr>
</thead>
<tbody>
<cfloop query="users">
<tr>
<td>#users.name#</td>
<td>#users.email#</td>
<td>
#linkTo(
text="Edit", action="edit", key=users.id,
title="Edit #users.name#"
)#
</td>
<td>
#linkTo(
text="Delete", action="delete", key=users.id,
title="Delete #users.name#",
confirm="Are you sure that you want to delete
#users.name#?"
)#
</td>
</tr>
</cfloop>
</tbody>
</table>
</cfoutput>
Editing Users
We'll now show another cool aspect of form helpers by creating a screen for editing users.
Coding the Edit Form
You probably noticed in the code listed above that we'll have an action for editing users called edit. This action expects a key as well which is passed in the URL by default.
Given the provided key, we'll have the action load the appropriate user object to pass on to the view:
<cffunction name="edit">
<cfset user = model("user").findByKey(params.key)>
</cffunction>
The view at views/user/edit.cfm looks almost exactly the same as the view for creating a user:
<cfoutput>
<h1>Edit User #user.name#</h1>
<cfif flashKeyExists("success")>
<p class="success">#flash("success")#</p>
</cfif>
#startFormTag(action="update")#
<div>#hiddenField(objectName="user", property="id")#</div>
<div>#textField(objectName="user", property="name", label="Name")#</div>
<div>#textField(objectName="user", property="email", label="Email")#</div>
<div>
#passwordField(objectName="user", property="password", label="Password")#
</div>
<div>#submitTag()#</div>
#endFormTag()#
</cfoutput>
But an interesting thing happens. Because the form fields are bound to the user object via the form helpers' objectName arguments, the form will automatically provide default values based on the object's properties.
With the user model populated, we'll end up seeing code similar to this:
<h1>Edit User Homer Simpson</h1>
<form action="/users/update" method="post">
<div><input type="hidden" name="user[id]" value="15" /></div>
<div>
<label for="user-name">Name
<input
id="user-name" type="text" value="Homer Simpson"
name="user[name]" /></label>
</div>
<div>
<label for="user-email">Email
<input
id="user-email" type="text" value="homerj@nuclearpower.com"
name="user[email]" /></label>
</div>
<div>
<label for="user-password">Password
<input
id="user-password" type="password" value="donuts.mmm"
name="user[password]" /></label>
</div>
<div><input value="Save changes" type="submit" /></div>
</form>
Pretty cool, huh?
Opportunities for Refactoring
There's a lot of repetition in the add and edit forms. You'd imagine that we could factor out most of this code into a single view file. To keep this tutorial from becoming a book, we'll just continue on knowing that this could be better.
Handing the Edit Form Submission
Now we'll create the update action. This will be similar to the create action, except it will be updating the user object:
<cffunction name="update">
<cfset user = model("user").findByKey(params.user.id)>
<cfset user.update(params.user)>
<cfset
flashInsert(
success="User #user.name# updated successfully."
)
>
<cfset redirectTo(action="edit", key=user.id)>
</cffunction>
To update the user, simply call its update() method with the user struct passed from the form via params. It's that simple.
After the update, we'll add a success message using the Flash and send the end user back to the edit form in case they want to make more changes.
Deleting Users
Notice in our listing above that we have a delete action. Our linkTo() call has an argument named confirm, which will include some simple JavaScript to pop up a box asking the end user to confirm the deletion before running the delete action.
Here's what our delete action would look like:
<cffunction name="delete">
<cfset user = model("user").findByKey(params.key)>
<cfset user.delete()>
<cfset flashInsert(success="#user.name# was successfully deleted.")>
<cfset redirectTo(action="index")>
</cffunction>
We simply load the user using the model's findByKey() method and then call the object's delete() method. That's all there is to it.
Database Says Hello
We've shown you quite a few of the basics in getting a simple user database up and running. We hope that this has whet your appetite to see some of the power packed into the ColdFusion on Wheels framework. There's plenty more.
Be sure to read on to some of the related chapters listed below to learn more about working with Wheels's ORM.
Beginner Tutorial: Hello World
Table of Contents
Tutorial: Wheels, AJAX, and You
Comments
Read and submit questions, clarifications, and corrections about this chapter.
Vivio Technologies.
There is a bug with your update function. the last line should be: <cfset redirectTo(action="edit", key=params.user.id) /> or the user is sent to the edit page without an ID
Good catch, Jon. Thanks for letting us know. Got it fixed up.
replace(Notice in our listing above that we have a delete action,"above","below","all") the listing is below
could you provide a tip for factoring the 2 views into one? is there a sort of partials? i think to change the actions is not the right way to go ... thx
Great Tutorial! I'm really enjoying CFWheels. Thanks a lot!
BTW I would love to see a CRUD tutorial using AJAX (JQuery would be even better)...
I noticed a small bug on the + Add New User link that its action is set "user" but it should be action="add".
@reinhard: It's as simple as checking if params.key exists on the view, although there could be a cleaner way of doing it on the controller.
Hi,
Following along with your "Hello Database" toot, I set out to follow your directive:
***
To get a blank set of properties in the model, we'll also call the generated model's new() method.
<cfset user = model("user").new()>
***
The thing is, you didn't specify which folder/file cfset... goes in. I guessed it should be added to models/Model.cfc -- I tried that and got the error "Variable user is undefined. "
Can you assist?
Thanks
Well, following these steps as far as Generating the Form, I get "The errorsOn method was not found." when going to localhost/users/add/
Me again -- suffering much less noob angst.
No whining this time, all I have to say this time around is thanks for the awesome tutorial. The errors committed were mine. All mine.
Back to my lair.
-Bryce
Am I retarded or is there a step missing? I'm getting an error about the user var not being defined.
You might want to throw in one line regarding creating the Users.cfc controller, between "Adding Users" and "Creating the Form". Might make thinks clearer for the absolute newbie, like me. :-)
Love what I see so far.
Where do you put this? <cfset user = model("user").new()>
Instructions not very clear to me. Sorry and thanks for any help.
Yeah its not clear... I added it to the add.cfm at the top.
<cfset user = model("user").new()>
<cfoutput>
<h1>Create a New User</h1>
#startFormTag(action="create")#
<div>#textField(objectName="user", property="name", label="Name")#</div><br/>
<div>#textField(objectName="user", property="email", label="Email")#</div><br/>
<div>#passwordField(objectName="user", property="password", label="Password")#</div><br/>
<div>#submitTag()#</div>
#endFormTag()#
</cfoutput>
I'm pretty sure the <cfset user = model("user").new()> is supposed to go into your Controllers/Users.cfc file, like so:
<cfcomponent extends="Controller">
<cffunction name="add">
<cfset user=model("user").new()>
</cffunction>
</cfcomponent>
My problem actually involves setting up the datasource. I've become so used to setting up my datasources from within CF Administrator. If my datasource is different than my site directory, where do I go to set the datasource name? I've updated my config/settings.cfm file with the datasource name I have set up in Admin, but I receive an error when trying to visit my application. It says:
Wheels.DataSourceNotFound
The data source could not be reached.
Suggested action
Make sure your database is reachable and that your data source settings are correct. You either need to setup a data source with the name cfwheels in the CFML Administrator or tell Wheels to use a different data source in config/settings.cfm.
Error location
Line 5 in controllers\Users.cfc
3: <cffunction name="add">
4:
5: <cfset user=model("user").new()>
6:
7: </cffunction>
My mySQL database is currently hosted. How does cfwheels recommend connecting to a hosted mySQL db from localhost?
Ok, so I simply renamed my datasource to "cfwheels" within CF Admin and the page loaded correctly.
So, I guess I'm a little unsure how to get a dsn working if it doesn't have the same name as the folder structure. This may be a silly question... but, just to clear this up... the following settings in the config/settings.cfm file:
<cfset set(dataSourceUserName="")> and
<cfset set(dataSourcePassword="")> are referring to what? Should these be username/password for access to the db? Access to CF Admin?
I see a lot of potential with CFWheels. Can't wait to really dig in.
Cody, thanks for the tip about Users.cfc. That did the trick.
I had the same problem you did with the database until I remembered that I should restart the CF server after editing the settings.cfm file. Not sure if that was your issue. I left the dataSourceUserName and dataSourcePassword blank since it was set in the CF admin.
Wow, so you have to restart CF in order for changes to config/settings.cfm to take place? Must be a way to force the framework to reinitialize instead eh?
No need to restart CF. You can reload settings in config/settings.cfm by adding ?reload=true to the end of your URL or by clicking the little "Reload" link in the debugging area if you're in design, development, or testing modes.
See the chapter on Switching Environments for more info:
http://cfwheels.org/docs/chapter/switching-environments
the js prompt didn't work for me, on inspections via firebug I couldn't see a js function anywhere.
Hi,
(1) Where should i put this? Controller? Model? Tut not specific the place to put this code
<cfset user = model("user").new()>
(2) I got an error message when try /users/XXX
---------
Element user is undefined in a Java object of type class coldfusion.runtime.VariableScope.
The error occurred in /opt/coldfusion9/wwwroot/meeting/wheels/view/miscellaneous.cfm: line 220
Called from /opt/coldfusion9/wwwroot/meeting/wheels/view/forms.cfm: line 1390
Called from /opt/coldfusion9/wwwroot/meeting/wheels/view/forms.cfm: line 1440
Called from /opt/coldfusion9/wwwroot/meeting/wheels/view/forms.cfm: line 150
Called from /opt/coldfusion9/wwwroot/meeting/views/users/add.cfm: line 4
Called from /opt/coldfusion9/wwwroot/meeting/wheels/global/cfml.cfm: line 101
Called from /opt/coldfusion9/wwwroot/meeting/wheels/controller/rendering.cfm: line 315
Called from /opt/coldfusion9/wwwroot/meeting/wheels/controller/rendering.cfm: line 119
Called from /opt/coldfusion9/wwwroot/meeting/wheels/controller/rendering.cfm: line 40
Called from /opt/coldfusion9/wwwroot/meeting/wheels/dispatch/request.cfm: line 390
Called from /opt/coldfusion9/wwwroot/meeting/wheels/dispatch/request.cfm: line 352
Called from /opt/coldfusion9/wwwroot/meeting/wheels/index.cfm: line 1
Called from /opt/coldfusion9/wwwroot/meeting/index.cfm: line 1
Called from /opt/coldfusion9/wwwroot/meeting/wheels/events/onrequest.cfm: line 1
218 : returnValue = Evaluate(arguments.objectName);
219 : else
220 : returnValue = variables[arguments.objectName];
221 : </cfscript>
222 : <cfreturn returnValue>
Resources:
* Check the ColdFusion documentation to verify that you are using the correct syntax.
* Search the Knowledge Base to find a solution to your problem.
Browser Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.1.8) Gecko/20100214 Ubuntu/9.10 (karmic) Firefox/3.5.8
Remote Address 127.0.0.1
Referrer
Date/Time 17-Mar-10 11:00 AM
I'm getting the same errors as hymns...any update? I'm using Wheels 1.0.3. Also, if I put the <cfset user = model("user").new()> in view\users\add.cfm I can load the form but if I put it in an 'add' action in the controller then I get this error.
I found that my users controller had the extention .cfm and when I changed that to .cfc everything started to work. Hope that helps to everyone else :)