It is currently not possible to determine if a field is null in the database (as far as I have found). Using the built in sql.NullString or sql.NullInt64 result in them always being set to invalid and nil.

At least on MySQL, SQLite and PostgreSQL.

There is a similar method on MongoDB's db.collection, but I'm not sure if we could use it.

A debug mode could print query data to stdout.

I'm looping a document at a time (will eventually start batching inserts if I can) and when I do, I'm getting some empty documents in the collection.

I went ahead and checked that the struct was valid before saving and logged the data being saved - there's nothing that's empty. It just randomly inserts empty documents. There's an ObjectId value and nothing else.

I see all the other documents I've sent off to be saved too, so that part is good =)

I really like the design of upper/db, it's super clean and minimal. However, I believe db.Open() should accept an array of settings objects. For example, it's common with mongodb to pass a bunch of hosts which are in a cluster and then the client will determine the master, etc. etc. I presume MySQL / Postgresl would have something similar in a master/slave arrangement. Thoughts?

In, https://github.com/upper/db/blob/master/ql/database.go ...
I see a bunch of functions, but I don't see how to actually create a table.

I see how you can append to a table (collection), I see that you can empty it, I see you can drop a database, but I just can't see how you create a table.

Is it currently possible? Thanks.

I get:

panic: Open: Unknown adapter . (see: https://upper.io/db#database-adapters)

I've passed both postgres.Adapter in as well as string "postgresql" to db.Open() ... I have all the packages too.

Attaching screenshot:

upper.io/db lacks support for statements like:

SELECT * FROM cool_table WHERE id in (1, 2, 3, 4);

Or similar NoSQL statements like:

db.find({_id: {$in: [ 1, 2, 3, 4 ]}})

First of all, thank you for developing such great library - it really simplified development of my software, because now I don't have to care about weird ORM magic.

The only issue is that whenever I create a new struct with the SERIAL column (ie. primary key) not set, it tries to create a new row with id 0 (because uninitialized int64 is 0). How can I use the nextval feature in PostgreSQL appends? If it's not possible, could you add such feature / shortly explain how can I add it on my own?

It currently freezed my development, as I can't finish the CRUD functionality of my API, so it'd be great if you could sort it out quickly.

When trying to connect to a PostgreSQL database with wrong credentials the adapter does not return an error value.

This would allow custom datatypes to be saved and retrieved to and from permanent storage.

In my current project I'm performing deletes on sets of records. Right now I have to use a fork of upper.io/db, which supports the IN() structure, in order to perform such action using a single query. My code isn't really clean and I'd rather not want it to be used publicly (as it's probably bugged with non-standard types). It would be great if someone implemented it in a way similar to the rest of the package.

type User struct {
    Id        int       `db:"id,omitempty" json:"id"`
    Username  string    `db:"username" json:"username"`
    Password  string    `db:"password" json:"password,omitempty"`
    Email     string    `db:"email" json:"email"`
    CreatedAt time.Time `db:"created_at" json:"created_at"`
    IsAdmin   bool      `db:"is_admin" json:"is_admin"`
    Verified  bool      `db:"verified" json:"verified"`
    PackageId int       `db:"package_id" json:"package_id"`
}

CREATE TABLE users (
    id            integer primary key,
    username      text,
    password      text,
    email         text,
    created_at    datetime,
    is_admin      boolean,
    verified      boolean,
    package_id    integer,
    FOREIGN KEY(package_id) REFERENCES packages(id)
);

    var user models.User
    err = env.G.Collections["users"].Find(db.Cond{
        "email": request.Email,
    }).One(&user)

WARN[0099] sql: Scan error on column index 4: unsupported driver -> Scan pair: time.Time -> *sql.RawBytes

What am I doing incorrectly?

Executing the command go get upper.io/db returns this:

package upper.io/db: unrecognized import path "upper.io/db"

It used to work, but it doesn't work now.

I've also tried go get -d upper.io/db. Same result. That means git/hg can't even download the package. Seems to be a hosting server setting issue.

(UPDATED: go get works only if no GOPATH is not set through env variable)

import (
    "upper.io/db"
    _ "upper.io/db/mysql"
)

var dbSettings = db.Settings{
    Host:     `127.0.0.1`,
    Database: `db_name`,
    User:     `user_name`,
    Password: `password`,
}

sess, err = db.Open("mysql", dbSettings)

this will give me undefined: sess and undefined: err

help?

golint throws lots of suggestions on the mongo/ directory that we need to address.

Now does not support array or user-defined type for PostgreSQL

I'm proposing the addition of C() to the Database interface, which is a combined short-hand to what would be MustCollection(). Saves a bunch of boilerplate code when querying a db.

See: pkieltyka@1e34dd8

.. doing PR's is difficult since the import paths break

Currently I'm facing the following issue.
I would like to do some date/time comparison queries like:
SELECT * FROM mytable WHERE DATE(mydate) = DATE(NOW())

Q: SELECT * FROM mytable WHERE (DATE(mydate) = ?) LIMIT 1000
A: [0000]
E: "Error 1054: Unknown column 'DATE(shipping_date)' in 'where clause'"
T: 0.00039s

What is the best way to approach this issue?

.. the issue is if a program imports labix.org/v2/mgo instead of what is normally used, and suggested: gopkg.in/mgo.v2. Even the examples in https://labix.org/mgo use it.

Perhaps just keep it in mind, the effect is the queries fail because the bson packages overlap, but never match an ObjectId

golint throws lots of suggestions on the util/ directory and subdirectories that we need to address.

Some databases support replication features. upper.io/db should distinguish between write operations (INSERT, UPDATE, DELETE) and direct them to master, read-only operations (SELECT) and direct them to slave[1], slave[2], ..., slave[n].

Hi Xiam,

I would really like some support for (MySQL) joins. And I guess to would be nice for Postgres to.

For example:

var Order struct {
Id int64 db:"id"
CustomerId int64 db:"customer_id"
Date time.Time db:"date"
}

var Customer struct {
Id int64 db:"id"
Name stringdb:"name"
}

var Result struct {
Order Order db:",inline"
Customer Customer db:",inline"
}

// Join type proposal ?
type Join struct {
Join interface{} // db.Raw{LEFT JOIN}
Table interface{} // db.Raw{customers}
Alias interface{} // db.Raw{Customer}
Key interface{} // db.Raw{Customer.Id}
ForeignKey interface{} // db.Raw{Order.customer_id}
}

myJoin := Join{
db.Raw{LEFT JOIN},
db.Raw{'customers'},
db.Raw{'Customer'},
db.Raw{'Customer.id'},
db.Raw{'Order.customer_id'},
}
col, err := sess.Collection(orders AS Order)
res := col.Find(db.Cond{"Order.id":1}).Join(myJoin).Select('Order.id, Customer.name')

var result []Result
err = res.All(&result)
....

The query output should then be something like this:
SELECT Order.id , Customer.id, Customer.name
FROM orders AS Order
LEFT JOIN customers AS Customer ON customers.id = Order.customer_id
WHERE (Order.id = ?)

I suspect that the .All() and .One needs to be modified to, a possible solution might be the using the alias instead of ,inline as the db struct tag.

This way u can easily wrap multiple "structs e.a Order,Customer" into a single result, which in my case can I can marshall to JSON and use it for an API.

But then, if you have a better solution then I probably missed so please let me know the better approach.

Best regards,

Jeroen

Current:

    if err = self.collection.Insert(bson.M{"_id": id}); err != nil {
            return nil, err
    }

    // Now append data the user wants to append.
    if err = self.collection.Update(bson.M{"_id": id}, item); err != nil {
            return nil, err
    }

Should be:

    if _, err = self.collection.Upsert(bson.M{"_id": id}, item); err != nil {
            return nil, err
    }

ErrorCollectionDoesNotExists should be ErrCollectionDoesNotExist.

Exist is a verb not an adjective.

around db/mongo/collection.go, line 90:

if len(chunks) > 1 {
            switch chunks[1] {
            case `>`:
                op = `$gt`
            case `<`:
                op = `$gt`

https://github.com/upper/db/blob/master/mongo/collection.go#L90

Some databases support rolling back a transaction.

Upper is not working with MySQL. Here is the sample code I have used. Go lang version I used is 1.2.1 and I have tried in both Windows 7 64 bit and Ubuntu 13.10 64bit. Upper-mysql is working in neither of them. And here is the MySQL schema I used. Am I doing something wrong here or is it a standing issue?

I recently tried to insert a row into a PostgreSQL table like this:

CREATE TABLE hello_table (
  Code varchar(255) default '',
  UserId varchar(255) default '',
  SomeVal varchar(255) default '',
  primary key (Code, UserId)
);

My code for the insert operation looks like this:

_, err := collection.Append(FooBarItem{
  Code: "foobar",
  UserId: "some user",
  SomeVal: "some more thing",
});

I get this error:

sql: Scan error on column index 0: converting string "foobar" to a int64

I've checked the database, the item was created successfully. Neither my struct nor the database table uses integer variable. I then slightly changed my table structure:

CREATE TABLE hello_table (
  Id serial,
  Code varchar(255) default '' unique,
  UserId varchar(255) default '',
  SomeVal varchar(255) default '',
  primary key (id)
);

The same code works again.

That means either the primary key or the first column is expected to be numerical (i.e. int64). That might not be the case for all tables.

Columns with uppercase letters in the name are not being mapped correctly.

• https://github.com/upper/db/blob/master/db.go#L244

maybe this is better

var ErrUnknownWrapper          = errors.New(`Unknown wrapper.`)

db.Open such as

if ok == false {
    return nil, ErrUnknownWrapper
}

Trying to look up godoc documentation for the util/sqlutil cannot be found via the godoc for upper.io/db link that the webpage has.
(For some reason the util package is not listed.)

Whereas godoc for github.com/upper/db does indeed show the util package.

hi @xiam
I do go test for sqlit got this

database_test.go:587: Struct is different

because testValues.Date is

time.Date(2012, 7, 28, 1, 2, 3, 0, time.Local), // time.Local

SQLite3/MySQL/PostgreSQL/MongoDB do not store the timezone.
so, upper/db use time.UTC will be better.
such as func toInternal

    case time.Time:
-       return t.Format(DateFormat)
+       return t.UTC().Format(DateFormat)

or all Datetime use time.UTC.

@xiam First off, I'm really liking the simplicity of this package. Thanks!
So, to the problem:

When I open a database connection, I have to call db.Database.Use because it always panics with a Collection does not exists..
This is because the check for table existence is happening before the right database is chosen. Calling the postgresql.Table.Exists method before this will always look in the information schema of the default postgres user's database and not the database set in the code settings. And this is where the right information schema lives.

This will help closing #43, other developers can define their customized connection URLs.

I have a PostgreSQL table with a non-standard primary key name. Currently it's not possible to set a custom primary key.

I think it's possible to detect automatically. Here's a query that retrieves the primary key:
https://wiki.postgresql.org/wiki/Retrieve_primary_key_columns

I tried to add it but I don't understand the new sqlgen stuff.

omitempty flag should be stored in the field value (most preferably seperated from the actual name by a comma) in order to not pollute the tag namespace and work the same way as stdlib does.

How do i mention query's offset?

It would be nice to support transaction rollbacks since transactions are already supported. Is there any reason why Database doesn't have a Rollback() method?

hello, from http://docs.mongodb.org/manual/reference/method/db.collection.insert/#create-collection i am under the impression that if i were to try to Collection.Append (corresponding to db.collection.insert) to a not existing collection, the collection would be created if it did not exist. however it seems that upper.io/db doesn't have a way to create collections and doesn't insert to a mongo collection if it doesn't exist. maybe i missed something, but how do i make new collections?

Most of the time you end up marshalling the Go objects into JSON for client side, and almost always the JSON key is exactly the same as the database field, which means your structs end up like this:

type User struct {
    ID            string `json:"-"           db:"id"`
    Email         string `json:"email"       db:"email"    `
    Password      string `json:"password"    db:"password" `
    AccessLevel   string `json:"accessLevel" db:"accessLevel"`
    Customerno    string `json:"customerNo"  db:"customerNo"`
    Name          string `json:"name"        db:"name"`
    authenticated bool   `json:"-"           db:"-"`
}

Now, if upper.io/db respected json tag when there is no db tag, the very same code could look a lot cleaner:

type User struct {
    ID            string `json:"-"           db:"id"`
    Email         string `json:"email" `
    Password      string `json:"password"  `
    AccessLevel   string `json:"accessLevel"`
    Customerno    string `json:"customerNo"`
    Name          string `json:"name"`
    authenticated bool   `json:"-"`
}

if the concept is acceptable within the design principles of upper.io/db I am happy to create a pull request.

When one needs SQL, which is not supported by upper/db, one uses sess.Driver() directly.

Consider the following:

1. I would like to use upper/db for CRUD, because it is tedious to write by hand.
2. I would also like to use "sql-helper-library", a third-party library which provides me with functionality that I need.
3. Operations performed by both of the libraries need to run in a transaction.

Now there is a problem.

Both of the two libraries should use one and the same transaction.
Since the sql-helper-library uses plain database/sql, it can use sql.Tx if given.

upper/db's type Tx does not expose the underlying sql.Tx, nor can it use an externally-started sql.Tx.

Either of those options would allow upper/db to inter-operate with other golang database libraries.

I deeply request this feature: to expose and set sql.Tx in upper/db's type Database

https://github.com/upper/db/blob/master/mongo/collection.go#L215-L218

when occurred E11000 duplicate key in mongo.Append() method, has remaining a record just before created.
so, I think it should removed when error

e.g. remove record as fllows

        if err = self.collection.Update(bson.M{"_id": id}, item); err != nil {
                _ = self.collection.RemoveId(id)
                return nil, err
       }

Description

When you use Collection.Append() method

sess.Collection("Object") 

PostgreSQL adapter show the next error

pq: relation "object" does not exist

You can view an example here and the documentation can find it here

For example, to pick one random element from a MySQL database:

col.Find().Sort(db.Raw{`RAND()`}).Limit(1)