What is the best way to create a test for an API that queries a postgres database?

<p>I was told that I should try to do an integration test and I am currently trying to figure out how to create a mock database to ensure that my endpoint will return the desires json.</p> <p>I was attempting to use <a href="https://github.com/DATA-DOG/go-sqlmock" rel="nofollow">https://github.com/DATA-DOG/go-sqlmock</a> but I&#39;m not sure that it can handle my query as I think it maybe be a bit complex. The query involves the joining of two tables and building a jsonb from an existing jsonb within the postgres database.</p> <p>I&#39;m currently trying to figure out how to mimic the data within a postgres database though.</p> <p>What would you think is the best approach. If you need anything else I can post more information.</p> <hr/>**评论：**<br/><br/>shovelpost: <pre><blockquote> <p>an integration test and I am currently trying to figure out how to create a mock database</p> </blockquote> <p>If you mock your database then it&#39;s technically no longer an integration test. What you should do is write tests that test against the actual database. There are many ways to do that. This <a href="https://www.philosophicalhacker.com/post/integration-tests-in-go/" rel="nofollow">article</a> describes 3 ways.</p> <p>The article does extensive use of <code>TestMain</code> but it&#39;s not necessarily needed. You can have your <code>setup</code> and <code>teardown</code> helper functions inside each integration test function.</p> <p>There is also another way that involves build tags. So you guard your integration tests with say <code>// +build db</code> then in order to make them run you do <code>go test -tags=db</code>.</p> <p>Each way has their own pros and cons so pick the one that best suits you.</p></pre>hell_0n_wheel: <pre><blockquote> <p>What you should do is write tests that test against the actual database.</p> </blockquote> <p>To be precise, write tests that test against a clone of the database you&#39;ll be deploying to. Tag the tests as suggested in the parent post, then run them on a machine that is also running an installation of postgres.</p> <p>Your integration test setup should create a clone of the database on the local server, populate it with test data, verify your software operates on the data as expected, then tear the cloned database down. Easy peasy!</p> <p>This is what we do for all our database-backed services and each test only takes about 6-8 seconds including setup and teardown. </p></pre>GreatDant0n: <pre><p>RemindMe! 1 day</p> <p>I am interested in this as well</p></pre>Ploobers: <pre><blockquote> <p>I&#39;m not sure that it can handle my query as I think it maybe be a bit complex</p> </blockquote> <p>It can definitely handle your query. It isn&#39;t actually executing anything, and it isn&#39;t checking your syntax for compatibility. It&#39;s just doing a glorified string match on your query and then returning whatever response you&#39;ve specified. That&#39;s not a knock on it, we use it extensively, but it isn&#39;t enough by itself.</p> <p>This is where we&#39;re heading for integration tests with our db, but haven&#39;t had time to actually try it out yet.</p> <p><a href="https://github.com/ory/dockertest" rel="nofollow">https://github.com/ory/dockertest</a></p></pre>