canonical / sqlair

Friendly type mapping for SQL databases
Apache License 2.0
17 stars 8 forks source link

Add support for basic types #97

Closed Aflynn50 closed 2 months ago

Aflynn50 commented 11 months ago

Add support for numeric, boolean and string types for use in input and output expressions. Specifically the types: string, bool, uint, uint8, uint16, uint32, uint64, int, int8, int16, int32, int64, float32, float64.

The types complex64 and complex128 are not supported by the Scan method of database/sql and I see no strong need to add them here. The type byte and rune are aliases for uint8 and int32 respectively and cannot be distinguished from the aliases using the reflect library so are not added here.

This change will allow queries such as SELECT name AS &string FROM ... and SELECT id AS &int FROM .... Previously these queries would have required a struct or map to extract the result into.

A limitation of being able to reference primitive types in I/O expressions is that each type can only be referenced once in the inputs and once in the outputs. For example the query SELECT name AS &string, address AS &string FROM ... is not valid. This is because when the user calls db.Query(...).Get(str1, str2) there is no way for SQLair to tell which column corrosponds to which variable. To get around this one can define new types that have the same underlying type. For example:

type nameStr string
type addressStr string
stmt, err := sqlair.Prepare(
    "SELECT name AS &nameStr, address AS &addressStr FROM ...",
    (nameStr)(""),
    (addressStr)(""),
)
var name nameStr
var address addressStr
err := db.Query(ctx, stmt).Get(&name, &address)

This PR also includes #98 which fixes a bug in detecting duplicate types.

jameinel commented 11 months ago

From a conceptual perspective, I don't have any problem with the awkwardness around multiple base types. And this is because you really want to be using mappings that have more meaning to them. Put a different way, you could write a function that is myfunc(bool, bool, bool), but that is usually a terrible function, because for an end user what is the difference between myfunc(true, true, false) and myfunc(true, true, true). You almost always would rather have:

is_open := true
check_size := true
verbose := false
myfunc(is_open, check_size, verbose)

Similarly here, having a SQL statement that just returns a 'string' makes it hard to be clear that you've lined them up correctly.

SELECT name, address FROM people;
db.Query().Get(&name, &address)

With only plain string types it would be:

SELECT name as &string, address as &string FROM people;

But both of these lines work without errors:

db.Query().Get(&name, &address)
db.Query().Get(&address, &name)

So it is clearly better to have

type address string
type name string
...
SELECT name as &name, address as &address

I think it makes sense to have a fast path for "I just have 1 string value to return", but as soon as there is confusion, it is better to be clear rather than rely on position to disambiguate.

In fact, I would probably document the type aliasing with something more realistic than 'string1' and 'string2' because it is fairly likely that people might actually think they should be using those integer variants.