Dead simple SQL data mapper for complex Go structs.
Load SQL data onto Go structs while keeping track of has-one and has-many relationships
Using carta is very simple. All you need to do is:
// 1) Run your query
if rows, err = sqlDB.Query(blogQuery); err != nil {
// error
}
// 2) Instantiate a slice(or struct) which you want to populate
blogs := []Blog{}
// 3) Map the SQL rows to your slice
carta.Map(rows, &blogs)
Assume that in above exmple, we are using a schema containing has-one and has-many relationships:
And here is our SQL query along with the corresponging Go struct:
select
id as blog_id,
title as blog_title,
P.id as posts_id,
P.name as posts_name,
A.id as author_id,
A.username as author_username
from blog
left outer join author A on blog.author_id = A.id
left outer join post P on blog.id = P.blog_id
type Blog struct {
Id int `db:"blog_id"`
Title string `db:"blog_title"`
Posts []Post
Author Author
}
type Post struct {
Id int `db:"posts_id"`
Name string `db:"posts_name"`
}
type Author struct {
Id int `db:"author_id"`
Username string `db:"author_username"`
}
Carta will map the SQL rows while keeping track of those relationships.
Results:
rows:
blog_id | blog_title | posts_id | posts_name | author_id | author_username
1 | Foo | 1 | Bar | 1 | John
1 | Foo | 2 | Baz | 1 | John
2 | Egg | 3 | Beacon | 2 | Ed
blogs:
[{
"blog_id": 1,
"blog_title": "Foo",
"author": {
"author_id": 1,
"author_username": "John"
},
"posts": [{
"post_id": 1,
"posts_name": "Bar"
}, {
"post_id": 2,
"posts_name": "Baz"
}]
}, {
"blog_id": 2,
"blog_title": "Egg",
"author": {
"author_id": 2,
"author_username": "Ed"
},
"posts": [{
"post_id": 3,
"posts_name": "Beacon"
}]
}]
Carta is NOT an an object-relational mapper(ORM). Read more in Approach
Sqlx does not track has-many relationships when mapping SQL data. This works fine when all your relationships are at most has-one (Blog has one Author) ie, each SQL row corresponds to one struct. However, handling has-many relationships (Blog has many Posts), requires running many queries or running manual post-processing of the result. Carta handles these complexities automatically.
Carta will match your SQL columns with corresponding fields. You can use a "db" tag to represent a specific column name. Example:
type Blog struct {
// When tag is not used, the snake case of the fiels is used
BlogId int // expected column name : "blog_id"
// When tag is specified, it takes priority
Abc string `db:"blog_title"` // expected column name: "blog_title"
// If you define multiple fiels with the same struct,
// you can use a tag to identify a column prefix
// (with underscore concatination)
// possible column names: "writer_author_id", "author_id"
Writer Author `db: "writer"`
// possible column names: "rewiewer_author_id", "author_id",
Reviewer Author `db: "reviewer"`
}
type Author struct {
AuthorId int `db:"author_id"`
}
Any primative types, time.Time, protobuf Timestamp, and sql.NullX can be loaded with Carta. These types are one-to-one mapped with your SQL columns
To define more complex SQL relationships use slices and structs as in example below:
type Blog struct {
BlogId int // Will map directly with "blog_id" column
// If your SQL data can be "null", use pointers or sql.NullX
AuthorId *int
CreatedOn *timestamp.Timestamp // protobuf timestamp
UpdatedOn *time.Time
SonsorId sql.NullInt64
// To define has-one relationship, use nested structs
// or pointer to a struct
Author *Author
// To define has-many relationship, use slices
// options include: *[]*Post, []*Post, *[]Post, []Post
Posts []*Post
// If your has-many relationship corresponds to one column,
// you can use a slice of a settable type
TagIds []int `db:"tag_id"`
CommentIds []sql.NullInt64 `db:"comment_id"`
}
Recommended driver for Postgres is lib/pg, for MySql use go-sql-driver/mysql.
When using MySql, carta expects time data to arrive in time.Time format. Therefore, make sure to add "parseTime=true" in your connection string, when using DATE and DATETIME types.
Other types, such as TIME, will will be converted from plain text in future versions of Carta.
go get -u github.com/jackskj/carta
Carta removes any duplicate rows. This is a side effect of the data mapping as it is unclear which object to instantiate if the same data arrives more than once. If this is not a desired outcome, you should include a uniquely identifiable columns in your query and the corresponding fields in your structs.
To prevent relatively expensive reflect operations, carta caches the structure of your struct using the column mames of your query response as well as the type of your struct.
Carta adopts the "database mapping" approach (described in Martin Fowler's book) which is useful among organizations with strict code review processes.
Carta is not an object-relational mapper(ORM). With large and complex datasets, using ORMs becomes restrictive and reduces performance when working with complex queries.
Apache License