Tips for Agile Database Development

Relational databases still power most of our enterprise-level systems and this is not about to change. The implication for the development community is that there continues to be an impendence mismatch between the relational model of the database and the object-oriented semantics of the application domain model. This divide is so profound that our popular system development patterns strictly separate the business layer from the data layer. This dichotomy translates into a significant amount of work for a development team such as creating database schemas, tuning database performance, mapping the domain classes to data access classes and maintaining all of the above throughout the system lifetime. Just like we apply agile practices to the business layer, we must apply similar principles to the data layer, of course with a few twists. Here are some tips on how to maintain the database layer in an agile ecosystem:

• Version your database. This may simply mean maintaining an integer value in a table in your database schema to indicate the version of the schema. Over time the database schema will evolve and there will inevitably be a mismatch between the application domain code and the schema it is running against. By checking the supported database version, the application domain code can spot these issues up front.

• Check version before accessing database. Make the database access code smart to check the version of the database before performing any operations. If an older version of the application needs to work with a newer schema of the database, write a compatibility layer that makes the database appear like the old schema to the older version of the application. Placing all data access code in one place helps in this scenario. 
• Create database upgrade and migration scripts. Create and maintain incremental database scripts that migrate a database schema from any released earlier version to any other later schema version up to the very latest version. In order to write good Unit Tests, we need to be able create any released version of the database. These scripts generally accomplish two tasks: Update the database schema and Migrate the data. 
• Put database access code in one place. Put all database access code in one place and have the application domain code talk to this database access code. That is, do not disperse your SQL Statements all over the place. Furthermore, create a well-defined interface for the database access code. This will facilitate creation of a Mock database access code layer that will be instrumental in creating Unit Tests for the application domain code. 
• Use source control. Implement source control over the Database Update scripts just like you would over any other kind of source code. This is essential to traceability of the database changes made. 
• Unit test database access code. Write robust Unit Tests for the database access code. These tests are separate from the Unit Tests for the application domain code. The purpose of these tests is to verify the CRUD (Create, Read, Update and Delete) behavior of your database access code. 
• Performance test database access code. Write performance tests to write large amounts of data using the database access code to spot inefficiencies.  
• Create database utilities and scripts. Create tools and scripts to allow testers and developers to create any released version of the database. A particularly useful script is a script that allows us to copy/move a database in its entirety from one machine to another. If a client reports a problem, an identical environment can then be easily created for diagnosis. These tools and scripts not only create appropriate database schema and but also populate appropriate seed data in the tables. 
• Integrate very often. Make database related activities part of the continuous integration cycle, just like your application domain code.
• Automate as much as you can. This includes writing automated tests, continuous integration server and writing simple tools and scripts to recreate a database schema with appropriate configuration/seed data. What cannot be used easily, does not usually get used. 
• Write a utility for database health check. Write a utility library to verify the structure of a given database. Even though we versioned our database, it is possible some table is missing a column, a constraint is non-existent or a trigger is not present. A utility to verify the validity of a database at database object level (table, views, stored procedures..) is invaluable to diagnosing problems in testing and at client sites. 
• Version compatibility matrix. Keep a compatibility matrix of what version of application works with what version of the database. Make it freely and widely available to development and onsite teams for early diagnosis of any database related issues.
• Use an ORM framework. When accessing database from your application, seriously consider employing an ORM framework. If you use a widely used, reputable ORM library, not only will you end up writing less code, you will also introduce fewer bugs. Building SQL queries by concatenating strings in code is error prone and opens up your application to numerous security vulnerabilities, such as SQL Injection attacks. By employing a reputable ORM, you can greatly reduce the security risk profile of your application.

Common Linux / Unix Commands

List Files 

ls

ls -la

ls -laSh

ls -laShr

ls -laS

ls -laShR

ls -lu

l - long listing

a - list all files including hidden, . and ..

A - don't show . and ..

S - sort by size

h - size in human readable format

r - sort in reverse order

R - list subdirectories recursively

u - sort alphabetically

Help on ls (or another command)

man ls

Create Directory

mkdir

Change to Path

cd path

Go up directory

cd ..

Update access date or create a file

touch fname 

Move a file

mv fname targetdir/fname

Copy a file

cp fname targetdir/fname

Delete a file

rm targetdir/fname

Delete a directory

rmdir targetdir

Edit file

nano fname

Download and save url content to disk

wget url

Reference: https://www.informit.com/articles/article.aspx?p=2858803&seqNum=2

Software Development Best Practices

SOLID PRINCIPLES

S - Single Responsibility Principle

An element of code (such as module, class or function) should do one primary thing and nothing else. That is, do not build Swiss army knife code elements. For example, a function that changes the case of a string should not trim it as well.

O - Open/Closed Principle

Do not alter the foundational code (such as base classes and common functions) but rather compose or extend them. Modules, classes and functions should be open for extension but closed for modification. The initial functionality should not be changed but may be extended via composition or inheritance.

L - Liskov Substitution Principle

Program to interfaces. Your business logic should be programmed against class interfaces (not concrete implementation of classes). A subclass should have "is a" relationship with the base class. Wherever you can use a base class, you should be able to use a derived class.

I - Interface Segregation Principle

Interfaces should be minimal and tailored to the function they need to perform. Minimize the number of methods in a given interface. Instead, expose multiple smaller interfaces. Clients can choose to implement just the interfaces they require.

D - Dependency Inversion Principle

Do not use concrete implementations directly. High level modules should not depend on lower level modules. Constructors for higher level modules should accept abstractions (interfaces) of lower level modules. At runtime, Dependency Container injects a concrete instance of the abstraction into the constructor.

SEPARATION OF CONCERNS

Similar to Single Responsibility Principle in SOLID but applied at a macro level (Systems, Modules, Sub-Systems). For example, a payment processing module should only perform payment processing and nothing else (i.e. it should not calculate invoices or bills).

DRY - DON'T REPEAT YOURSELF

Do not implement the same logic twice. Put it in a common module, library, class or function and use it everywhere.

Factor out common code into helper classes, methods and components. This makes the testing effort more concise and helps track down bugs to a single place in code instead of several repeating code sections.

MINIMIZE THE CODE YOU WRITE

Minimize the amount of code you have to write to solve a problem. The less code there is, the lesser the chance of buggy code. Code Minimization goes beyond DRY in that you may opt to look at alternate design approaches that minimize the amount of code you have to write to begin with. Remember code is a liability! The less code you have to solve the problem, the better. Examples:

• Use a well established library or software package to solve the problem instead of writing code to do the same.
• Use declarative code instead of imperative code. For example in .Net, instead of using for loops use LINQ.
• Instead of using switch/case statements to select a value or action use a table driven approach to look up a value or execute an action (a database table or an in-memory data structure should do the trick).

YAGNI - YOU AREN'T GONNA NEED IT

Do not implement code that is not immediately needed now even if you think you might need it in the future (you probably won't or if you do, you will need something entirely different).

Don't anticipate features and try to adapt your design or code to the features that are not necessary to complete the stories at hand. The chances are that either the requirements will change over time or the features you anticipate are never really needed. Do the simplest thing that will work.

USE DESIGN PATTERNS

When writing code check if there is a well-known design pattern that can be used to make implementation more robust and maintainable. Design Pattern Examples

ORGANIZE YOUR CODE WELL

Use polymorphism (abstract class + derived classes) to implement specific behaviors instead of conditionals sprinkled all over the code to handle differing behaviors. Use a single switch to instantiate the polymorphic derived class of the desired behavior and have the controlling logic invoke the behavior in a general fashion that is applicable to the entire family of the derived polymorphic classes.

Keep methods (functions) short and single-purposed. Complex methods should be no longer than 20 lines. Simpler methods should be no longer than 10 lines.

Do not pass too many parameters into a method. When several parameters must be passed into a method, create a request object that contains the parameters and pass the request object into the method.

Name the methods purposefully. The name should clearly reflect what the method does. A comment for the method should ideally be unnecessary. Write code that reads like prose. Comments should not be necessary when reading the code. Refactor logic into smaller methods with descriptive names. When reading the main method, the reader should be able deduce the purpose of the helper methods being called and understand what the main method is trying to accomplish without reading the implementation of the helper methods.

Do not enter who changed the code or the what the ticket or issue number was in code comments. Such information belongs in the source control check-in comments.

GET HELP FROM THE COMPILER

Use strongly type variables and constructs (generics, for example) to detect problems as early as possible (at compile time instead of runtime). Use enumerations instead of string values. Use named constants instead of string literals. Pay attention to compiler warnings.

TESTABILITY

Write testable code

Structure and implement your code so that it is easily testable. This mind set will naturally help you write code that is loosely coupled and minimizes inter-dependencies. The loose coupling is essential for testing a class or a component in isolation as is necessary when writing Unit Tests.

Write automated tests

By all means write automated Unit Tests. Better yet, take the Test Driven Development (TDD) route. That is, devise and write your tests before you start developing. TDD will greatly help in the design of the system in addition to the more obvious benefit of having a more complete set of automated tests. Having good automated unit tests allow us to refactor our code confidently.

Integrate often - Fail fast

Don't sit on pending changes. Check them in right away to find any incompatible changes. It is better to fail fast and correct the problem earlier in the cycle than to sit on a change and having to figure out why things don't work due to a change you made last week.

REFACTORING

Groom (clean up) code without changing the underlying functionality often. Refactoring is essential for long term maintenance of the code and to facilitate future enhancements.

Over the time, the code will naturally decay. You will see violations of DRY, Minimize Code and even YAGNI principles. It is imperative to take time to periodically refactor the code to ensure its maintainability over time. Remove and discard dead code. Such code only adds noise to your code base. The lower the noise-to-signal (code) ratio, the better.

Async calls, why call ConfigureAwait(false)

Adding .ConfigureAwait(false) to your async call takes away the overhead of context switching of threads.

DoSomething();  // on thread 1

await DoMoreAsync().ConfigureAwait(false); // on thread 2

DoSomethingElse();  // continues the work on thread 2 (instead of having to switch to thread 1)

The default for ConfigureAwait is true. But ConfigureAwait(false) is the right thing to do, if  the code following the async all does not rely on the context of thread 1 (thread storage of thread 1, for example).

Async method signature example in C#

Interface

Task<GetDatabaseServersResponse> GetDatabaseServersAsync(GetDatabaseServersRequest request, CancellationToken cancellationToken = default(CancellationToken));

Implementation

public async Task<GetDatabaseServersResponse> GetDatabaseServersAsync(GetDatabaseServersRequest request, CancellationToken cancellationToken = default(CancellationToken))
        {
            var list = await _dbContext.DatabaseServer.ToListAsync(cancellationToken);
            return new GetDatabaseServersResponse(_mapper.Map<List<DatabaseServerRm>>(list), request.TraceId);
        }

Executing an async method synchronously in C#

void NotAnAsyncMethod()
{
try
{
   var answer = someAsyncMethod().GetAwaiter().GetResult();
}
catch (Exception ex)
{
   // ex is our application's exception instead of aggregate exception
   //    aggregate exception is thrown if .Wait() and .Result are used instead
}
}

Creating distinct pairs of values from database table

SELECT * FROM tbl1;
A
B

SELECT t1.val, t2.val
FROM tbl1 t1, tbl1 t2;

A A
B A
A B
B B

SELECT t1.val, t2.val
FROM tbl1 t1, tbl1 t2
WHERE t1.val <> t2.val;

B A
A B

SELECT t1.val, t2.val
FROM tbl1 t1, tbl1 t2
WHERE t1.val <> t2.val
AND t1.val < t2.val;

A B