Meddler

Meddler is a small toolkit to take some of the tedium out of moving data back and forth between SQL queries and structs.

It is not a complete ORM. Meddler is intended to be a lightweight way to add some of the convenience of an ORM while leaving more control in the hands of the programmer.

Package docs are available at:

• http://godoc.org/github.com/russross/meddler

The package is housed on GitHub, and the README there has more info:

• http://github.com/russross/meddler

Meddler is currently configured for SQLite, MySQL, and PostgreSQL, but it can be configured for use with other databases. If you use it successfully with a different database, please contact me and I will add it to the list of pre-configured databases.

DANGER

Meddler is still a work in progress, and additional backward-incompatible changes to the API are likely. The most recent change added support for multiple database types and made it easier to switch between them. This is most likely to affect the way you initialize the library to work with your database (see the install section below).

Another recent update is the change to int64 for primary keys. This matches the convention used in database/sql, and is more portable, but it may require some minor changes to existing code.

Install

The usual go get command will put it in your $GOPATH:

go get github.com/russross/meddler

If you are only using one type of database, you should set Default to match your database type, e.g.:

meddler.Default = meddler.PostgreSQL

The default database is MySQL, so you should change it for anything else. To use multiple databases within a single project, or to use a database other than MySQL, PostgreSQL, or SQLite, see below.

Note: If you are using MySQL with the github.com/go-sql-driver/mysql driver, you must set "parseTime=true" in the sql.Open call or the time conversion meddlers will not work.

Why?

These are the features that set meddler apart from similar libraries:

• It uses standard database/sql types, and does not require special fields in your structs. This lets you use meddler selectively, without having to alter other database code already in your project. After creating meddler, I incorporated it into an existing project, and I was able to convert the code one struct and one query at a time.
• It leaves query writing to you. It has convenience functions for simple INSERT/UPDATE/SELECT queries by integer primary key, but beyond that it stays out of query writing.
• It supports on-the-fly data transformations. If you have a map or a slice in your struct, you can instruct meddler to encode/decode using JSON or Gob automatically. If you have time fields, you can have meddler automatically write them into the database as UTC, and convert them to the local time zone on reads. These processors are called “meddlers”, because they meddle with the data instead of passing it through directly.
• NULL fields in the database can be read as zero values in the struct, and zero values in the struct can be written as NULL values. This is not always the right thing to do, but it is often good enough and is much simpler than most alternatives.
• It exposes low-level hooks for more complex situations. If you are writing a query that does not map well to the main helper functions, you can still get some help by using the lower-level functions to build your own helpers.

High-level functions

Meddler does not create or alter tables. It just provides a little glue to make it easier to read and write structs as SQL rows. Start by annotating a struct:

type Person struct {
    ID      int       `meddler:"id,pk"`
    Name    string    `meddler:"name"`
    Age     int
    salary  int
    Created time.Time `meddler:"created,localtime"`
    Closed  time.Time `meddler:",localtimez"`
}

Notes about this example:

• If the optional tag is provided, the first field is the database column name. Note that "Closed" does not provide a column name, so it will default to "Closed". Likewise, if there is no tag, the field name will be used.
• ID is marked as the primary key. Currently only integer primary keys are supported. This is only relevant to Load, Save, Insert, and Update, a few of the higher-level functions that need to understand primary keys. Meddler assumes that pk fields have an autoincrement mechanism set in the database.
• Age has a column name of "Age". A tag is only necessary when the column name is not the same as the field name, or when you need to select other options.
• salary is not an exported field, so meddler does not see it. It will be ignored.
• Created is marked with "localtime". This means that it will be converted to UTC when being saved, and back to the local time zone when being loaded.
• Closed has a column name of "Closed", since the tag did not specify anything different. Closed is marked as "localtimez". This has the same properties as "localtime", except that the zero time will be saved in the database as a null column (and null values will be loaded as the zero time value).
• You can set a default column name mapping by setting meddler.Mapper to a func(s string) string function. For example, meddler.Mapper = meddler.SnakeCase will convert field names to snake_case unless an explict column name is specified.

Meddler provides a few high-level functions (note: DB is an interface that works with a *sql.DB or a *sql.Tx):

• Load(db DB, table string, dst interface{}, pk int64) error

  This loads a single record by its primary key. For example:

  elt := new(Person)
  err := meddler.Load(db, "person", elt, 15)

  db can be a *sql.DB or a *sql.Tx. The table is the name of the table, pk is the primary key value, and dst is a pointer to the struct where it should be stored.

  Note: this call requires that the struct have an integer primary key field marked.

• Insert(db DB, table string, src interface{}) error

  This inserts a new row into the database. If the struct value has a primary key field, it must be zero (and will be omitted from the insert statement, prompting a default autoincrement value).

  elt := &Person{
      Name: "Alice",
      Age: 22,
      // ...
  }
  err := meddler.Insert(db, "person", elt)
  // elt.ID is updated to the value assigned by the database

• Update(db DB, table string, src interface{}) error

  This updates an existing row. It must have a primary key, which must be non-zero.

  Note: this call requires that the struct have an integer primary key field marked.

• Save(db DB, table string, src interface{}) error

  Pick Insert or Update automatically. If there is a non-zero primary key present, it uses Update, otherwise it uses Insert.

  Note: this call requires that the struct have an integer primary key field marked.

• QueryRow(db DB, dst interface{}, query string, args ...interface) error

  Perform the given query, and scan the single-row result into dst, which must be a pointer to a struct.

  For example:

  elt := new(Person)
  err := meddler.QueryRow(db, elt, "select * from person where name = ?", "bob")

• QueryAll(db DB, dst interface{}, query string, args ...interface) error

  Perform the given query, and scan the results into dst, which must be a pointer to a slice of struct pointers.

  For example:

  var people []*Person
  err := meddler.QueryAll(db, &people, "select * from person")

• Scan(rows *sql.Rows, dst interface{}) error

  Scans a single row of data into a struct, complete with meddling. Can be called repeatedly to walk through all of the rows in a result set. Returns sql.ErrNoRows when there is no more data.

• ScanRow(rows *sql.Rows, dst interface{}) error

  Similar to Scan, but guarantees that the rows object is closed when it returns. Also returns sql.ErrNoRows if there was no row.

• ScanAll(rows *sql.Rows, dst interface{}) error

  Expects a pointer to a slice of structs/pointers to structs, and appends as many elements as it finds in the row set. Closes the row set when it is finished. Does not return sql.ErrNoRows on an empty set; instead it just does not add anything to the slice.

Note: all of these functions can also be used as methods on Database objects. When used as package functions, they use the Default Database object, which is MySQL unless you change it.

Meddlers

A meddler is a handler that gets to meddle with a field before it is saved, or when it is loaded. "localtime" and "localtimez" are examples of built-in meddlers. The full list of built-in meddlers includes:

• identity: the default meddler, which does not do anything

• localtime: for time.Time and *time.Time fields. Converts the value to UTC on save, and back to the local time zone on loads. To set your local time zone, make sure the TZ environment variable is set when your program is launched, or use something like:

  os.Setenv("TZ", "America/Denver")

  in your initial setup, before you start using time functions.

• localtimez: same, but only for time.Time, and treats the zero time as a null field (converts both ways)

• utctime: similar to localtime, but keeps the value in UTC on loads. This ensures that the time is always coverted to UTC on save, which is the sane way to save time values in a database.

• utctimez: same, but with zero time means null.

• zeroisnull: for other types where a zero value should be inserted as null, and null values should be read as zero values. Works for integer, unsigned integer, float, complex number, and string types. Note: not for pointer types.

• json: marshals the field value into JSON when saving, and unmarshals on load.

• jsongzip: same, but compresses using gzip on save, and uncompresses on load

• gob: encodes the field value using Gob when saving, and decodes on load.

• gobgzip: same, but compresses using gzip on save, and uncompresses on load

You can implement custom meddlers as well by implementing the Meddler interface. See the existing implementations in medder.go for examples.

Working with different database types

Meddler can work with multiple database types simultaneously. Database-specific parameters are stored in a Database struct, and structs are pre-defined for MySQL, PostgreSQL, and SQLite.

Instead of relying on the package-level functions, use the method form on the appropriate database type, e.g.:

err = meddler.PostgreSQL.Load(...)

instead of

err = meddler.Load(...)

Or to save typing, define your own abbreviated name for each database:

ms := meddler.MySQL
pg := meddler.PostgreSQL
err = ms.Load(...)
err = pg.QueryAll(...)

If you need a different database, create your own Database instance with the appropriate parameters set. If everything works okay, please contact me with the parameters you used so I can add the new database to the pre-defined list.

Lower-level functions

If you are using more complex queries and just want to reduce the tedium of reading and writing values, there are some lower-level helper functions as well. See the package docs for details, and see the implementations of the higher-level functions to see how they are used.

License

Meddler is distributed under the BSD 2-Clause License. If this license prevents you from using Meddler in your project, please contact me and I will consider adding an additional license that is better suited to your needs.

Copyright © 2013 Russ Ross. All rights reserved.

Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:

1. Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.

2. Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.