Bonjour (Good morning)! Today I will be speaking about my experiences using Sequel and Roda to run a small government department, as well as what led us to adopt Ruby and these libraries.

My name is Jeremy Evans, and I have been using Ruby since 2004.

Our department’s software development approach differs from many other government departments.

We build all of our custom software internally using government staff, with no use of contractors.

All of our internal custom software development is done in Ruby. Ruby has been our primary development language for new projects since 2005, and all of our existing internal custom systems were converted to Ruby by 2009.

All of our SQL database access uses Sequel. We use PostgreSQL for our custom software development, and also have to interface with Microsoft SQL Server databases. Occassionally, we’ve had to access other databases, such as Oracle and Microsoft Access. Thankfully, Sequel ships with support for all of the databases we have needed to use.

Most of our software development is delivered as web applications, and for those we have used Roda since 2014.

Let me discuss some of the web applications that we develop.

Our largest application is our intranet site, called The Hub. The majority of our development is adding new features to The Hub.|The Hub has some pretty standard intranet features. It contains a lot of information on internal processes, has our comprehensive manual and our employee directory.

Each employee has a profile page showing information about the employee, such as their division and position, audits they have worked on, audits they are currently working on, any awards they have received.

The employee’s profile page also has a link to a map of the floor that they work on, with their desk location highlighted. This makes it easy for new staff to navigate the office, and for existing staff to easily find new staff.

Most of the new development centers around automating existing processes. When I started working for the department in 2000, almost all processes were manual, paper-based processes. Now, most of the common processes are automated via online forms.|For example, submitting requests to take time off, attend training, get reimbursed for overtime, modify facilities, purchase supplies and equipment, and many others are automated. Records are kept so employees can see the status of all previous requests.

Most of the processes have custom review/approval workflows based on different requirements. Some processes will only require supervisory review. In other cases, there will be between two and five levels of review, with the number of levels dependent on the request and the employee’s position.

Another web application that we develop is our recruiting system, which is split into two parts, a publicly accessible system used by applicants, and an internal system used by human resources staff. Most of the employees that we hire are entry level auditors directly out of university, and most of the recruiting system is designed to handle the recruiting process for these applicants.

The recruiting system allows prospective auditors to apply to take our online exam. After applying, our human resources staff review the application, and if they approve it, the applicant is notified and can take our online exam.

The online exam is timed and has 75 muliple choice questions. You have to get about 80% of the questions correct to rank highly enough to advance.

Assuming the applicant scores highly enough, they are notified they can advance further and can take our online writing assessment. When the applicant begins the writing assessment, they are given a prompt and an upload form, and they have two hours to upload a writing sample similar in style to our audit reports. This writing assessment is graded by our staff.

If the applicant passes the writing assessment, there is a phone interview and then an in-house interview, and the system handles the information related to those. The system also handles all internal workflows related to processing these applications, and has extensive reporting capabilities. There are also smaller subsystems of the recruiting system that handle the recruiting process for more advanced auditing positions.

Another major web application we develop is our recommendations system. One of our department’s primary functions is to make recommendations to improve government. The departments we audit are required to respond to our recommendations on a regular basis on their progress implementing our recommendations, until the recommendations have been fully implemented.

The recommendations system is split into three parts. The first part is externally accessible and allows other government departments to respond to our recommendations. The second part is internal and allows our staff to add recommendations, and to review responses to the recommendations.

Each response goes through four levels of review, and after being fully reviewed, the department’s response and our assessment of their response is posted on our website.|This holds departments accountable, and their implementations of our recommendations are often considered when the legislature reviews the department’s budget. We continue to follow up on recommendations we make to departments for up to 6 years after the release of our audit report.

The third part of the recommendations system is externally accessible and allows the legislature, press, and public to subscribe to be notified about new report releases and new responses to our recommendations. This system allows subscribing to receive notifications filtered to specific policy areas.

So how did we start using Ruby, and end up using Sequel and Roda? When I was first given the task of maintaining our websites back in 2003, they were developed as static pages using Net Objects Fusion. While I had no previous professional programming experience,

I did have some exposure to PHP, and I decided to use that.

I do not have a copy of the PHP code we used to use, but it was something like this.

At the top of every PHP file, we’d include a library with the shared code. This would establish a database connection and store it in the db variable.

All SQL statements were written manually.

All values used in the SQL had to be escaped and interpolated into the SQL manually.

To access data returned from the database, you would index into an array, you could not access the data by column name.

In late 2004, I heard about Rails, and tried it out. I saw that it was a great improvement over PHP.

Using Rails, the previous PHP code turned into much nicer ERB code, something like this.

Rails takes care of most of the SQL creation, except for this fragment.

It escapes and interpolates the submitted parameter into the SQL.

It allows for getting the returned attribute by calling a method using the column name.

After a few months of using Rails in personal projects and a few more testing it at work, I switched our intranet site from PHP to Rails in the summer of 2005.|I was pretty happy working with Rails for a few years, as it made things so much easier than PHP.

After being exposed to Sequel in 2008, I saw the benefits of Sequel’s method chaining approach to building queries. I converted all ActiveRecord usage to Sequel that year, and we have been using Sequel exclusively since.

Here’s some example ActiveRecord code before the upgrade. This is around the time of Rails 2.0. This code searches for paper forms, which used to be a common need before we started automating processes.

ActiveRecord did not and I believe still does not have public API support for case insensitive searching, so you had to manually use database-specific SQL.

Back then in ActiveRecord, you had to pass a hash of values to find.

You generally provided the filter conditions as an array with an SQL fragment and interpolated variables.

In cases where the filter conditions needed to be modified depending on the submitted parameters, you had to append to the first element of the conditions array to modify the SQL fragment.

You also had to append to the conditions array for each interpolated variable added to the SQL fragment.

With Sequel, this got much simpler.

Sequel even back in 2008 had built in support for case insensitive searching that we could use, instead of manually writing the SQL for it.

Since the beginning, Sequel has used a method chaining approach to building queries. We no longer had to worry about modifying the conditions array correctly.

If you notice, there are actually no SQL code fragments at all. After our switch to Sequel in 2008, only a small amount of code needed to use SQL fragments. As Sequel became more powerful, we stopped needing to use SQL code fragments. We no longer have code that uses SQL fragments or raw SQL at runtime in our web applications. All SQL executed is generated by Sequel.

In 2008, I learned about Sinatra, and was drawn to Sinatra’s much simpler approach to web development. In 2009, we started using Sinatra for all new development, and the initial versions of our recruiting and recommendations systems were written in Sinatra.

In Sinatra, routes are specified directly, with a block to handle the route, and the return value of the block being the response body. I found that Sinatra was much less complex than Rails, while still handling the needs for our recruiting and recommendations systems.

Another thing I appreciated about Sinatra was the API stablity. In the 5 years we used Sinatra, we never had to modify any of our Sinatra apps due to Sinatra version changes.

That was quite different from our experience with Rails, where every Rails minor version update required application changes, and sometimes even Rails tiny version updates required application changes.

While Sinatra was simple, in our experience, it was not very DRY. Here is the previous example with the form submission handler added.

Because Sinatra considers all routes separately, routes for the different request methods for the same request path repeat the path.

In most cases, code inside the route handlers was also duplicated. Some cases where it was not duplicated turned out to be bugs.|Sinatra did eventually add support for deduplicating the logic via before filters, but that resulted in the same issues as using before filters in Rails, making the code harder to understand by making important logic no longer local to the route handler.

Another issue with Sinatra is that it lacked support for handling email, having no equivalent for ActionMailer in Rails. So email handling in our Sinatra applications was ad hoc and quite messy, often just inlined directly into the route handling blocks.

In 2014, I was exposed to the routing approach used by Cuba, and saw how it addressed the complexity issues we were having in our Sinatra applications while still being much simpler than Rails.

With Cuba, routes are handled in a nested fashion, which naturally DRYs up code while still keeping the route handling localized. This resulted in significantly easier to understand code.

The path itself might be broken up to handle routing different levels of the path, but no part of the path is repeated.

Likewise, code used in handling the request may be separated, but the logic is not duplicated. Cuba’s approach still makes it simple to see how a specific route will be handled.

While I loved the nested routing aspect of Cuba, I found some of Sinatra’s behavior friendlier, such as route blocks returning the response body. I also wanted the ability to easily extend the behavior using plugins, which is one of the best aspects of using Sequel.

I ended up creating a fork of Cuba called Roda, integrating ideas from Sinatra and Sequel. I converted all of our web applications to Roda in the summer of 2014.|I did have to keep ActionMailer around for a couple months after that in one application, until Roda shipped with a mailer plugin. Roda’s mailer plugin offers functionality similar to ActionMailer, and allowed us to unify the email handling in all of our applications. Roda’s mailer plugin routes requests to send mail similar to how Roda itself routes web requests, and we found our email handling code was significantly simpler using Roda’s mailer plugin compared to using ActionMailer.

One thing that became apparent after the upgrade to Roda was that our web applications were noticeably faster.

This was especially apparent when running tests. With the exact same set of capybara tests, tests were twice as fast using Roda compared to using Rails.

Before our upgrade to Roda, our newer applications were running on Sinatra, and our older applications were running on Rails. Switching between the two required some cognative overhead. Starting with the upgrade to Roda, all of our applications run using the same set of libraries, and use the same library versions. This makes it much easier to switch between applications during development.

This is possible because Sequel’s and Roda’s plugin architecture allow for what I call complexity scaling. You can easily use Roda and Sequel with a few plugins to handle a small application without being overwhelmed. However, you can keep the same basic architecture and add plugins as needed to scale the application to hundreds or thousands of routes, without having to switch libraries.|This is difficult to do with Sinatra or Rails. Sinatra is not suited to large applications due to its routing architecture, and Rails is not suited to small applications due to its overhead and complexity.

As I am the department’s Information Security Officer, one focus area for me is security. As you would expect, we try to protect against the common vulnerabilities in web applications.

We attempt to mitigate cross site scripting by automatically escaping output in templates using Roda and Erubi, similar to Rails’ automatic escaping. This was something we were unable to do with Sinatra.

We protect against cross site request forgery using Roda’s route_csrf plugin, and enforce the use of form-specific CSRF tokens. This goes further than Rails’s CSRF protection, which allows generic CSRF tokens even when form-specific CSRF tokens are enabled.|Sinatra does not include CSRF protection. You can use rack_csrf, but that does not offer support for form-specific CSRF tokens.

Many security vulnerabilities in Ruby web applications stem from unexpected parameter types being submitted by an attacker. We protect against unexpected parameter types by using Roda’s typecast params plugin and Sequel’s type conversion to ensure that all parameter inputs are of the expected type.|This is something I think is lacking a good equivalent in both Sinatra and Rails. Rails has strong parameters, but not strong enough to say what type each parameter is expected to be.

Most of our web applications are used both by trusted staff and by untrusted users from the Internet. Our security strategy uses different web application processes to serve these users. The different processes use separate operating system and database users with reduced permissions and capabilities. This way an outside attacker cannot use a vulnerability in one of our web applications to perform actions only one of our staff should be able to perform.|We started doing this when we used Sinatra, and our usage of this has increased after our upgrade to Roda. This approach is difficult when using Rails unless you want to have separate application directories with no shared code.

Our department has now been using Sequel for over 11 years and Roda for over 5 years. The combination of the two has made web development productive and fun. Here are a few main advantages we have experienced using Sequel and Roda.

I think Roda and Sequel are easier to understand and therefore easier to learn. I am now the manager of the department’s IT unit, and I no longer handle most of the day-to-day programming on our web applications.|The day-to-day updating and adding features to the applications is now handled by another programmer. This programmer had no professional programming experience and had never programmed in Ruby before we hired her. She was able to quickly become productive and implement new features using Sequel and Roda. If we were using Rails, I think it would have taken her significantly longer to become productive.

We’ve found that applications converted from Rails to Sequel and Roda have been easier to maintain. Other than major version updates to Sequel and Roda, we have not had to make backwards compatibility changes. The backwards compatibility changes we have had to make in major updates have been smaller compared to similar changes in Rails versions. Part of the reason for this is that Sequel and Roda have offered plugins implementing new behavior before that behavior becomes the default, allowing for more gradual updates.|Our applications using Sequel and Roda total over one hundred thousand lines of Ruby code, and we maintain and add new features on a regular basis using the equivalent of one and a half positions. As a manager, I think that is remarkably efficient.

We have found that Sequel and Roda are signficiantly faster than Rails. Faster performance when serving requests and running tests, and more importantly faster development time to implement new features and make desired changes.

If you have not tried out Sequel or Roda yet, I encourage you to experiment with them and see if you also experience the same benefits that we have experienced.