Performing CRUD Operations
Master how to Create, Read, Update, and Delete data in your relational databases from Clojure.
Performing CRUD Operations is a free Clojure Functional Programming & JVM Backend Development lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the Clojure Functional Programming & JVM Backend Development learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
What is CRUD?
When you build applications, you'll constantly interact with databases. The fundamental operations for managing data are often summarized by the acronym CRUD.
- Create: Adding new data.
- Read: Retrieving existing data.
- Update: Modifying existing data.
- Delete: Removing data.
In this lesson, we'll master how to perform these essential operations using Clojure's next.jdbc library.
Database Connection Setup
Before we perform CRUD operations, we need a database connection. We'll use next.jdbc with an in-memory SQLite database for our examples.
Remember from the previous lesson, we define a database specification (db-spec) and obtain a data source (ds) or a direct connection.
We'll create a simple users table for our examples.
Create: Inserting New Records
To add new data, we use the next.jdbc.sql/insert! function. It takes a connection, table name, and a map of column-value pairs.
Try running this example to create a users table and insert your first user:
(ns coddykit.core
(:require [next.jdbc :as jdbc]
[next.jdbc.sql :as sql]))
(defn -main
"Insert a new user into the database."
[& args]
(let [db-spec {:dbtype "sqlite" :dbname ":memory:"}]
(with-open [conn (jdbc/get-connection db-spec)]
(jdbc/execute! conn ["DROP TABLE IF EXISTS users;"])
(jdbc/execute! conn ["CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)"])
(println "Inserting new user...")
(let [new-user {:name "Alice" :email "alice@example.com"}
result (sql/insert! conn :users new-user)]
(println "Insert result:" result))
(println "\nUsers in database:")
(doseq [user (sql/select! conn ["SELECT * FROM users"])]
(println user)))))Create: Inserting Multiple Records
What if you need to add many records at once? The next.jdbc.sql/insert-multi! function is perfect for this. It takes a sequence of maps, each representing a row to insert.
It's more efficient than calling insert! repeatedly in a loop.
(ns coddykit.core
(:require [next.jdbc :as jdbc]
[next.jdbc.sql :as sql]))
(defn -main
"Insert multiple users into the database."
[& args]
(let [db-spec {:dbtype "sqlite" :dbname ":memory:"}]
(with-open [conn (jdbc/get-connection db-spec)]
(jdbc/execute! conn ["DROP TABLE IF EXISTS users;"])
(jdbc/execute! conn ["CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)"])
(println "Inserting multiple users...")
(let [new-users [{:name "Bob" :email "bob@example.com"}
{:name "Charlie" :email "charlie@example.com"}]
result (sql/insert-multi! conn :users new-users)]
(println "Insert result:" result))
(println "\nUsers in database:")
(doseq [user (sql/select! conn ["SELECT * FROM users"])]
(println user)))))Read: Fetching All Data
To retrieve all records from a table, use next.jdbc.sql/select-all!. It takes a connection and the table name (as a keyword or string).
The function returns a sequence of maps, where each map represents a row.
(ns coddykit.core
(:require [next.jdbc :as jdbc]
[next.jdbc.sql :as sql]))
(defn -main
"Select all users from the database."
[& args]
(let [db-spec {:dbtype "sqlite" :dbname ":memory:"}]
(with-open [conn (jdbc/get-connection db-spec)]
(jdbc/execute! conn ["DROP TABLE IF EXISTS users;"])
(jdbc/execute! conn ["CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)"])
(sql/insert! conn :users {:name "Alice" :email "alice@example.com"})
(sql/insert! conn :users {:name "Bob" :email "bob@example.com"})
(println "Fetching all users...")
(let [all-users (sql/select-all! conn :users)]
(doseq [user all-users]
(println user))))))Read: Filtering Data with `WHERE`
Often, you only want specific records. Use next.jdbc.sql/select! with a WHERE clause to filter results. The WHERE clause is provided as a map.
For example, to find users with a specific name or email:
(ns coddykit.core
(:require [next.jdbc :as jdbc]
[next.jdbc.sql :as sql]))
(defn -main
"Select users with a specific name."
[& args]
(let [db-spec {:dbtype "sqlite" :dbname ":memory:"}]
(with-open [conn (jdbc/get-connection db-spec)]
(jdbc/execute! conn ["DROP TABLE IF EXISTS users;"])
(jdbc/execute! conn ["CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)"])
(sql/insert! conn :users {:name "Alice" :email "alice@example.com"})
(sql/insert! conn :users {:name "Bob" :email "bob@example.com"})
(sql/insert! conn :users {:name "Alice" :email "alice2@example.com"})
(println "Fetching users named Alice...")
(let [alices (sql/select! conn :users {:name "Alice"})]
(doseq [user alices]
(println user))))))Update: Modifying Existing Data
To change existing records, use next.jdbc.sql/update!. It takes a connection, table name, a map of columns to update, and a WHERE clause map to specify which records to modify.
Always be careful with your WHERE clause when updating!
(ns coddykit.core
(:require [next.jdbc :as jdbc]
[next.jdbc.sql :as sql]))
(defn -main
"Update a user's email address."
[& args]
(let [db-spec {:dbtype "sqlite" :dbname ":memory:"}]
(with-open [conn (jdbc/get-connection db-spec)]
(jdbc/execute! conn ["DROP TABLE IF EXISTS users;"])
(jdbc/execute! conn ["CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)"])
(sql/insert! conn :users {:name "Alice" :email "alice@example.com"})
(println "Original user:")
(println (first (sql/select! conn :users {:name "Alice"})))
(println "\nUpdating Alice's email...")
(let [result (sql/update! conn :users {:email "alice.new@example.com"} {:name "Alice"})]
(println "Update result:" result))
(println "\nUpdated user:")
(println (first (sql/select! conn :users {:name "Alice"})))))Delete: Removing Data
To remove records from a table, use next.jdbc.sql/delete!. Similar to update!, it requires a connection, table name, and a WHERE clause map to specify which records to remove.
A missing or empty WHERE clause can delete ALL records, so use with extreme caution!
(ns coddykit.core
(:require [next.jdbc :as jdbc]
[next.jdbc.sql :as sql]))
(defn -main
"Delete a user from the database."
[& args]
(let [db-spec {:dbtype "sqlite" :dbname ":memory:"}]
(with-open [conn (jdbc/get-connection db-spec)]
(jdbc/execute! conn ["DROP TABLE IF EXISTS users;"])
(jdbc/execute! conn ["CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)"])
(sql/insert! conn :users {:name "Alice" :email "alice@example.com"})
(sql/insert! conn :users {:name "Bob" :email "bob@example.com"})
(println "Users before delete:")
(doseq [user (sql/select-all! conn :users)] (println user))
(println "\nDeleting Bob...")
(let [result (sql/delete! conn :users {:name "Bob"})]
(println "Delete result:" result))
(println "\nUsers after delete:")
(doseq [user (sql/select-all! conn :users)] (println user)))))Atomic Operations with Transactions
Sometimes you need multiple CRUD operations to succeed or fail as a single, atomic unit. This is where transactions come in.
If any operation within a transaction fails, all changes are rolled back, ensuring data consistency. Use next.jdbc/with-transaction for this.
(ns coddykit.core
(:require [next.jdbc :as jdbc]
[next.jdbc.sql :as sql]))
(defn -main
"Demonstrate a database transaction."
[& args]
(let [db-spec {:dbtype "sqlite" :dbname ":memory:"}]
(with-open [conn (jdbc/get-connection db-spec)]
(jdbc/execute! conn ["DROP TABLE IF EXISTS accounts;"])
(jdbc/execute! conn ["CREATE TABLE accounts (id INTEGER PRIMARY KEY, name TEXT, balance INTEGER)"])
(sql/insert! conn :accounts {:name "Alice" :balance 100})
(sql/insert! conn :accounts {:name "Bob" :balance 50})
(println "Balances before transaction:")
(doseq [acc (sql/select-all! conn :accounts)] (println acc))
(println "\nAttempting to transfer $20 from Alice to Bob...")
(try
(jdbc/with-transaction [tx conn]
(sql/update! tx :accounts {:balance [- :balance 20]} {:name "Alice"})
(sql/update! tx :accounts {:balance [+ :balance 20]} {:name "Bob"})
(println "Transfer successful!"))
(catch Exception e
(println "Transfer failed:" (.getMessage e))))
(println "\nBalances after transaction:")
(doseq [acc (sql/select-all! conn :accounts)] (println acc)))))CRUD Quick Check
You've learned the core functions for interacting with databases. Let's test your understanding!
Recap: Mastering CRUD
Congratulations! You've mastered the essential CRUD operations for database interaction in Clojure using next.jdbc:
- Create:
insert!andinsert-multi!to add data. - Read:
select!andselect-all!to retrieve data. - Update:
update!to modify existing data. - Delete:
delete!to remove data.
You also learned about using transactions with with-transaction for atomic operations, ensuring data integrity. These are the building blocks for almost any data-driven application!
Frequently asked questions
Is the “Performing CRUD Operations” lesson free?
Yes — the full text of “Performing CRUD Operations” is free to read here on the web, and the Clojure Functional Programming & JVM Backend Development course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the Clojure Functional Programming & JVM Backend Development course, upgrade to CoddyKit PRO.
What will I learn in “Performing CRUD Operations”?
Master how to Create, Read, Update, and Delete data in your relational databases from Clojure. You practise Clojure Functional Programming & JVM Backend Development with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start Clojure Functional Programming & JVM Backend Development?
No prior experience is required. Clojure Functional Programming & JVM Backend Development on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Performing CRUD Operations” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this Clojure Functional Programming & JVM Backend Development lesson?
Yes. Every Clojure Functional Programming & JVM Backend Development lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.