CRUD Operations

Every CRUD operation so far — cfquery, queryExecute, cfqueryparam, ORM's entity functions — has been shown as an isolated example. A real feature strings them together into actual pages that work as a set: one page lists rows, another creates a new one, another edits an existing one, and a delete action removes one. This lesson builds that complete flow using queryExecute with parameterized values, the same safe pattern cfqueryparam uses under the hood.

Everything here works exactly as well with the ORM entities from the previous lesson — entityLoad instead of a SELECT, entitySave instead of an INSERT or UPDATE, entityDelete instead of a DELETE. The page structure itself doesn't change based on which one is underneath it.

Learning Objectives

After completing this lesson, you'll be able to:

• Structure a CRUD feature as a set of pages: list, create, edit, and delete.
• Build a create and an edit form that share the same handler logic where it makes sense.
• Avoid a real security gotcha with trusting an ID that comes straight from a form or URL.

The Table

Setting Up: Application.cfc

Every page in this lesson refers to application.datasource rather than naming a datasource directly. That value comes from Application.cfc, set once and shared by every page in the application.

component {
    this.name = "TasksApp";
    this.sessionManagement = true;

    function onApplicationStart() {
        application.datasource = "tasksDB";
        return true;
    }
}

How the Pages Connect

Read: Listing Every Row

<cfscript>
tasks = queryExecute("SELECT id, title, completed FROM tasks ORDER BY id DESC", {}, {datasource: application.datasource});
</cfscript>

<a href="create.cfm">Add Task</a>

<cfoutput>
<table>
    <cfloop query="tasks">
        <tr>
            <td>#tasks.title#</td>
            <td>#tasks.completed ? "Done" : "Pending"#</td>
            <td>
                <a href="edit.cfm?id=#tasks.id#">Edit</a>
                <a href="delete.cfm?id=#tasks.id#">Delete</a>
            </td>
        </tr>
    </cfloop>
</table>
</cfoutput>

Create: A Form and Its Handler

Step by step, in plain words, what create.cfm does every time it runs:

• 1. Check whether form.title exists. The very first time someone opens this page (just clicking "Add Task"), no form has been submitted yet, so this is false — skip straight to step 5.
• 2. If the form was submitted, read title and description out of it and trim any extra spaces.
• 3. If title isn't empty, run an INSERT to add the new row to the tasks table.
• 4. Immediately send the browser to list.cfm with cflocation, so the new task shows up right away.
• 5. Show the empty form — this is what actually renders on that first visit, and also what renders again if title turned out to be empty in step 3.

<cfif structKeyExists(form, "title")>
    <cfset title = trim(form.title)>
    <cfset description = trim(form.description ?: "")>

    <cfif len(title)>
        <cfquery datasource="#application.datasource#">
            INSERT INTO tasks (title, description, completed)
            VALUES (
                <cfqueryparam value="#title#" cfsqltype="cf_sql_varchar">,
                <cfqueryparam value="#description#" cfsqltype="cf_sql_varchar">,
                0
            )
        </cfquery>
        <cflocation url="list.cfm" addtoken="false">
    </cfif>
</cfif>

<form method="post">
    <input type="text" name="title" required>
    <textarea name="description"></textarea>
    <button type="submit">Add Task</button>
</form>

Update: Loading One Row Into the Form, Then Saving Changes

Step by step, what edit.cfm does every time it runs:

• 1. Read id from the URL — this is how edit.cfm knows which task it's even working with, since the link from list.cfm was edit.cfm?id=7 (or whatever the row's actual id is).
• 2. Check whether form.title exists. The first time this page loads (someone just clicked "Edit"), it doesn't — skip to step 5.
• 3. If the form was submitted, run an UPDATE against that same id with whatever new title/description came from the form.
• 4. Redirect to list.cfm with cflocation, the same as create.cfm does after a successful write.
• 5. Load that one row fresh with a SELECT, and use its current values to pre-fill the form — this is what actually shows on the first visit, with the existing title and description already sitting in the input fields.

<cfset id = val(url.id ?: 0)>

<cfif structKeyExists(form, "title")>
    <cfquery datasource="#application.datasource#">
        UPDATE tasks
        SET title = <cfqueryparam value="#trim(form.title)#" cfsqltype="cf_sql_varchar">,
            description = <cfqueryparam value="#trim(form.description ?: "")#" cfsqltype="cf_sql_varchar">
        WHERE id = <cfqueryparam value="#id#" cfsqltype="cf_sql_integer">
    </cfquery>
    <cflocation url="list.cfm" addtoken="false">
</cfif>

<cfquery name="task" datasource="#application.datasource#">
    SELECT id, title, description FROM tasks
    WHERE id = <cfqueryparam value="#id#" cfsqltype="cf_sql_integer">
</cfquery>

<cfoutput>
<form method="post">
    <input type="text" name="title" value="#task.title#" required>
    <textarea name="description">#task.description#</textarea>
    <button type="submit">Save Changes</button>
</form>
</cfoutput>

Delete: One Query, But Guard It First

Step by step, what delete.cfm does every time it runs:

• 1. Read id from the URL, same as edit.cfm, but pass it through val() first — val() turns anything that isn't a real number into 0.
• 2. Check that id is actually greater than 0. A normal click from list.cfm always has a real id, so this only matters if someone tampers with the URL or the link is somehow broken.
• 3. If it passed that check, run the DELETE against that id.
• 4. Redirect back to list.cfm either way — whether the delete actually ran or was skipped by the guard in step 2.

<cfset id = val(url.id ?: 0)>

<cfif id GT 0>
    <cfquery datasource="#application.datasource#">
        DELETE FROM tasks
        WHERE id = <cfqueryparam value="#id#" cfsqltype="cf_sql_integer">
    </cfquery>
</cfif>

<cflocation url="list.cfm" addtoken="false">

A Real Gotcha: Never Trust an ID Straight From the Request

Every example above pulls id from the URL or a form field, both of which anyone can edit freely before submitting. A generic tasks list with no owner isn't especially risky, but the moment rows belong to a specific user, an edit or delete page has to actually check that the row being touched belongs to the logged-in user — not just that a row with that id happens to exist. Skipping that check means one user could edit or delete another user's data just by changing a number in the URL.

Common Beginner Mistakes

Best Practices

• Keep create and edit forms visually and structurally similar — it makes the codebase, and the user's mental model of the feature, more consistent.
• Always parameterize values in every query in the flow, not just the ones that feel like they involve "user input" — an id from a URL is user input too.
• Redirect after a successful write (cflocation), never just render a result page directly from the same request that performed the write.

Interview Questions

Summary

In this lesson, you built a complete CRUD flow as real pages — a list page, a create form and handler, an edit form and handler that both loads and saves, and a guarded delete — and covered the real security gotcha of trusting an id straight from the request without checking ownership.

What's Next?

That completes Module 10 (Database). The next module covers Files — reading, writing, and uploading files, and working with directories.