The SQL Server Problem

• During deployment, database is blown away
   
• A backup of the production database with a subset of the data is restored to environment
   
• Current release number is queried from database

The Current Flow

The SQL Server Problem

• The new release number retrieved from variable set in Bamboo
   
• All Master Scripts with release numbers after the database value and up to the Bamboo value are executed

The Current Flow

The SQL Server Problem

• Ensure that the database is in a consistent state for testing
   
• Ensure it's consistent with Production

The Rationale

The SQL Server Problem

• SQL Server is blown away and recreated during every CI & QA deployment
   
• This means QA must re-enter their test data after every deployment
   
• We are assuming that the subset of data from production will never change

The Problem

The SQL Server Problem

• We are dependent on an external binary file (the backup) in order to get the database up and running
   
• We are dependent on the Bamboo variable being set to the correct value - if it's not set correctly then we'll run the risk of executing updated code against an out of date schema

The Problem

The SQL Server Problem

• The database is no longer blown away and restored as part of the deployment process
   
• Instead, this action is now performed on-demand when QA needs to reset the database

The New Strategy - The Easy Part

The SQL Server Problem

• We no longer restore the database from production
   
• Instead, we will update the database schema via SQL scripts with a naming convention that indicates the order in which they should be executed
   
• A new table in the database will save the last executed script

The New Strategy - The Redesign

The SQL Server Problem

• No more MasterScript.sql
   
• File structure:
  • 3.66
    • 01-pp12345.sql
    • 02-pp12346
  • 3.67
    • 01-pp12347.sql
    • 02-pp12348.sql

The New Strategy - The File Structure

The SQL Server Problem

• Move version number from SystemConfiguration to its own table $version:
   
• Schema:
  • CurrentVersion VARCHAR(16)
     
• This decouples the database state from business-related configuration

The New Strategy - The $version Table

The SQL Server Problem

The New Strategy - The New Flow

• Query $version table to get file name of last release executed against it
   
• Find the SQL scripts in folders whose names come after the last release number
   
• Execute each script in order, updating the $version table after each script to ensure we know the state of the database if there is an error during one of the scripts

The SQL Server Problem

• The SQL will contain the schema changes that need to be applied
   
• Scripts will be idempotent so they can be re-executed if needed
   
• It will also contain the data for enum tables (e.g. MasterStatus)
   
• It will not contain any seed data

The New Strategy - The SQL

The SQL Server Problem

• QA's test data is persisted between deployments
   
• It's easy to determine the version of the database in any environment
   
• Developers can now create the database on their machines and work locally
   
• We ensure the DB and code are in sync

The Result

The MongoDB Problem

• Changes to the MongoDB schema are made in Krypton
   
• MongoDB is cleared out and re-seeded

The Current Flow

The MongoDB Problem

• If MongoDB is not re-seeded after there are schema changes, exceptions may be thrown when accessing records with old schema - this depends on the type of schema change that was made
   
• This means we can't make incremental schema changes once we go to Production

The Problem

The MongoDB Problem

• It's all about defensive coding
   
• Change Krypton to allow extra properties when deserializing - the default behavior is to throw an exception
   
• Provide logical defaults for properties in the scenarios where the record uses an old schema that doesn't have the given property

The New Strategy

The MongoDB Problem

• Use attributes on properties if the MongoDB deserializer requires more information about how to deserialize a property (e.g. a property used to be an int but now it's a string)
   
• Records with old schemas will automatically be upgraded to the new schema the next time they are saved

The New Strategy

The MongoDB Problem

• No more deserialization exceptions
   
• This means it is no longer a requirement to re-seed MongoDB when the schema changes

The Result

The Seeding Problem

• Krypton contains classes that allow us to seed each type of MongoDB collection
   
• There is also a console app that consumes these classes to allow Neon to seed data from a JSON file
   
• Only data for certain clients is modified - the whole collection is not dropped

The Current Architecture

The Seeding Problem

• On app start, bootstrapper determines whether environment is supposed to have its MongoDB data seeded via environment config 
   
• If seeding is supposed to happen, app looks for all seeding objects and seeds MongoDB based on what is defined in the seeding objects

The Current Flow - Krypton

The Seeding Problem

• Before Integration or E2E tests are executed, the seeding console app is executed with a link to the JSON files that contain the seed data

The Current Flow - Neon

The Seeding Problem

• Krypton and Neon have different ways of providing the seed data to the seeder
   
• Test writers need to know too much about how the data is stored in order to be able to write integration & e2e tests
   
• This can lead to test failures that are simply due to bad data

The Problem

The Seeding Problem

• There is currently no way to automatically seed SQL - we have to seed MongoDB with client IDs that we know are set up in SQL

The Problem

The Seeding Problem

• Requirements-based seeding:
  • Abstract away the seeding from this data goes in that table to I need an entity that has this set-up
     
• The seeding engine will transform the given requirements into MongoDB / SQL upserts

The New Strategy

The Seeding Problem

The New Strategy

/* Example seeding call */
seeder
  .seed
  .client({
    vanityUrl: 'foocompany'
  })
  .withOfferings({
    rowCount: 9,
    uniqueCategoryCount: 3
  })

The Seeding Problem

• The preceding example says I need a client with vanity URL 'foocompany' that has 9 offerings that are grouped into 3 unique categories
   
• It doesn't care about all the other plumbing that needs to take place to make that happen

The New Strategy

The Seeding Problem

• In order to ensure the tests know what the entities' properties will be consistent values will be provided.  For example, for the 9 offerings in the previous example:
  • Offering 1's name is offering 1
  • ...
  • Offering n's name is offering n

The New Strategy

The Seeding Problem

• When the database needs to be reset, a seed batch file can be executed that ensure the required data is in the database for QA testing

The New Strategy

The Seeding Problem

• Seeding becomes a lot easier to maintain
   
• When the schema changes, only the seeding engine needs to change instead of all of the seed data
   
• Requirements-based seeding aligns with Neon AJAX mocking strategy

The Result