Your Database is Underutilized

Relational databases are pretty good at storing and retrieving data. But if that’s all you’re using your relational database management system (RDBMS) for, you’re missing out.

Most databases provide robust tools for processing data, describing domain logic, and taking action when data changes, among other things. Many of these features are neglected by authors of popular frameworks like Rails (Active Record). This is because they are less standardized and more difficult to approach in a database-agnostic manner.

There are ways to use these features when they provide a table-like interface (e.g., you can use a view as the source of an Active Record model) or are supported by additional libraries or plugins. And they’re often worth the effort.

Why Bother?

Improved performance

Ever had memory problems in your web application because you were loading a lot of data to generate a summary? Moving logic into the database puts it closer to the data it needs for more efficient processing. Databases are built to be good at processing large amounts of data efficiently.

Centralized logic

Logic that lives in your database is accessible to a variety of tools and applications. Databases are often the lowest common denominator across an application ecosystem. Anything in the database is accessible to all other parts of the system.

Improved productivity

Using the right tools for the job can make all the difference. For example, the reporting possibilities provided by data cubes would be very difficult to replicate without them. These tools can be complex to manage. I wouldn’t look for developer efficiency gains simply by moving logic into stored procedures. But the ability to share the logic or use tools that provide additional leverage can improve effectiveness.

What to Do

Views

Views are essentially canned queries that can be shared across applications. They’re an easy place to start because you can treat them like tables in select statements or as sources for Active Record models. They’re good for encapsulating consistent business rules about relationships.

Materialized views

These are canned queries that cache their results. Different platforms have different mechanisms for keeping the cache up to date. Some will require custom intervention. If you have an expensive query or view, this may provide some help if you can manage the mechanisms used to keep the data fresh.

Stored procedures & functions

Stored procedures can include more of the programming concepts that you use in other parts of your application: variables, multiple SQL statements, conditionals, enumerating lists, etc. They are useful for moving domain and application code into the database. One example is calculating subscriber status and updating the users that have expired subscriptions. They provide the most value when a lot of data is involved or the logic they encapsulate is important to share across different toolsets.

Triggers

Triggers can help you take action when a row is inserted, updated, or deleted. Again, the database is typically the lowest common denominator in an application. If there’s something that needs to happen every time an update is made to the data, triggers are a good option. They are often used to execute a stored procedure in response to a change.

Other database addons & extensions

In addition to the features described above (which are supported by nearly every relational database except SQLite), many relational databases provide unique capabilities or plugins that enable other powerful features. A few examples include (pardon my obsession with Postgresql):

Utilize Your Database to its Potential

Databases are a large part of most applications, but they are often underutilized. Unfortunately, most modern web development tutorials ignore them completely. You shouldn’t! Use the broad set of features they provide to build an application that performs well and can share important logic across different parts of the app ecosystem.


Originally published at spin.atomicobject.com on August 31, 2016.

Show your support

Clapping shows how much you appreciated Atomic Object’s story.