Web Server Database Abstraction Advice

<p>I&#39;m trying to organize the code base. The structure I had in my mind is like:</p> <pre><code>project/ ... database/ database.go model/ models.go ... ... </code></pre> <p>database.go</p> <pre><code>import ( &#34;database/sql&#34; &#34;github.com/lib/pq&#34; ) type Database struct { database *sql.DB } var db *Database func connectDB() { ... dbObject, err := sql.Open(&#34;postgres&#34;, dbinfo) ... db = &amp;Database{database: dbObject} } func GetDatabase() *Database { return db } </code></pre> <p>models.go</p> <pre><code>import &#34;project/database&#34; func getUser(db database.Database, ...) (user User, err error) { query := &#34;SELECT * FROM ...&#34; err = db.QueryRow(query, ...).Scan(...) if err != nil { return user, err } return user, err } </code></pre> <p>routes.go</p> <pre><code>func getUser(c *gin.Context) { db := c.MustGet(DB).(Database) // Gets from middleware calling GetDatabase() ... user, err := getUser(db, id) ... } </code></pre> <p>The problem here is in models.go it is not possible to <code>db.Query</code> since <code>Database</code> doesn&#39;t implement that function. Instead of <code>Database</code> I can create a <code>*sql.DB</code> object but then I can&#39;t abstract the <code>sql.DB</code> inside models.go. It will be like this <code>getUser(db *sql.DB, ...)</code>. I checked some opensource codes but couldn&#39;t find a good structure to abstract the database operations which uses SQL. Any advices? </p> <p>You can also give advices/examples which have completely different structures than the structure I described which does the abstraction</p> <p>PS: I don&#39;t use any ORM</p> <hr/>**评论：**<br/><br/>danhardman: <pre><p>My project is fairly similarly structured to this. However, I have defined my models as just entities, so User, Order, Item etc. and I have a separate package for database interaction called datastores.</p> <p>In the datastores package, each model has a datastore (userDatastore, orderDatastore) which has a defined struct that has an *sql.DB field and I add all database functions onto that struct.</p> <p>Here&#39;s an example:</p> <pre><code>//UserDatastore handles database functionality for the user model type UserDatastore struct { BaseDatastore } //NewUserDatastore creates a new user datastore with the provided database and //collection name func NewUserDatastore(db *sql.DB, c string) *UserDatastore { return &amp;UserDatastore{BaseDatastore{db, c}} } //Create adds a new user to the datastore func (d *UserDatastore) Create(u models.User) error { stmt, err := d.DB().Prepare(&#34;&#34;) if err != nil { return err } //rest of the code... } </code></pre></pre>QThellimist: <pre><p>looks prettier than the structure I described. Do you have another abstraction where a you can only access to datastore within models like </p> <pre><code>func (u User) CreateUser() (User, error) { return u.Datastore.Create() } </code></pre> <p>or does the endpoints/routes have direct access to datastore?</p></pre>danhardman: <pre><p>Like the UserDatastore, all others take a *sql.DB as an argument. So as long as you can pass it that, you can use it from anywhere. I&#39;ve taken my DB initialisation out to a separate package for that reason.</p></pre>crowl91: <pre><p>Take a look at <a href="http://www.alexedwards.net/blog/organising-database-access" rel="nofollow">this</a></p></pre>QThellimist: <pre><p>This is awesome thanks. I got a question though. In the last example the author stated passing variables over context in a request scope is not adviced. Why is that? It is a more functional approach also allows a better abstraction between requests as I can see. </p></pre>QThellimist: <pre><p>If I use the <code>Database struct</code> structure these interfaces solve the problem.</p> <pre><code>type DatabaseHandler interface { Prepare(query string) (*sql.Stmt, error) QueryRow(query string, args ...interface{}) *sql.Row Query(query string, args ...interface{}) (*sql.Rows, error) Exec(query string, args ...interface{}) (sql.Result, error) } </code></pre></pre>