What are the options to fake SQL database for testing?

[vkoch...@gmail.com]

Back in 2014 Andrew Gerrand made nice talk “Testing Techniques” which advocates using httptest fake http server implementation for your code testing. In scope of business logic application the major dependency is SQL database. I found testing part of go code that access data in database as problematic. The Java word enjoys a very powerful H2 database that can:

• create database snapshot to the file
  
• load the snapshot into in memory database instance
  
• can simulate different dialects of sql 
  

Is there any similar soft in go world for “database/sql/driver” capable of running as embedded database engine? 

P.S. I don’t see  embedded versions of PostgreSQL or MySql. The SQLite is not quite reach SQL implementation from enterprise point of view.

How do you test your data access in go code?

[Shawn Milochik]

If you use an interface instead of writing your code to speak SQL, you can create a dummy version that stores records in a map or something.

This is demonstrated by @francesc in the first half of this demo:

https://www.youtube.com/watch?v=wB1hoUJDbk0

For example, you create an interface with Save, Search, Delete methods, and so on. Then you create a type that implements this against your database backend, and another that you use for testing.

[Eric Johnson]

Sqlite does support in-memory only database. I've used that successfully for testing purposes....

Depends on your product's intended use. With databases, actually testing against different databases is going to be pretty critical if you intend to support running in production against multiple database engines. They all have their quirks, and you need to test for those. Perhaps adopt a pattern where your test cases look for an environment variable (or more than one), that defines how to connect to a database. Have your tests pre-populate your database, then run the tests you want to run.

Eric.

[Hǎiliàng]

1. abstract database layer by interface (unit test)
2. Docker (either unit test or integration test)
3. a combination of 1 and 2

Hǎiliàng

[Matt Silverlock]

https://robots.thoughtbot.com/interface-with-your-database-in-go is a good article that references what +Shawn Milochik mentioned.

• Set up interfaces that other packages expect/deal with for DB operations
• Have your postgres|mysql|whatever package satisfy that interface (effectively a driver for that interface)
• In the packages that import your datastore interface, stub/mock the interface for testing
• Keep the integration tests associated with the specific datastore implementation

[Chris Hines]

https://github.com/DATA-DOG/go-sqlmock can help.

[Kyle Wolfe]

On Thursday, October 8, 2015 at 3:54:08 PM UTC-4, vkoch...@gmail.com wrote:

P.S. I don’t see  embedded versions of PostgreSQL or MySql. The SQLite is not quite reach SQL implementation from enterprise point of view.

Maybe have a look at tidb? (https://github.com/pingcap/tidb/blob/master/docs/USAGE.md)

[Amit Saha]

Hi all,

I am just looking at options to write tests for parts of my application that interacts with a SQL database. So far it seems like the community has few distinct schools of thought:

- Mocks (For e.g. using https://pkg.go.dev/github.com/DATA-DOG/go-sqlmock)

- In-memory "real" DB solutions - such as tidb-lite for MySQL (https://github.com/WangXiangUSTC/tidb-lite)

- Container based functional/integration style testing

It will be great to hear if anybody has some other experiences to share.

Thanks,

Amit.

[Levieux Michel]

Hi,

IMO, specific mocks like DATA-DOG's tend to be complicated to use and have behaviors that should not appear in a mock, which would ideally tend to have no logic at all.

There are several mock packages that you can find here [and BTW if you have time to give feedback I'm currently working on my own, anyway that is completely unrelated].

It really depends on what you are looking for in terms of features. Do you need something that is able to error on syntax problems? That keeps track of the execution flow (i.e. if you insert data in your fake database, are you expecting it to be able to return it to you? Under what conditions?)?

Mocking complex systems like SQL databases is quite hard... What I have seen many times is just to have a "seed-based" local database that is used in local integration tests.

Another, quite efficient solution is to have a test suite's database initialized once and used for all subsequent unit tests. Once you have good unit tests for your database-interacting low-level functions, and integration tests for the whole flow, you can just mock the db behavior in other higher level unit tests because you know it'll work in production.

Hope this helps!

--
You received this message because you are subscribed to the Google Groups "golang-nuts" group.
To unsubscribe from this group and stop receiving emails from it, send an email to golang-nuts...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/golang-nuts/4fbc3ae9-b0ff-4750-bfba-1d58a1dba986n%40googlegroups.com.

[Markus Zimmermann]

We switched from SQLite to PostgreSQL as our "in memory" database. We use an SQL database so we do not have to mock DB calls and maintain an interface. If that is something that is interesting i could trigger a blog post about what we did and why?

[Henry]

We go-generate the mapping between the data models and the database. It was tedious to write such tool, but it was a time well spent. Now we just work with data models and not worry about testing the SQL instructions. Occasionally, we use SQLite in-memory database to double-check certain functionality work as intended.

We also add code that allow the application to run tests against the actual database during deployment. 

[Levieux Michel]

I'm not quite sure the intent is the same? Generating models only reduces the cognitive and time overhead of going back and forth from the code to the db. It does not test anything. Having a model that you are sure is a reflection of the db does not help you know that the logic going with it works? Maybe there is something I don't understand.

BTW I'm interested to know if you tool is open-source? Thx!

To view this discussion on the web visit https://groups.google.com/d/msgid/golang-nuts/ec72823c-d528-48a6-9d4c-704f0862fcb6n%40googlegroups.com.

[Henry]

Are we talking about testing the domain logic or the data persistence? .

If it is about testing data persistence, rephrasing my earlier response, we do not test data persistence, not very thoroughly anyway, because it is auto-generated by a tool. We did however thoroughly tested the tool (and the code it generates) against actual common SQL databases. It is an upfront investment, so that we do not have to do similar tests for the subsequent projects.

The tool is not open-source, but there are plenty of ORM and ORM-like open source Go libraries around. You may consider writing your own. I think in the long run, it is a worthy investment. When I first started it, I just wanted to save time from having to write repetitive mapper for each data type. Our data access code were quite fragile to changes. The manually typed SQL commands were also error prone, and not everybody in the team is familiar with various SQL dialects. So I wrote the tool to auto generate the necessary code. The tool was then used by my colleagues, and over time they added their own enhancement. Now every time there is a change, I just update the data models, run the tool, and done.

We do a test run against a copy of the actual database before deployment, and the final test run against the actual database during deployment.

[Amit Saha]

On Tue, Jul 27, 2021 at 6:20 PM Levieux Michel <mlevi...@gmail.com> wrote:
>
> Hi,
>
> IMO, specific mocks like DATA-DOG's tend to be complicated to use and have behaviors that should not appear in a mock, which would ideally tend to have no logic at all.
> There are several mock packages that you can find here [and BTW if you have time to give feedback I'm currently working on my own, anyway that is completely unrelated].
>
> It really depends on what you are looking for in terms of features. Do you need something that is able to error on syntax problems? That keeps track of the execution flow (i.e. if you insert data in your fake database, are you expecting it to be able to return it to you? Under what conditions?)?
>
> Mocking complex systems like SQL databases is quite hard... What I have seen many times is just to have a "seed-based" local database that is used in local integration tests.
> Another, quite efficient solution is to have a test suite's database initialized once and used for all subsequent unit tests. Once you have good unit tests for your database-interacting low-level functions, and integration tests for the whole flow, you can just mock the db behavior in other higher level unit tests because you know it'll work in production.
>
> Hope this helps!

Thank you for sharing your thoughts - super helpful. I especially like
your summarized version of what i think of as testing it at various
layers - if you test the code which is close to the DB well, you can
then take advantage of simple mocks at higher levels.

[Amit Saha]

On Tue, Jul 27, 2021 at 7:19 PM Markus Zimmermann <zim...@gmail.com> wrote:
>
> We switched from SQLite to PostgreSQL as our "in memory" database. We use an SQL database so we do not have to mock DB calls and maintain an interface. If that is something that is interesting i could trigger a blog post about what we did and why?

That will be great to hear. I am assuming your tests do not use any
PostgreSQL specific functionality? This thread looks interesting as
well [1]

[1] https://news.ycombinator.com/item?id=10002142

> To view this discussion on the web visit https://groups.google.com/d/msgid/golang-nuts/5829cbca-703d-42c4-9338-5f739375568an%40googlegroups.com.

[Amit Saha]

On Wed, Jul 28, 2021 at 12:43 AM Henry <henry.ad...@gmail.com> wrote:
>
> Are we talking about testing the domain logic or the data persistence? .
>
> If it is about testing data persistence, rephrasing my earlier response, we do not test data persistence, not very thoroughly anyway, because it is auto-generated by a tool. We did however thoroughly tested the tool (and the code it generates) against actual common SQL databases. It is an upfront investment, so that we do not have to do similar tests for the subsequent projects.
>
> The tool is not open-source, but there are plenty of ORM and ORM-like open source Go libraries around. You may consider writing your own. I think in the long run, it is a worthy investment. When I first started it, I just wanted to save time from having to write repetitive mapper for each data type. Our data access code were quite fragile to changes. The manually typed SQL commands were also error prone, and not everybody in the team is familiar with various SQL dialects. So I wrote the tool to auto generate the necessary code. The tool was then used by my colleagues, and over time they added their own enhancement. Now every time there is a change, I just update the data models, run the tool, and done.
>
> We do a test run against a copy of the actual database before deployment, and the final test run against the actual database during deployment.

Thanks for sharing your experiences and approach to testing. I once
again read this is as taking a layered approach to testing.

I am curious about this point:

>We did however thoroughly tested the tool (and the code it generates) against actual common SQL databases.

That sounds interesting - is the tool generating or is able to
generate SQL for different databases? That must have been a pretty big
effort to create such an abstraction.

> To view this discussion on the web visit https://groups.google.com/d/msgid/golang-nuts/df878e11-3a70-45d1-9cc3-7a73d864ee9an%40googlegroups.com.

[Henry]

On Wednesday, July 28, 2021 at 3:05:43 PM UTC+7 amits...@gmail.com wrote:

That sounds interesting - is the tool generating or is able to
generate SQL for different databases? That must have been a pretty big
effort to create such an abstraction.

It produces different SQL for different databases. It supports a limited number of databases. Note that quite a number of Go ORM libraries already support multiple databases. So it is not new. The difference is that other ORM libraries usually provide a general purpose data access library, whereas ours generates more specific codes. Other than that, they serve a similar purpose.

[Marcin Romaszewicz]

I have this exact testing issue at my company, we have many Go services which use Postgres in production, but are unit tested against SQLite.

The latest SQLite covers the vast majority of Postgres queries, so most tests simply use an SQLite in-memory DB.

For the tests which require Postgres- specific functionality, such as partitioned tables, for example, we use https://github.com/testcontainers/testcontainers-go. This is a library which talks to Docker and can create your test prerequisites as docker containers and gives you connection information once they're up. This makes unit tests incredibly slower, but at least functional.

The danger with mocking too much is that your unit tests end up testing the mocks, and not anything remotely like your runtime environment, so we've chosen to mock as little as possible.

-- Marcin

--

You received this message because you are subscribed to the Google Groups "golang-nuts" group.
To unsubscribe from this group and stop receiving emails from it, send an email to golang-nuts...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/golang-nuts/9c81746a-4fb4-4fb5-8e5f-605169a3f2afn%40googlegroups.com.

[Amit Saha]

On Thu, Jul 29, 2021 at 4:39 AM Marcin Romaszewicz <mar...@gmail.com> wrote:
>
> I have this exact testing issue at my company, we have many Go services which use Postgres in production, but are unit tested against SQLite.
>
> The latest SQLite covers the vast majority of Postgres queries, so most tests simply use an SQLite in-memory DB.
>
> For the tests which require Postgres- specific functionality, such as partitioned tables, for example, we use https://github.com/testcontainers/testcontainers-go. This is a library which talks to Docker and can create your test prerequisites as docker containers and gives you connection information once they're up. This makes unit tests incredibly slower, but at least functional.
>
> The danger with mocking too much is that your unit tests end up testing the mocks, and not anything remotely like your runtime environment, so we've chosen to mock as little as possible.

Thank you for sharing about testcontainers-go. I have come across
testcontainers, it sounds like a viable solution as well. As with a
lot of things in software, it seems like we just have to see what
works for "us".

>
> -- Marcin
>
> On Wed, Jul 28, 2021 at 4:09 AM Henry <henry.ad...@gmail.com> wrote:
>>
>> On Wednesday, July 28, 2021 at 3:05:43 PM UTC+7 amits...@gmail.com wrote:
>>>
>>> That sounds interesting - is the tool generating or is able to
>>> generate SQL for different databases? That must have been a pretty big
>>> effort to create such an abstraction.
>>>
>>
>> It produces different SQL for different databases. It supports a limited number of databases. Note that quite a number of Go ORM libraries already support multiple databases. So it is not new. The difference is that other ORM libraries usually provide a general purpose data access library, whereas ours generates more specific codes. Other than that, they serve a similar purpose.
>>
>> --
>> You received this message because you are subscribed to the Google Groups "golang-nuts" group.
>> To unsubscribe from this group and stop receiving emails from it, send an email to golang-nuts...@googlegroups.com.
>> To view this discussion on the web visit https://groups.google.com/d/msgid/golang-nuts/9c81746a-4fb4-4fb5-8e5f-605169a3f2afn%40googlegroups.com.
>
> --
> You received this message because you are subscribed to the Google Groups "golang-nuts" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to golang-nuts...@googlegroups.com.

> To view this discussion on the web visit https://groups.google.com/d/msgid/golang-nuts/CA%2Bv29Lv83-4yDijNmukf0Vx%2BoBVZXJPR11bqA_B5CY1mNhOowA%40mail.gmail.com.

[Brian Candler]

You might also want to look at podman, which runs containers directly as processes under your own userid with no docker daemon - and buildah, the corresponding tool for creating container images.  I use them quite a lot for building and running container images before deploying them to kubernetes.

I haven't used the go bindings, but apparently they do exist:

https://podman.io/blogs/2020/08/10/podman-go-bindings.html

According to that, they are running podman as a service with a REST API, which isn't a million miles from a docker daemon - except that the podman service runs as your own userid, not as root.

[Andrew Werner]

Another choice to throw into the mix is https://github.com/cockroachdb/copyist. It comes with different trade offs but if you buy into its framework, it should be much faster than running the database. 

To view this discussion on the web visit https://groups.google.com/d/msgid/golang-nuts/4d6f1b9d-ad44-4fc5-b309-beba2a199382n%40googlegroups.com.

[Robert Engels]

I don’t like the “solves the problem of your test running fine in isolation but fails when run in concert with other tests”. 

If you have that problem you have poorly written tests. 

On Jul 29, 2021, at 2:05 PM, Andrew Werner <awer...@gmail.com> wrote:



To view this discussion on the web visit https://groups.google.com/d/msgid/golang-nuts/CA%2BvRuzPvz%2Bu2NCubgnWEiu-D29HVj13tGxUHmEVpaTTdcqyO0Q%40mail.gmail.com.

[Levieux Michel]

Also, though I think it's easy to slip into tests that test the mocks if you don't pay attention, you can have arbitrarily numerous mocks without having bad tests. Unit tests are not designed to reflect your runtime environment at all, they're here to make sure your function (program unit) does what it's supposed to do under different conditions.

If your function accesses a DB for data, a Redis instance for (i don't know) cached identifiers, and an http API for other data, then you can mock the three of them (db, redis, and http API) and still have good unit tests, you just need to make sure you don't test that the mocks do what mocks do, but rather that your function responds well to different contexts.

To view this discussion on the web visit https://groups.google.com/d/msgid/golang-nuts/CA%2Bv29Lv83-4yDijNmukf0Vx%2BoBVZXJPR11bqA_B5CY1mNhOowA%40mail.gmail.com.

[Amit Saha]

On Thu, Jul 29, 2021 at 5:13 PM Brian Candler <b.ca...@pobox.com> wrote:
>
> You might also want to look at podman, which runs containers directly as processes under your own userid with no docker daemon - and buildah, the corresponding tool for creating container images. I use them quite a lot for building and running container images before deploying them to kubernetes.
>
> I haven't used the go bindings, but apparently they do exist:
> https://podman.io/blogs/2020/08/10/podman-go-bindings.html

Thanks - I will check those out. I am looking for a solution which
will work across operating systems - Linux, Mac OS and Windows. Hence,
I suspect using the docker desktop might be a better solution for me.
I am leaning towards using tests containers go.

> To view this discussion on the web visit https://groups.google.com/d/msgid/golang-nuts/4d6f1b9d-ad44-4fc5-b309-beba2a199382n%40googlegroups.com.