Reddit Programming

Dear r/programming (https://www.reddit.com/r/programming) community, I'd like to discuss my solution to a common challenge many teams encounter. These teams work on their projects using PostgreSQL for the database layer. Their tests take too long because they run database migrations many times. If we have many tests each needing a new PostgreSQL database with a complex schema, these ways of running tests tend to be slow: Running migrations before each test (the more complex the schema, the longer it takes) Using transaction rollbacks (this does not work with some things in PostgreSQL) One database shared among all the tests (interference among tests) In one production system I worked on, we had to wait 15-20 minutes for CI to run the test unit tests that required isolated databases. Using A Template Database from PostgreSQL PostgreSQL has a powerful feature for addressing this problem: template databases. Instead of running migrations for each test database, we create a template database with all the migrations once. Create a clone of this template database very fast (29ms on average, regardless of the schema's complexity). Give each test an isolated database. Go implementation with SOLID principles I used the idea above to create pgdbtemplate. This Go library demonstrates how to apply some key engineering concepts. Dependency Injection & Open/Closed Principle // Core library depends on interfaces, not implementations. type ConnectionProvider interface { Connect(ctx context.Context, databaseName string) (DatabaseConnection, error) GetNoRowsSentinel() error } type MigrationRunner interface { RunMigrations(ctx context.Context, conn DatabaseConnection) error } That lets the connection provider implementations pgdbtemplate-pgx and pgdbtemplate-pq be separate from the core library code. It enables the library to work with various database setups. Tested like this: func TestUserRepository(t *testing.T) { // Template setup is done one time in TestMain! testDB, testDBName, err := templateManager.CreateTestDatabase(ctx) defer testDB.Close() defer templateManager.DropTestDatabase(ctx, testDBName) // Each test gets a clone of the isolated database. repo := NewUserRepository(testDB) // Do a test with features of the actual database... } How fast were these tests? Were they faster? In the table below, the new way was more than twice as fast with complex schemas, which had the largest speed savings: (Note that in practice, larger schemas took somewhat less time, making the difference even more favourable): Scenario Was Traditional Was Using a Template How much faster? Simple schema (1 table) ~29ms ~28ms Very little Complex schema (5+ tables) ~43ms ~29ms 50% more speed! 200 test databases ~9.2 sec ~5.8 sec 37% speed increase Memory used Baseline 17% less less resources needed Technical aspects beyond Go The core library is designed to be independent of the driver used. Additionally, it is compatible with various PostgreSQL drivers: pgx and pq Template databases are a PostgreSQL feature, not language-specific. The approach can be implemented in various programming languages, including Python, Java, and C#. The scaling benefits apply to any test suite with database requirements. Has this idea worked in the real world? This has been used with very large setups in the real world. Complex systems were billing and contracting. It has been tested with 100% test coverage. The library has been compared to similar open-source Go projects. Github: github.com/andrei-polukhin/pgdbtemplate (https://github.com/andrei-polukhin/pgdbtemplate) The concept of template databases for testing is something every PostgreSQL team should consider, regardless of their primary programming language. Thanks for reading, and I look forward to your feedback!  submitted by /u/Individual_Tutor_647 (https://www.reddit.com/user/Individual_Tutor_647)

3 views04:26