Speed database for local env setup

[mikicho]

@jhenaoz @Thormod @goldbergyoni

I want to focus on MySQL and Postgres, which are the most popular databases engines in those days. We can add more databases later (MongoDB is a good candidate IMO)

Also, Because of WSL2, I think we can recommend using Linux practices for Windows, WDYT?

The challenge:

• In-memory database setup (as much as possible)
• Minimize disk writes.
• Database specific configuration
• Docker for Mac performance issues (if any)

Optional Solutions

• RAM Disk (folder)
• Disable logging
• Disable durability

Postgres:

• disable durability - https://www.postgresql.org/docs/current/non-durability.html
• increase worker count - https://www.postgresql.org/docs/10/how-parallel-query-works.html
• test in small tables
• truncate vs transaction-rollback?
• use tmpfs? https://stackoverflow.com/questions/42226418/how-to-move-postresql-to-ram-disk-in-docker

Tasks

• [ ] Define a benchmark scenario

[jhenaoz]

I suggest running the app and all the commands inside a docker image, that way we don't nedd to worry about any OS specific config

[mikicho]

@jhenaoz Thanks, Indeed this is preferable, Just not sure about the performance drawbacks (if any) on Mac, want to do a small benchmark to check it out. my guess is there is a small, neglectable performance drawback.

[goldbergyoni]

@mikicho Sounds like a super-strategic topic. It can be interesting to benchmark the differences. I also wonder, whether we can map to RAM in Mac/Windows in an automated way, so a developer just pull, type 'npm t' and the magic happens OR a developer would have to manually point his Docker daemon to some specific local folder.

I suggest running the app and all the commands inside a docker image, that way we don't nedd to worry about any OS specific config

@jhenaoz Did you mean the infra (e.g. DB, MQ) or also the code under test?

[mikicho]

@jhenaoz another thing is there are options that available only for Linux, for example tmpfs

@jhenaoz @goldbergyoni @Thormod In component testing, do we run schema migrations at the beginning of every run? just truncate all tables? leave the tables intact?

[jhenaoz]

@mikicho , how about schema migration in some global setup for all the test execution? and truncate tables for data clean up for each test case?

[goldbergyoni]

@jhenaoz @mikicho Interesting stuff, my 2.5 cent:

Schema - Migration on global setup sounds indeed very sensible like Juan wrote 🚀. Maybe in dev env, no need to seed on every run, rather when explicitly asked? This will bump the performance for local dev given that in 99% of the executions we don't need to migrate (no schema changes)

Clean-up - If we clean-up after every test, then processA.test1.afterEach will delete processB.test12 data during execution, isn't it? Maybe clean-up only at the end, globalTeardown, and every test should take care to act on his own records only?

For example:

test('When deleting a user, then his articles are deleted', ()=>{

// Arrange
const userToAdd = {name: `Joe ${uuid()}`};
...
}

[mikicho]

@goldbergyoni @jhenaoz Just saying there is another option which is to open a transaction and rollback it after each. it's efficient and clean. Having said that I agree with you, on dev no need for cleaning after each (just once in a while to keep the tables small) I don't think we need to clean up at all on global teardown.

[goldbergyoni]

@mikicho Yes, good point, transactions are also an option (when working with a supportive DB + single DB + local process) and it's good that we cover everything before making decisions.

Curious to hear @jhenaoz and @Thormod thoughts on this.

[Thormod]

Several comments about this discussion:

• For CNS project we are running the migrations for each test execution (in the global setup and in the global teardown doing the revert).
• Im agree with @jhenaoz we should run the application and DB using docker images, the only thing that I'm worried about this is how to review code coverage if we are only targeting the image. (I mean we create and mount the image, then we run test targeting the application, but jest - for example - is not running directly the application)
• Im agree with @mikicho comment, I think to perform a transaction and then rollback it is a good option.

[goldbergyoni]

While I believe using transactions is a valid option, it's not my superior one because:

1. Works only with DB that supports transaction. Also, if there is a cache layer or a 2nd DB there might be partial data as they don't participate in the transaction
2. Gets hairy when the code already has a transaction, there you get transaction inside a transaction OMG
3. Won't work for remote tests
4. Performance penalty and locking
5. White-box approach - Instead of focusing on the API, the tester might dig into the DAL
6. Can't see data in DB for troubleshooting purposes

My preferred option is like (always) like the production is working - Generate unique rows for every test and don't delete those. It isn't perfect but maybe better.

[mikicho]

@goldbergyoni @jhenaoz shouldn't we close this in favor of #6 ? (maybe the opposite to preserve the conversation above)

[goldbergyoni]

@mikicho Yes