Pre-seeded databases (dev dbs)

Squash makes it very easy to seed development databases. You can use your own sanitized data set or even clone production dumps (although in some cases this might not be recommend, see below).

The way it works is based on importing a development db dump from any SQL or non-SQL database engines (MySQL, PostgreSQL, MongoDB, etc) and then caching that data into Squash so that you don't need to do a full (and often times slow) import every time you start a new Squash environment.

You take care of the db dump sanitation process, which according to best practices should live in your own production environment (so that PII data never leaves your production environment). Squash has APIs (discussed below) allowing you to easily ship the sanitized db dump to the Squash Assets storage. Then you can also update the Squash cache by making an API call or starting a new Squash deployment.

Benefits

  • You have full control over your development db generation process.  Customer and/or sensitive data never needs to leave production.

  • Each Squash deployment will have its own copy of your development database, completely isolated from each other. Run db migration schemas on any of these deployments or add more test data without the fear of affecting other environments.

  • Keep your development databases always up-to-date, and update them as often as you want.

  • Do you have large databases? no problem, you can easily customize both the storage and the VM size (memory, CPU) of each deployment. Add as much storage as you need.

Steps to setup a pre-seeded database

  • Upload sanitized db dumps (or any dev db dumps as you see fit) into the Assets storage.

  • Update your deployment build process to automatically import this db dump during your application build process. See: using the assets storage within the Squash YAML file.

  • Then make sure to enable the deployment cache. This will allow you to load the db dump only once and re-use it for any new deployments in a much faster way. More details below.

  • Then run a test deployment for any branch of code, wait until it receives a success response and you can shut it down after that. Squash will automatically save a new deployment cache volume after a few minutes. You can see the deployment cache volumes on this page (make sure to login to Squash first).

  • Once the deployment cache volume is saved, next time you start new deployments Squash will automatically attach the cache into it. The database will be ready for you to use in a complete isolated fashion within the same host VM. You can then update the schema as you see fit.

How to leverage the deployment cache for Docker based builds

For Docker based builds, either using Dockerfile or Docker-compose, Squash will cache the dev db dump import process within the Docker image itself. The easiest way to accomplish this is to add the dump loading process as a command or script that gets called from the Dockerfile itself, or within the docker-compose file (or within a Dockerfile that is used by the docker-compose file).

Here is a Dockerfile snipped  showing the approach mentioned above: 

FROM ruby:2.5.1
RUN apt-get update -qq && \
  apt-get install -y build-essential libpq-dev && \
  curl -sL https://deb.nodesource.com/setup_8.x | bash - && apt-get install -y nodejs
RUN setuser my_app_user load_dev_db.sh

And here is how the "load_dev_db.sh" script would look like: 

#!/bin/bash
setuser postgres /usr/lib/postgresql/11/bin/pg_ctl -D /var/lib/postgresql/11/main start -w
sleep 3
setuser postgres psql -c "CREATE USER my_app_user WITH SUPERUSER PASSWORD 'my_app_user_pw';"
setuser postgres psql -c "CREATE DATABASE my_app OWNER my_app_user;"
setuser my_app_user psql my_app < sanitized-db.sql

How to leverage the deployment cache for non-Docker builds

  • Make sure to install the database service and import the db dump within the build_steps field of the YAML file. This will ensure everything gets cached by the Deployment Cache.

  • You would run any database migrations/schema changes within the launch_steps of the YAML file. Since this step is not cached it means your db schema will be always up to date according to the latest code in the branch.

Here is a sample .squash.yml file: 

deployments:
  default:
    dockerimage:
      ubuntu:14.04
    build_steps:
      - DEBIAN_FRONTEND=noninteractive apt-get update
      - DEBIAN_FRONTEND=noninteractive apt-get install -y \
      software-properties-common
      - DEBIAN_FRONTEND=noninteractive add-apt-repository \
      ppa:ubuntugis/ubuntugis-unstable
      - DEBIAN_FRONTEND=noninteractive apt-get install -y \
      python python-dev python3.5 python3.5-dev python-pip
        python-virtualenv libssl-dev libpq-dev git ssh
        build-essential libfontconfig1 libfontconfig1-dev
        locales gcc postgresql postgresql-contrib
        postgresql-9.3-postgis-2.1 sudo supervisor
      - COPY . /code
      - cd /code
      - bash ./create_venv.sh
      - service postgresql start && \
      sudo -u postgres psql -U postgres -c "CREATE USER myapp WITH PASSWORD 'myapp'" &&
        sudo -u postgres psql -U postgres -c "CREATE DATABASE myapp OWNER myapp" &&
      - unzip sanitized-db.zip && service postgresql start && \
      sleep 15 && sudo -u postgres psql myapp < sanitized-db.sql
      - WORKDIR /code
    post_build_steps:  
      - cd /code
      - /code/venv/bin/pip install -r requirements.txt
    launch_steps:
      - service postgresql start -w
      - sleep 5
      - cd /code
      - /code/venv/bin/python /code/manage.py migrate
      - /code/venv/bin/python /code/manage.py runserver 0.0.0.0:80
    ready_wait: 300
    copy_files_build_process:
      - /assets/sanitized-db.zip ~/code/sanitized-db.zip

How to automate updates of pre-seeded databases

This process is only needed if you change your development databases often.

  • Use the Assets API to ship database dumps to the assets folder.

  • Use the Deployment Cache API to force a cache update. This will essentially trigger a new deployment on the desired branch/deploymentID, and it will shut down after receiving a success response and update the cache afterwards.

How to manually update pre-seeded databases

If you don't change your pre-seeded databases often, here is a quick way to update your dev db dumps in Squash:

  • Manually upload the new development db dump file(s) to the Assets Web UI.

  • Then manually delete the Deployment Cache records in this page.

  • And you are ready to go. Next time you start new Squash deployments they will be built without cache. The first deployment to go down will automatically trigger a cache update and further deployments will be cached after that.