Key point to keep in mind when writing database interaction layer (DAL)

Question

What are key aspects or points when writing a Data Access Layer (DAL).

Do you allow higher layers to pass "unique" queries to the DAL, ie not just standard update, insert and delete...?

How does one allow for scalability in such a layer?

Any comments would be great

Aiden

Answer 1

Speaking from my experience there are a few key aspects when writing a DAL.

1. Making the DAL an interface
2. Pick the right level of abstraction
3. Keep caching in mind

(1) Make sure you DAL is an interface. This allows you to mock the methods that might call the database for testing. If you have a getItems() function in the interface, then you can implement a class that goes out to your database and a class that mocks data for quick unit testing.

(2) While you're doing (1) make sure you've picked the right level of abstraction. As an example, I've seen a bad DAL at my current company that used an abstract query language for each call.

As an example, one call in our DAL would be:

getItems(List filters, int limit, int skip).

Instead I would have liked to see:

getItems(List ids, int pageSize, int page).

We let the details of the underlying data source (Mongo in this case) bleed through the interface. We were exposing Mongo specific features in our interface. Be careful not to do that!

If you do this step well you can swap your backing data store from relational to document to whatever.

(3) Keep caching in mind. Pretty simple, but make sure you consider how to cache your DAL calls. This will help address scalability. You might look into AOP (aspect oriented programming) which you would use with your DAL calls.

Your specific questions:

1. I would allow high layers to pass unique queries. Remember to pick the right abstraction though so that you can swap the persistence store if you need to.
2. Use caching a ton (likely a distributed cache) to provide scalability. Simple as that. =)