Breaking The Rules: Using SQLite To Demo Web Apps

This tutorial is based on a real-world solution that I have been successfully running for a SaaS demo web app since 2015. The tutorial is written for Ruby on Rails (my framework of choice) version 3 and up, but the basic concepts should be able to be adapted to any other language or framework. In fact, since Ruby on Rails follows the software paradigm "convention over configuration" it may even be easier to implement in other frameworks, especially bare languages (such as straight PHP) or frameworks that do not do much in terms of managing the database connections.

That being said, this technique is particularly well suited for Ruby on Rails. Why? Because, for the most part, it is "database agnostic." Meaning that you should be able to write your Ruby code and switch between databases without any issues.

A sample of a finished version of this process can downloaded from GitHub.

The First Step: Deployment Environment

We will get to deployment later, but Ruby on Rails is by default split into development, test and production environments. We are going to add to this list a new demo environment for our app that will be almost identical to the production environment but will allow us to use different database settings.

In Rails, create a new environment by duplicating the config/environments/production.rb file and rename it demo.rb. Since the demo environment will be used in a production like setting, you may not need to change many configuration options for this new environment, though I would suggest changing config.assets.compile from false to true which will make it easier to test locally without having to precompile.

If you are running Rails 4 or above, you will also need to update config/secrets.yml to add a secret_key_base for the demo environment. Be sure to make this secret key different than production to ensure sessions are unique between each environment, further securing your app.

Next you need to define the database configuration in config/database.yml. While the demo environment will primarily use the duplicated database that we will cover in the next section, we must define the default database file and settings to be used for our demo. Add the following to config/database.yml:

demo:
  adapter: sqlite3
  pool: 5
  timeout: 5000
  database: db/demo.sqlite3

In Rails, you may also want to check your Gemfile to make sure that SQLite3 is available in the new demo environment. You can set this any number of ways, but it may look like this:

group :development, :test, :demo do
  gem 'sqlite3'
end

Once the database is configured, you need to rake db:migrate RAILS_ENV=demo and then seed data into the database however you wish (whether that is from a seed file, manually entering new data or even duplicating the development.sqlite3 file). At this point, you should check to make sure everything is working by running rails server -e demo from the command line. While you are running the server in the new demo environment, you can make sure your test data is how you want it, but you can always come back and edit that content later. When adding your content to the demo database, I would recommend creating a clean set of data so that the file is as small as possible. However, if you need to migrate data from another database, I recommend YamlDb, which creates a database-independent format for dumping and restoring data.

If your Rails application is running as expected, you can move on to the next step.

The Second Step: Using The Demo Database

The essential part of this tutorial is being able to allow each session to use a different SQLite database file. Normally your application will connect to the same database for every user so that additional code will be needed for this task.

To get started with allowing Ruby on Rails to switch databases, we first need to add the following four private methods into application_controller.rb. You will also need to define a before filter for the method set_demo_database so that logic referencing the correct demo database is called on every page load.

# app/controllers/application_controller.rb

# use `before_filter` for Rails 3
before_action :set_demo_database, if: -> { Rails.env == 'demo' }

private

  # sets the database for the demo environment
  def set_demo_database
    if session[:demo_db]
      # Use database set by demos_controller
      db_name = session[:demo_db]
    else
      # Use default 'demo' database
      db_name = default_demo_database
    end

    ActiveRecord::Base.establish_connection(demo_connection(db_name))
  end

  # Returns the current database configuration hash
  def default_connection_config
    @default_config ||= ActiveRecord::Base.connection.instance_variable_get("@config").dup
  end

  # Returns the connection hash but with database name changed
  # The argument should be a path
  def demo_connection(db_path)
    default_connection_config.dup.update(database: db_path)
  end

  # Returns the default demo database path defined in config/database.yml
  def default_demo_database
    return YAML.load_file("#{Rails.root.to_s}/config/database.yml")['demo']['database']
  end

Since every server session will have a different database, you will store the database filename in a session variable. As you can see, we are using session[:demo_db] to track the specific database for the user. The set_demo_database method is controlling which database to use by establishing the connection to the database set in the session variable. The default_demo_database method simply loads the path of the database as defined in the database.yml config file.

If you are using a bare language, at this point you can probably just update your database connection script to point to the new database and then move on to the next section. In Rails, things require a few more steps because it follows the "convention over configuration" software paradigm.

The Third Step: Duplicating The SQLite File

Now that the app is set up to use the new database, we need a trigger for the new demo session. For simplicity's sake, start by just using a basic "Start Demo" button. You could also make it a form where you collect a name and email address (for a follow up from the sales team, etc.) or any number of things.

Sticking with Rails conventions, create a new 'Demo' controller:

rails generate controller demos new

Next, you should update the routes to point to your new controller actions, wrapping them in a conditional to prevent it from being called in the production environment. You can name the routes however you want or name them using standard Rails conventions:

if Rails.env == 'demo'
  get 'demos/new', as: 'new_demo'
  post 'demos' => 'demos#create', as: 'demos'
end

Next, let's add a very basic form to the views/demos/new.html.erb. You may want to add additional form fields to capture:

<h1>Start a Demo</h1>
<%= form_tag demos_path, method: :post do %>
  <%= submit_tag 'Start Demo' %>
<% end %>

The magic happens in the create action. When the user submits to this route, the action will copy the demo.sqlite3 file with a new unique filename, set session variables, login the user (if applicable), and then redirect the user to the appropriate page (we will call this the 'dashboard').

class DemosController < ApplicationController
  def new
    # Optional: setting session[:demo_db] to nil will reset the demo
    session[:demo_db] = nil
  end

  def create
    # make db/demos dir if doesn't exist
    unless File.directory?('db/demos/')
      FileUtils.mkdir('db/demos/')
    end

    # copy master 'demo' database
    master_db = default_demo_database
    demo_db = "db/demos/demo-#{Time.now.to_i}.sqlite3"
    FileUtils::cp master_db, demo_db

    # set session for new db
    session[:demo_db] = demo_db

    # Optional: login code (if applicable)
    # add your own login code or method here
    login(User.first)

    # Redirect to wherever you want to send the user next
    redirect_to dashboard_path
  end
end

Now you should be able to try out the demo code locally by once again launching the server using running rails server -e demo.

If you had the server already running, you will need to restart it for any changes you make since it is configured to cache the code like the production server.

Once all the code works as expected, commit your changes to your version control and be sure that you commit the demo.sqlite3 file, but not the files in the db/demos directory. If you are using git, you can simply add the following to your .gitignore file:

If you want to collect additional information from the demo user (such as name and/or email), you will likely want to send that information via an API to either your main application or some other sales pipeline since your demo database will not be reliable (it resets every time you redeploy).

!/db/demo.sqlite3
db/demos/*

Final Step: Deploying Your Demo Server

Now that you have your demo setup working locally, you will obviously want to deploy it so that everyone can use it. While every app is different, I would recommend that the demo app lives on a separate server and therefore domain as your production app (such as demo.myapp.com). This will ensure that you keep the two environments are isolated. Additionally, since the SQLite file is stored on the server, services like Heroku will not work as it does not provide access to the filesystem. However, you can still use practically any VPS provider (such as AWS EC2, Microsoft Azure, etc). If you like the automated convenience, there are other Platforms as Service options that allow you to work with VPS.

Regardless of your deployment process, you may also need to check that the app has the appropriate read/write permissions for your directory where you store the demo SQLite files. This could be handled manually or with a deployment hook.

SQLite Won't Work For Me. What About Other Database Systems?

No two apps are created alike and neither are their database requirements. By using SQLite, you have the advantage of being able to quickly duplicate the database, as well as being able to store the file in version control. While I believe that SQLite will work for most situations (especially with Rails), there are situations where SQLite might not be suitable for your application's needs. Fortunately, it is still possible to use the same concepts above with other database systems. The process of duplicating a database will be slightly different for each system, but I will outline a solution for MySQL and a similar process exists with PostgreSQL and others.

The majority of the methods covered above work without any additional modifications. However, instead of storing a SQLite file in your version control, you should use mysqldump (or pg_dump for PostgreSQL) to export a SQL file of whichever database has the content that you would like to use for your demo experience. This file should also be stored in your version control.

The only changes to the previous code will be found in the demos#create action. Instead of copying the SQLite3 file, the controller action will create a new database, load the sql file into that database and grant permissions for the database user if necessary. The third step of granting access is only necessary if your database admin user is different from the user which the app uses to connect. The following code makes use of standard MySQL commands to handle these steps:

def create
  # database names
  template_demo_db = default_demo_database
  new_demo_db = "demo_database_#{Time.now.to_i}"

  # Create database using admin credentials
  # In this example the database is on the same server so passing a host argument is not require
  `mysqladmin -u#{ ENV['DB_ADMIN'] } -p#{ ENV['DB_ADMIN_PASSWORD'] } create #{new_demo_db}`

  # Load template sql into new database
  # Update the path if it differs from where you saved the demo_template.sql file
  `mysql -u#{ ENV['DB_ADMIN'] } -p#{ ENV['DB_ADMIN_PASSWORD'] } #{new_demo_db} < db/demo_template.sql`

  # Grant access to App user (if applicable)
  `mysql -u#{ ENV['DB_ADMIN'] } -p#{ ENV['DB_ADMIN_PASSWORD'] } -e "GRANT ALL on #{new_demo_db}.* TO '#{ ENV['DB_USERNAME'] }'@'%';"`

  # set session for new db
  session[:demo_db] = new_demo_db

  # Optional: login code (if applicable)
  # add your own login code or method here
  login(User.first)

  redirect_to dashboard_path
end

Ruby, like many other languages including PHP, allows you to use backticks to execute a shell command (i.e., `ls -a`) from within your code. However, you must use this with caution and ensure no user-facing parameters or variables can be inserted into the command to protect your server from maliciously injected code. In this example, we are explicitly interacting with the MySQL command line tools, which is the only way to create a new database. This is the same way the Ruby on Rails framework creates a new database. Be sure to replace ENV['DB_ADMIN'] and ENV['DB_ADMIN_PASSWORD'] with either your own environment variable or any other way to set the database username. You will need to do the same for the ENV['DB_USERNAME'] if your admin user is different from the user for your app.

That's all that it takes to switch to MySQL! The most obvious advantage of this solution is that you don't have to worry about potential issues that might appear from the different syntax between database systems.

Eventually, a final decision is made based on the expected quality and service, rather than convenience and speed, and it’s not necessarily influenced by price point alone.

Final Thoughts

This is just a starting point for what you can do with your new demo server. For example, your marketing website could have a link to "Try out feature XYZ." If you don't require a name or email, you could link demos#create method with a link such as /demos/?feature=xyz and the action would simply redirect to the desired feature and/or page, rather than the dashboard in the above example.

Also, if you use SQLite for the development and demo environments, always having this sample database in version control would give all your developers access to a clean database for use in local development, test environments or quality assurance testing. The possibilities are endless.

You can download a completed demo from GitHub.