Executing SQL Queries in Go: Query vs. Exec
When working with databases in Go, you'll frequently need to interact with your SQL database. The
database/sql
Query
Exec
The `Query` Method: Retrieving Data
The
Query
SELECT
*sql.Rows
`Query` is for `SELECT` statements that return data.
Use Query when you need to fetch records from your database. It returns a *sql.Rows object, which you'll then iterate over to access each row's columns.
The Query method takes a SQL query string and any arguments to be passed to it. It returns a *sql.Rows and an error. If an error occurs during execution, *sql.Rows will be nil. You must always call rows.Close() when you are finished with the result set to release the database connection. Iterating through the rows is done using the rows.Next() method, and you retrieve column values using rows.Scan().
The `Exec` Method: Modifying Data
The
Exec
INSERT
UPDATE
DELETE
CREATE TABLE
sql.Result
`Exec` is for statements that modify data or schema.
Use Exec for operations like inserting, updating, or deleting records. It returns a sql.Result which tells you how many rows were affected.
Similar to Query, Exec takes a SQL query string and arguments. The sql.Result interface has methods like RowsAffected() to get the count of modified rows and LastInsertId() to retrieve the ID of the last inserted row (if applicable). Exec does not return *sql.Rows because no result set is expected.
Key Differences Summarized
| Feature | Query | Exec |
|---|---|---|
| Purpose | Retrieve data (e.g., SELECT) | Modify data/schema (e.g., INSERT, UPDATE, DELETE, CREATE) |
| Return Value | *sql.Rows (for iterating results) | sql.Result (for affected rows, last insert ID) |
| Data Expected | Yes, multiple rows and columns | No, only execution status |
| Resource Management | Requires rows.Close() | No specific resource to close from the method itself |
Always remember to close your *sql.Rows after using Query to prevent resource leaks.
Illustrative Example: Inserting and Selecting
Let's consider a common scenario: inserting a new user and then fetching all users. We'll use
Exec
Query
Imagine a simple user table with id (integer, primary key) and name (text). We first use Exec to add a new user, then Query to retrieve all users. The Exec call returns a sql.Result object, from which we can get the LastInsertId. The Query call returns *sql.Rows, which we then iterate through using rows.Next() and rows.Scan() to extract the id and name for each user.
Text-based content
Library pages focus on text content
DELETE FROM users WHERE id = ? statement?You should use the Exec method because it modifies data and does not return rows.
Query method in Go's database/sql package?The primary return type is *sql.Rows.
Learning Resources
The official Go documentation for the database/sql package, detailing the `Query` and `Exec` methods and their usage.
A clear and practical explanation of how to use `Query` and `Exec` with code examples in Go.
An overview of database access in Go, including best practices and common patterns for using the database/sql package.
A tutorial covering basic database operations in Go, including examples of executing SQL queries.
A Medium article that breaks down the differences between `Query` and `Exec` with illustrative code snippets.
A comprehensive guide to using Go's standard library for database interactions, covering query execution.
Focuses on how to effectively handle the results returned by `Query` and `Exec`, including error management.
While not directly about `Query` vs `Exec`, this resource is crucial for understanding safe query parameterization, which is vital when using both methods.
A collection of best practices for working with databases in Go, including tips on efficient query execution.
A video tutorial that demonstrates database access in Go, likely covering `Query` and `Exec` in practical examples.