How do I map database columns to a struct in a list of structs in Racket?

Question

I have a list called attendance-events, consisting of struct type attendance-event. The struct is defined as

(define-struct attendance-event (date flag))

The "date" is the date of the event and "flag" is a 2 character value that represents the type of event.

In my database, I am selecting "select date, code from attendance_table", and I want those two columns to be added to the list of attendance-events for each row.

So, how can this be done?

Answer 1

This performs the query and returns a list of attendance-event structures:

(for/list ([(date code)
            (in-query conn "select date, code from attendance_table")])
  (make-attendance-event date code))

Answer 2

I find it much easier to work with database rows after they've been converted to hash tables. The following function converts the rows-result type returned by the query function into a list of immutable hashes:

(require (planet jphelps/loop)) ;; Use the loop macro from Common Lisp.

(define (rows-result->hash results)
  (let ((header (rows-result-headers results)))
    (loop for row in (rows-result-rows results)
          collect (loop for ((_ . name) (_ . decltype)) in header
                        for column across row
                        with-collection-type hash/immutable
                        collect (cons name column)))))

Then you can wrap the query function like this:

(define (simple-query query-text conn . params)
  (rows-result->hash (apply query (list* conn query-text params))))

Finally, to create your struct:

(loop for row in (simple-query "select date,code from attendance_table;")
      collect (make-attendance-event (hash-ref row 'date) (hash-ref row 'code)))