<p>So for example I have this function:</p>
<pre><code>func (i *Item) Store(db *sql.DB) (id string, password string, err error) {
err = db.QueryRow(
"INSERT INTO items (name, description, price) VALUES($1, $2, $3) RETURNING id, password",
i.Name, i.Description, i.Price,
).Scan(&id, &password)
return
}
</code></pre>
<p>That stores an Item to a SQL database. I thought about just spinning up a SQL database for testing; but it seems like there would be a better Go solution, am I right in assuming that? Is there a "mock" DB that I can pass to the function, or define an interface for sql DB's and have the function take those, and create my own mock DB?</p>
<p>Thanks,</p>
<p>Franklin.</p>
<hr/>**评论：**<br/><br/>gopher-octa24: <pre><p>I separate the <code>Item</code> from the underlying storage by using a <code>Storage</code> interface.
So in your example you will have:</p>
<pre><code> type ItemStore interface {
Insert(*Item) (id string, password, err error)
}
</code></pre>
<p>As you can tell, implementing a <code>MemoryItemStore</code> is going to be super simple so you can test other parts without worrying about the database and sql part.</p>
<p>Then for the <code>SqlItemStore</code> implementation I just start a real database in a docker container.</p>
<p>Hope that helps!</p></pre>Franke123: <pre><p>Brilliantly simple! Part of what I want to do is test the actual SQL statements themselves, as well as the server (some of the UUID generation and stuff is handled by SQL) but this is a good idea for just a mock "db". Thanks!</p></pre>gopher-octa24: <pre><p>Happy to help :)</p>
<p>I also use a library[1] I wrote to start and stop/delete containers from tests.</p>
<ol>
<li><a href="https://github.com/omeid/conex" rel="nofollow">https://github.com/omeid/conex</a></li>
</ol></pre>Franke123: <pre><p>Starred :)</p></pre>Franke123: <pre><p>This works great! I implemented it. While it still doesn't test my SQL, it's handy for quick and dirty testing.</p></pre>gopher-octa24: <pre><p>Fantastic!</p></pre>lumost: <pre><p><a href="https://github.com/DATA-DOG/go-sqlmock" rel="nofollow">https://github.com/DATA-DOG/go-sqlmock</a> may help you out</p></pre>Franke123: <pre><p>Is that really the best solution? No offense to the writer, but I was looking for a cleaner solution. Edit: Also I'm not sure how the RETURNING would work with go-sqlmock.</p></pre>ryeguy: <pre><p>Are you trying to test the actual sql statement itself? If so, only an instance of the real database will work. There are things you can do to speed up local databases if you go that route.</p>
<p>If you want to just mock the sql layer, pull your sql into its own struct (like <code>ItemRepository</code> or <code>ItemDao</code>) and now you have a single point of entry for your queries. Your <code>Item</code> struct ideally shouldn't contain queries anyway, it's kind of clunky to do it that way.</p></pre>Franke123: <pre><p>Yes I am trying to test that the data can be properly CRUD from the database.</p>
<p>So I should have my own SQL class for items, but then how does that solve the problem? Wouldn't it still be storing to a database?</p></pre>ryeguy: <pre><p>Forget about the design pattern stuff for now, it's a separate thing from your problem.</p>
<p>If you want to actually test sql validity and behavior, there isn't really a way around this in any language but to hit an instance of that database. There are some proposed solutions but they never work as well.</p>
<p>Some people use generic orm tools and then use something like sqlite to test against. Sqlite doesn't behave the same way in some situations and doesn't implement as many features as full blown rdbms.</p>
<p>Some build mock versions of databases, but this only works if the database is already pretty simple (for example, redis is doable but still a lot of work - i think someone did this).</p>
<p>The real solution is to just run a local instance of your database. It looks like you're using postgres. <a href="http://stackoverflow.com/questions/9407442/optimise-postgresql-for-fast-testing" rel="nofollow">There are many tweaks you can make for fast testing</a>.</p></pre>softwaregav: <pre><p>Another way to put this:</p>
<p>There are two types of tests being referenced here: unit tests and integration tests. </p>
<p>If you want to test that some service does what it should with the data from the database, that's a unit test. It can be done without any external dependencies.</p>
<p>If you want to ensure that you can do CRUD operations with your database, that's an integration test. It requires you to actually connect to an instance of a database.</p>
<p>Unit tests are definitely the most common type of test. Integration tests can sometimes be more work than they are worth. Sometimes you will need to have a test instance of your database with test data that you must maintain, and it's just not worth it. If you plan on testing code as simple as the example you've given, it doesn't really seem worth it to write integration tests, but that decision is ultimately left for you to make.</p></pre>Franke123: <pre><p>Yep, Postgres. Thanks a lot for the help! It's a shame there isn't a simpler way. I'll look into that for fast testing.</p></pre>lumost: <pre><p>If you're looking for an end to end test for getting data in and out of the DB your best bet will be to spin up a database in your flavor of choice for your test. Do you have an example from a different language of the type of tooling you're looking for?</p></pre>Franke123: <pre><p>Well I was hoping that I could define test cases and a fake db, but I think I'm just going to go with what <a href="/u/ryeguy" rel="nofollow">/u/ryeguy</a> recommended and run a postgres database with optimizations for testing.</p></pre>danredux: <pre><p>My personal philosophy is not to test external services.</p>
<p>If you import a library, you wouldn't test that library, you would test your use of it.</p>
<p>So, you're not testing the raw SQL does what you expect... That should be tested in the DB itself. What you're testing is a: your code executes that query when you expect it to, and b: your code correctly handles the response of that query, ie, scanning the results into the struct appropriately.</p>
<p>Therefor, if you use something like "sqlmock" you get to test both of these things.. You set up an expected query (even just checking for "SELECT *", meaning a select of any kind), then putting in the results and making sure your code scans them in correctly. At no point do you need to make the roundtrip to a DB, because you're not testing that your DB driver works, nor are you testing that Postgres knows how to execute SQL.</p></pre>Franke123: <pre><p>I like that philosophy.</p>
<p>Yes I think that SQLMock would work, but I was looking for something a little more advanced and simple (I know, beggars can't be choosers). For now a optimized testing database in postgres should work, but I may switch to SQLMock in the future.</p></pre>PsyWolf: <pre><p>Specifically, what usage/feature(s) were you looking for that sqlmock doesn't support?</p></pre>Franke123: <pre><p>There may be a way to do it that I just don't see, but so like for this: </p>
<pre><code>/ Store ... stores item into SQL database, returning id,password and/or error
func (sis *SQLItemStore) Store(i *model.Item) (id string, password string, err error) {
err = sis.DB.QueryRow(
"INSERT INTO items (name, description, price) VALUES($1, $2, $3) RETURNING id, password",
*i.Name, *i.Description, *i.Price,
).Scan(&id, &password)
return
}
</code></pre>
<p>I'd like to return a valid UUID for id and password, and a clean way of doing it ideally.</p></pre>PsyWolf: <pre><p>ok, I threw this together and it seems to do the trick. Cleanliness is a matter of preference, so I don't know how to help with that, but functionally, it's all there.</p>
<p>main.go</p>
<pre><code>package main
import (
"database/sql"
"fmt"
)
func main() {
fmt.Println("foo")
}
type ItemModel struct {
Name string
Description string
Price int
}
type SQLItemStore struct {
DB *sql.DB
}
func (sis *SQLItemStore) Store(i *ItemModel) (id string, password string, err error) {
err = sis.DB.QueryRow(
"INSERT INTO items (name, description, price) VALUES($1, $2, $3) RETURNING id, password",
i.Name, i.Description, i.Price,
).Scan(&id, &password)
return
}
</code></pre>
<p>main_test.go</p>
<pre><code>package main
import (
"testing"
sqlmock "github.com/DATA-DOG/go-sqlmock"
"github.com/stretchr/testify/assert"
)
// a successful case
func TestStore(t *testing.T) {
db, mock, err := sqlmock.New()
if err != nil {
t.Fatalf("an error '%s' was not expected when opening a stub database connection", err)
}
defer db.Close()
// input
i := &ItemModel{
Name: "foo",
Description: "bar",
Price: 42,
}
// expected output
expectedID := "ebe875eb-1313-48b4-9f77-fa808f081da0"
expectedPass := "super secret password"
// define the rows you want the mock to return
rows := sqlmock.NewRows([]string{"id", "password"}).
AddRow(expectedID, expectedPass)
// tell the mock what to expect and what to return
mock.ExpectQuery("INSERT INTO items").WithArgs(i.Name, i.Description, i.Price).WillReturnRows(rows)
// now we execute our method
itemStore := &SQLItemStore{DB: db}
outputID, outputPass, err := itemStore.Store(i)
if err != nil {
t.Errorf("error was not expected: %s", err)
}
// we make sure that all expectations were met
if err := mock.ExpectationsWereMet(); err != nil {
t.Errorf("there were unfulfilled expections: %s", err)
}
assert.Equal(t, expectedID, outputID)
assert.Equal(t, expectedPass, outputPass)
}
</code></pre></pre>Franke123: <pre><p>Wow that is pretty clean! Thanks for writing it out, this is just what I wanted!</p></pre>PsyWolf: <pre><p>A good example is worth a thousand words :-)</p></pre>Franke123: <pre><p>Truth!</p></pre>1Gijs: <pre><p>I use standardized sql and therefore its easy to switch between servers by switching drivers. For live I use a remote mysql server but all tests run local on sqlite. For each test the db is reset and then filled with the data needed for the test. Super fast and no mocking needed.</p></pre>
