When handling null values, things can get a bit more complicated. Null values can be problematic because they are not equal to any other value, including other null values. This article will discuss how we can handle null values in Go.
Handling null values can sometimes be problematic when working with SQL databases in Go. Here are some of the problems that may arise when dealing with null values in SQL with Golang:
Data type compatibility: When working with null values, ensuring that the data types of the database columns and the Go variables match is important. If the types do not match, it can cause errors when retrieving or storing null values.
Querying difficulties: Querying a database for null values can be challenging, as null values are not equal to any other value, including other null values. Writing SQL queries that retrieve the correct data can make it difficult.
Parsing issues: When retrieving null values from a database, it can be challenging to parse the results in Go. This is because null values in SQL are often represented as a special value, such as "NULL" or "nil", which may not map directly to the Go "nil" value.
Debugging difficulties: Debugging SQL queries that involve null values can be difficult, as it is not always clear where the null value originated from. This can make it challenging to identify and fix errors in the code.
The most seen error by everyone looks something like this-
sql: Scan error on column index 0, name "address": converting NULL to string is unsupported
sql: Scan error on column index 0, name "age": converting NULL to int is unsupported
Pointer
Firstly, let us understand the concept of pointers in Go. Pointers are variables that store memory addresses. They are used to point to the memory location where a value is stored rather than directly storing it. When we want to access or modify the value stored in a memory location, we use the pointer to access it. However, pointers' ability to handle null values is another benefit.
type User struct {
Name string
Age *int
Address *string
}
If we declare a pointer variable and do not assign a value to it, the pointer variable will be set to nil.
To handle null values in Go, we can use the if statement to check if the pointer variable is nil before we try to access or modify the value it points to. This helps to prevent null pointer exceptions and unexpected errors in our code.
Null handing in Golang
sql.Null*
A set of types in the Go standard library allows for handling null values in database queries. These types include sql.NullString, sql.NullInt64, sql.NullFloat64, and sql.NullBool is designed to be used with the standard database/sql package.
The sql.Null* types represent nullable versions of their corresponding data types, with two fields: Valid and Value. The Valid field indicates whether the value is null, and the Value field stores the value itself. This allows you to safely handle null values without encountering runtime errors.
Here is an example of how we can define a struct-
type User struct {
Name string
Age sql.NullInt64
Address sql.NullString
}
Some of the columns may contain null values when we retrieve data from a database. We can use the type to handle these null values in such cases. For example, suppose we have a table with columns name and address, and the name column contains null values for some of the rows. We can retrieve the name and address columns using the Query method of the sql.DB or sql.Tx type, and then iterate over the rows using the Rows method. Here is an example:
rows, err := db.Query("SELECT name, age, address FROM customers")
if err != nil {
log.Fatal(err)
}
defer rows.Close()
for rows.Next() {
var user User
err := rows.Scan(&user.Name, &user.Age, &user.Address)
if err != nil {
log.Fatal(err)
}
fmt.Printf("Name: %s\n", user.Name)
if user.Age != nil {
fmt.Printf("Age: %s\n", &user.Age)
} else {
fmt.Println("Age: <null>")
}
if user.Address != nil {
fmt.Printf("Address: %s\n", &user.Address)
} else {
fmt.Println("Address: <null>")
}
}
In this example, we use the Scan method to read the name, age, and address columns from the current row of the result set. We store the value of the address column in a sql.NullString Variable and age in sql.NullInt64. If the name column is null, the Valid field of the name variable will be false, the String field will be empty, and the same goes for age. We can use the Valid field to check if the column is null, and if it is, we can print <null> instead of the empty string.
github.com/guregu/null
A Go package that provides a set of nullable types for handling null values in database queries. The package supports several data types, including string, int, float, bool, time.Time and slices, and provides convenient methods for handling null values, such as checking whether a value is null. Additionally, it allows you to set a default value for null fields, which can be useful for handling missing or incomplete data.
One of the key advantages of github.com/guregu/null is its flexibility, as it can be used with SQL and NoSQL databases. It also provides additional features beyond what is available with the standard database/sql package, such as support for time.Time and slices, and methods for handling common operations.
Here is an example of how we can define a struct.
type User struct {
Name string
Age null.Int64
Address null.String
}
With null.String, we can create a null string by setting the Valid field to false and leaving the Value field empty. If we have a function that expects a non-empty string, we can check the Valid field of the null.String struct to ensure the string is not null before using it. Here is a real-life example:
rows, err := db.Query("SELECT name, age, address FROM customers")
if err != nil {
log.Fatal(err)
}
defer rows.Close()
for rows.Next() {
var user User
err := rows.Scan(&user.Name, &user.Age, &user.Address)
if err != nil {
log.Fatal(err)
}
fmt.Printf("Name: %s\n", user.Name)
if user.Age.Valid {
fmt.Printf("Age: %s\n", user.Age.Int64)
} else {
fmt.Println("Age: <null>")
}
if user.Address.Valid {
fmt.Printf("Address: %s\n", user.Address.String)
} else {
fmt.Println("Address: <null>")
}
}
This example is the same as above. Using sql.Null* and github.com/guregu/null feels the same as they have the same ‘valid’ field and data-type field. Comparing these packages on the code level, they work exactly in the same manner but also have some differences.
Both sql.Null* and github.com/guregu/null are useful for handling null values in Golang, and which one is better depends on your specific use case.
If you are using the standard
database/sqlpackage with a SQL database,sql.Null*is a good choice because it is part of the standard library and is designed to work seamlessly withdatabase/sql. This makes using standard SQL database drivers easier and allows for more efficient database communication.However, if you need more advanced features or are using a NoSQL database, github.com/guregu/null might be a better choice. This package provides additional data types and convenience methods, such as checking whether a value is null. Additionally, it allows you to set a default value for null fields, which can be useful for handling missing or incomplete data.
In summary, both sql.Null* and github.com/guregu/null are useful for handling null values, but the choice between them depends on your specific requirements and the context in which you use them.
SQL NULL Functions
Null functions in SQL are used to handle null values within a database.
Let's use some null functions in SQL statements to get the "name", “age” and “address” columns from the "customers" table. If these columns' values are empty, we expect the default age to be 24 and the default address to be “empty string.
IFNULL Function
The IFNULL function is used to check if the first argument is null or not. It returns the second argument if the first argument is null. Otherwise, it returns the first argument.
SELECT
name,
IFNULL(age, 24) AS age,
IFNULL(address, '') AS address,
FROM customers;
COALESCE Function
The COALESCE function returns the first non-null value in a list of values. This function takes two or more arguments and returns the first argument that is not null.
SELECT
name,
COALESCE(age, 24) AS age,
COALESCE(address, $1,'') AS address,
FROM customers;
$1 will get replaced with the value passed in arguments while querying. If $1 is null, then the next argument will be returned.
These NULL functions vary depending on the SQL Server used, but all the servers provide these functions.
Thank you for following along through this journey. I hope you found it helpful and informative. Feel free to reach out to Gyanesh Sharma