Currently the database modules (db_mysql, db_postgre, and db_sqlite) all rely
on using `nil` to represent NULL when passing data in/out of the module.
As most folks know, Nim is moving away from production support of `nil`, so it
would be good to make changes to those libraries. I'm happy to help with this,
but rather arbitrarily deciding on a solution, I want to get opinions and/or
direction from the community.
But, to get started, let's formally describe the current solution:
**CURRENT**
Rows of data are passed in/out using:
seq[string]
Run
Where NULL values are represented by entries with a `nil`. All data types are
represented by the string equivalent of the data.
So, if you have three columns of "name VARCHAR(50), desc VARCHAR(50), age INT"
and "desc" is NULL, you would pass:
@["joe", nil, "30"]
Run
I see four ways we could change this:
**STRINGIFY**
Rows of data are passed in/out using:
seq[string]
Run
Where NULL values are represented by entries with the strings NULL. Strings are
quoted. All other data types are represented by the string equivalent of the
data.
So, if you have three columns of "name VARCHAR(50), desc VARCHAR(50), age INT"
and "desc" is NULL, you would pass:
@["'joe'", "NULL", "30"]
Run
notice the extra inner quotes around the real string value.
**OPTION[STRING]**
Rows of data are passed in/out using:
seq[Option[string]]
Run
Where NULL values are represented by entries with none. All other data types
are represented by the string equivalent of the data.
So, if you have three columns of "name VARCHAR(50), desc VARCHAR(50), age INT"
and "desc" is NULL, you would pass:
@[some[string]("joe"), none(string), some[string]("30")]
Run
**TUPLES**
Rows of data are passed in/out using:
tuple[data: seq[string], are_null: seq[bool]]
Run
The data types are represented by the string equivalent of the data, and the
null items are indicated in the separate sequence.
So, if you have three columns of "name VARCHAR(50), desc VARCHAR(50), age INT"
and "desc" is NULL, you would pass:
(data: @["joe", "", "30"], are_null: @[false, true, false])
Run
**JSON**
Rows of data are passed in/out using:
seq[JsonNode]
Run
Where all data types are represented by their JSON equivalents.
So, if you have three columns of "name VARCHAR(50), desc VARCHAR(50), age INT"
and "desc" is NULL, you would pass:
@[parseJson("""["joe", null, 30]""")]
Run
**NULLABLE**
Rows of data are passed in/out using:
seq[nstring]
Run
Where all data types are represented by string equivalents.
So, if you have three columns of "name VARCHAR(50), desc VARCHAR(50), age INT"
and "desc" is NULL, you would pass:
@["joe", NULL, "30"]
Run
Note: I've not actually finished this type library yet. In fact, I only have
`nint`'s type operations fully flushed out.
Okay, and now:
NOTES:
* A subtle problem with both the CURRENT method and the OPTION[STRING] method
is that nil and none are best interpreted as "no value". But on a database,
NULL which means "unknown value". That is why, in SQL, "NULL != NULL". Two real
but unknown values cannot be assumed to be equal.
[https://www.essentialsql.com/get-ready-to-learn-sql-server-what-is-a-null-value](https://www.essentialsql.com/get-ready-to-learn-sql-server-what-is-a-null-value)/
Handling this correctly solves many odd border-case scenarios, especially with
aggregation functions.
* I mention my "nullable" database types library to be complete. I'm _not_
pushing for this. In fact, using that would require that nullable be added to
the standard library, which I'm not entirely sure is a good idea. But, if
curious, details are at
[https://github.com/JohnAD/nullable](https://github.com/JohnAD/nullable) .
* Personal option, so far: I'm really not a fan on using JSON given the
overhead that would raise. I think that STRINGABLE and OPTION[STRING] are the
best candidates. But, I'm happy to go along with the community.
