If you have a few apps in production like me, you might want a tool to make database backups easy. In this tutorial, we're going to build an app using Django and React that will enable us create and download scheduled database backups.

Create the project and backend app - commit

We'll start with creating a virtual environment. I tend to keep all my virtual environments as sub-directories inside one directory for easy management. So, in my case that will be venvs/janitor and I'll run the command python3 -m venv venvs/janitor to create a virtual environment for this app.

Activate the virtual environment you created with source ../venvs/janitor/bin/activate. If your activation was successful, you should see a (janitor) before the prompt on the command line.

Install Django and Django REST Framework by running pip3 install django djangorestframework.

Create the project with django-admin startproject janitor. Next, create the backend app by running cd janitor && django-admin startapp backend. Then, register the app by appending its name to INSTALLED_APPS in ./janitor/settings.py like so:

INSTALLED_APPS = [
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    'backend'
]

Create the model - commit

With the app in place it’s time to create our model. A model is an object representing your table’s data. Almost every web framework has the concept of models.

A Django model may have one or more fields - each field being a column in your table. Before moving forward let’s define our model requirements: To connect to a database, we need it's name, where it's hosted, what the database server is and what port it communicates on. We need a username and password for authentication. We need to keep track of our backup schedule (daily or weekly), day and time and how many previous backups we should keep around.

With our requirements in mind, our ./backend/models.py would be:

from django.db import models

class Schedule(models.Model):
  db = models.CharField(max_length=15) # mysql, postgresql
  name = models.CharField(max_length=200, unique=True)
  host = models.CharField(max_length=50, default='127.0.0.1')
  port = models.IntegerField()
  username = models.CharField(max_length=200)
  password = models.CharField(max_length=200)
  schedule = models.CharField(max_length=10) # daily, weekly
  day = models.CharField(max_length=10) # day
  time = models.CharField(max_length=5) # 12:00
  keep = models.IntegerField(default=5)
  created_at = models.DateTimeField(auto_now_add=True)
  updated_at = models.DateTimeField(auto_now=True)

  def __str__(self):
    return '{}://{}:{}/{}'.format(self.db, self.host, self.port, self.name)

  class Meta:
    db_table = 'schedules'

With the model in place, let's make a migration with python3 manage.py makemigrations backend and migrate with python3 manage.py migrate.

NOTE: The default SQLite is enough for my purposes, but you could use whatever database server is appropriate for you.

Creating a serializer - commit

Serialization is the act of transforming an object into another data format. After transforming an object we can save it to a file or send it through the network. A Django model is a Python class and to send it through a network (as JSON) we have to use a serializer. Django provides a REST serializer to do that. A serializer does 2-way conversion so it would also convert JSON objects to Python objects as well.

Create a new file named ./backend/serializers.py and paste the following into it:

from rest_framework import serializers
from backend.models import Schedule

class ScheduleSerializer(serializers.ModelSerializer):
  class Meta:
    model = Schedule
    fields = '__all__'

Setting up the view and routes - commit

Django is an MVT (Model – View – Template) framework. The View takes care of the request/response lifecycle. Django provides function views, class-based views, and generic views. In most cases, it is advisable to use function views only if writing the view by hand is faster than customizing a generic view.

As with plain Django, Django REST has function based views, class based views and generic API views (which we'll be using).

Open ./backend/views.py and add:

from backend.models import Schedule
from backend.serializers import ScheduleSerializer
from rest_framework.generics import ListCreateAPIView, UpdateAPIView, DestroyAPIView

class ScheduleView(ListCreateAPIView, UpdateAPIView, DestroyAPIView):
  queryset = Schedule.objects.all()
  serializer_class = ScheduleSerializer

With that we created a view for handling GET, POST, PUT and DELETE requests.

We'll be putting mapping our views to /api/schedules/.

First, add path('api/', include('backend.urls')), to urlpatterns in ./janitor/urls.py.

Next, create a file ./backend/urls.py and paste in:

from django.urls import path
from .views import ScheduleView

urlpatterns = [
    path('schedules/', ScheduleView.as_view()),
    path('schedules/<int:pk>', ScheduleView.as_view())
]

Finally, we need to enable Django REST framework by adding rest_framework to INSTALLED_APPS in ./janitor/settings.py.

Let's move on to the frontend.

Setup frontend app - commit

We'll create a Django app to serve as a host for our React app.

Run django-admin startapp frontend in the root folder of the project. Create folders for static content with mkdir -p ./frontend/{static,templates}/frontend and a folder for our components with mkdir -p ./frontend/src/components.

Create ./frontend/package.json with the following content:

{
  "name": "frontend",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "watch": "webpack --watch --mode development src/index.js --output static/frontend/main.js",
    "dev": "webpack --mode development src/index.js --output static/frontend/main.js",
    "build": "webpack --mode production src/index.js --output static/frontend/main.js"
  },
  "keywords": [],
  "author": "ukchukx",
  "license": "ISC",
  "devDependencies": {
    "@babel/core": "^7.5.5",
    "@babel/preset-env": "^7.5.5",
    "@babel/preset-react": "^7.0.0",
    "babel-loader": "^8.0.6",
    "babel-plugin-transform-class-properties": "^6.24.1",
    "prop-types": "^15.7.2",
    "react": "^16.9.0",
    "react-dom": "^16.9.0",
    "webpack": "^4.39.3",
    "webpack-cli": "^3.3.7"
  }
}

Create ./frontend/.babelrc with the following content:

{
  "presets": [
    "@babel/preset-env",
    "@babel/preset-react"
  ],
  "plugins": [
    "transform-class-properties"
  ]
}

Create ./frontend/webpack.config.js with the following content:

module.exports = {
  module: {
    rules: [
      {
        test: /\.js$/,
        exclude: /node_modules/,
        use: {
          loader: "babel-loader"
        }
      }
    ]
  }
}

We then install our NPM dependencies with cd frontend; npm i; cd ...

We need a template to house our React app. First, we create a view to serve the template by adding the following code to ./frontend/views.py:

def index(request):
  return render(request, 'frontend/index.html')

Then, create the template in ./frontend/templates/frontend/index.html with:

<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/bulma/0.6.2/css/bulma.min.css">
    <title>Janitor</title>
  </head>
  <body>
    <section class="section">
      <div class="container">
        <div id="app" class="columns"><!-- React --></div>
      </div>
    </section>
  </body>
  {% load static %}
  <script src="{% static "frontend/main.js" %}"></script>
</html>

As you've noticed, we're using Bulma CSS for this app.

After that, add path('', include('frontend.urls')), to urlpatterns in ./janitor/urls.py. Also change from django.urls import path to from django.urls import path, include in the same file.

Then, create ./frontend/urls.py with:

from django.urls import path
from .views import index

urlpatterns = [
  path('', index)
]

Enable the frontend app by adding frontend to INSTALLED_APPS in ./janitor/settings.py, which should now look like:

INSTALLED_APPS = [
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    'rest_framework',
    'backend',
    'frontend'
]

List schedules - commit

Create ./frontend/src/index.js with the line:

import App from './components/App';

Create the following files:

./frontend/src/components/App.js with:

import React from 'react';
import ReactDOM from 'react-dom';
import SchedulePage from './SchedulePage';

const App = () => (<SchedulePage />);

const wrapper = document.getElementById('app');

if (wrapper) ReactDOM.render(<App />, wrapper);
App.js

./frontend/src/components/SchedulePage.js with:

import React, { Component } from 'react';
import ScheduleTable from './ScheduleTable';

class SchedulePage extends Component {
  state = {
    schedules: [],
    endpoint: 'api/schedules/'
  };

  componentDidMount() {
    fetch(this.state.endpoint)
      .then(response => response.status === 200 ? response.json() : [])
      .then(schedules => this.setState({ schedules }));
  }

  render() {
    return <ScheduleTable schedules={this.state.schedules} />;
  }
}

export default SchedulePage;
SchedulePage.js

./frontend/src/components/ScheduleTable.js with:

import React, { Component } from 'react';
import PropTypes from 'prop-types';

class ScheduleTable extends Component {
  static propTypes = {
    schedules: PropTypes.array.isRequired
  };

  render() {
    return (
      <div className="column">
        <h2 className="subtitle">
          Showing <strong>{this.props.schedules.length}</strong> schedules
        </h2>
        <table className="table is-striped">
          <thead>
            <tr>
              <th>Name</th>
              <th>Database</th>
              <th>Host</th>
              <th>Schedule</th>
              <th></th>
            </tr>
          </thead>
          <tbody>
            {this.props.schedules.map((s) => (
              <tr key={s.id}>
                <td>{s.name}</td>
                <td>{s.db}</td>
                <td>{s.host}</td>
                <td>{s.schedule === 'weekly' ? `${s.day} @ ${s.time}` : `Daily @ ${s.time}`}</td>
                <td>
                  <div className="buttons are-small">
                    <button className="button is-outlined">Backup now</button>
                    <button className="button is-outlined">Update</button>
                    <button className="button is-outlined is-danger">
                      Delete
                    </button>
                  </div>
                </td>
              </tr>
            ))}
          </tbody>
        </table>
      </div>
    );
  }
}

export default ScheduleTable;
ScheduleTable.js

Let's run our app. In a different terminal window, run npm run watch to compile our React app when any file changes and in our virtual environment run python3 manage.py runserver. If you open django localhost in a browser, you'll see an empty table. Let's create schedules.

Create schedules - commit

Aside: I modified the index.html template to load Bulma CSS locally. You can see the changes here.

Create ./frontend/src/components/ScheduleForm.js with:

import React, { Component } from 'react';
import PropTypes from 'prop-types';

class ScheduleForm extends Component {
  static propTypes = {
    endpoint: PropTypes.string.isRequired
  };

  defaultForm = {
    db: 'mysql',
    name: '',
    host: 'localhost',
    port: 3306,
    username: 'root',
    password: '',
    keep: 5,
    schedule: 'daily',
    day: 'Monday',
    time: '23:59'
  };

  state = {
    dbs: ['mysql', 'postgresql'],
    schedules: ['daily', 'weekly'],
    days: ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'],
    isFormValid: false,
    form: { ...this.defaultForm }
  };

  handleChange = (e) => {
    let { form: { port, username } } = this.state;
    
    if (e.target.name === 'db') {
      port = e.target.value === 'mysql' ? 3306 : 5432;
      username = e.target.value === 'mysql' ? 'root' : 'postgres';
    }

    const form = { ...this.state.form, port, username, [e.target.name]: e.target.value };
    
    this.setState({ ...this.state, form, isFormValid: this.validateForm() });
  }

  handleSubmit = (e) => {
    e.preventDefault();
    e.stopPropagation();

    fetch(this.props.endpoint, {
      method: 'post',
      body: JSON.stringify(this.state.form),
      headers: new Headers({ 'Content-Type': 'application/json' })
    })
    .then((response) => {
      this.clearForm();
    });
  };

  clearForm = () => {
    this.setState({ ...this.state, form: { ...this.defaultForm }, isFormValid: false });
  }

  validateForm = () => {
    const { days, dbs, form, schedules } = this.state;

    return !!form.name &&
      !!form.host &&
      !!form.port &&
      !!form.username &&
      !!form.keep &&
      !!form.time &&
      dbs.includes(form.db) &&
      schedules.includes(form.schedule) &&
      (form.schedule === 'weekly' ? days.includes(form.day) : true);
  }

  render() {
    const { days, dbs, form, schedules, isFormValid } = this.state;

    return (
      <div className="column is-two-fifths">
        <form onSubmit={this.handleSubmit}>
          <div className="field">
            <label className="label">Database type</label>
            <div className="select">
              <select name="db" onChange={this.handleChange} value={form.db}>
                {dbs.map(x => <option value={x} key={x}>{x}</option>)}
              </select>
            </div>
          </div>
          <div className="columns">
            <div className="column">
              <div className="field">
                <label className="label">Database host</label>
                <div className="control">
                  <input
                    className="input"
                    placeholder="Database host"
                    type="text"
                    name="host"
                    onChange={this.handleChange}
                    value={form.host}
                    required
                  />
                </div>
              </div>
            </div>
            <div className="column">
              <div className="field">
                <label className="label">Database port</label>
                <div className="control">
                  <input
                    className="input"
                    type="number"
                    name="port"
                    onChange={this.handleChange}
                    value={form.port}
                    required
                  />
                </div>
              </div>
            </div>
          </div>
          <div className="columns">
            <div className="column">
              <div className="field">
                <label className="label">Database username</label>
                <div className="control">
                  <input
                    className="input"
                    type="text"
                    name="username"
                    onChange={this.handleChange}
                    value={form.username}
                    required
                  />
                </div>
              </div>
            </div>
            <div className="column">
              <div className="field">
                <label className="label">Database password</label>
                <div className="control">
                  <input
                    className="input"
                    type="text"
                    name="password"
                    onChange={this.handleChange}
                    value={form.password}
                  />
                </div>
              </div>
            </div>
          </div>
          
          <div className="field">
            <label className="label">Database name</label>
            <div className="control">
              <input
                className="input"
                type="text"
                name="name"
                onChange={this.handleChange}
                value={form.name}
                required
              />
            </div>
          </div>
          <div className="columns">
            <div className="column">
              <div className="field">
                <label className="label">Backup schedule</label>
                <div className="select">
                  <select name="schedule" onChange={this.handleChange} value={form.schedule}>
                    {schedules.map(s => <option value={s} key={s}>{s}</option>)}
                  </select>
                </div>
              </div>
            </div>
            {
              form.schedule === 'weekly' ? 
                (
                  <div className="column">
                    <div className="field">
                      <label className="label">Day</label>
                      <div className="select">
                        <select name="day" onChange={this.handleChange} value={form.day}>
                          {days.map(d => <option value={d} key={d}>{d}</option>)}
                        </select>
                      </div>
                    </div>
                  </div>
                ) : 
                (<p></p>)
            }  
            <div className="column">
              <div className="field">
                <label className="label">Time</label>
                <div className="control">
                  <input
                    className="input"
                    type="text"
                    name="time"
                    onChange={this.handleChange}
                    value={form.time}
                    required
                  />
                </div>
              </div>
            </div>
          </div>
          
          <div className="field">
            <label className="label">Preserve last {form.keep} backups</label>
            <div className="control">
              <input
                className="input"
                type="number"
                name="keep"
                onChange={this.handleChange}
                value={form.keep}
                required
              />
            </div>
          </div>
          <div className="control">
            <button disabled={!isFormValid} type="submit" className="button is-info">
              Create schedule
            </button>
          </div>
        </form>
      </div>
    );
  }
}

export default ScheduleForm;
ScheduleForm.js

In SchedulePage.js, add import ScheduleForm from './ScheduleForm'; to import our form component and change the render function to:

render() {
    return (
      <React.Fragment>
        <ScheduleForm endpoint={this.state.endpoint} />
        <ScheduleTable schedules={this.state.schedules} />
      </React.Fragment>
    );
}

Go ahead and create schedules on the frontend.

You'll notice that created schedules are only visible when the page is reloaded. Let's change that.

Aside: You may have noticed that we can't create schedules with empty passwords. To change that, we change the password field declaration of our model to password = models.CharField(max_length=200, blank=True) and run python3 manage.py makemigrations backend; python3 manage.py migrate to persist our changes to the database. See the changes here.

In ScheduleForm.js:

Change propTypes to:

static propTypes = {
    endpoint: PropTypes.string.isRequired,
    updateSchedule: PropTypes.func.isRequired
};

Change handleSubmit() to:

handleSubmit = (e) => {
    e.preventDefault();
    e.stopPropagation();

    fetch(this.props.endpoint, {
      method: 'post',
      body: JSON.stringify(this.state.form),
      headers: new Headers({ 'Content-Type': 'application/json' })
    })
    .then((response) => {
      if (response.status === 201) {
        this.clearForm();
        return response.json();
      }
    })
    .then(schedule => !!schedule ? this.props.updateSchedule(schedule) : null);
};

In SchedulePage.js:

Modify the ScheduleForm tag in render() to:

<ScheduleForm endpoint={this.state.endpoint} updateSchedule={this.updateSchedule.bind(this)} />

Add an updateSchedule() function:

updateSchedule(schedule) {
    const schedules = [...this.state.schedules, schedule];
    
    this.setState({ schedules });
}

Delete schedules - commit

You may have created some schedules that you'd want to delete, so let's write code to provide that functionality.

Add a deleteSchedule() method to SchedulePage.js:

deleteSchedule(index) {
    if (! confirm('Are you sure?')) return;

    const schedules = this.state.schedules;
    const { id } = schedules[index];

    fetch(`${this.state.endpoint}${id}`, {
      method: 'delete',
      headers: new Headers({ 'Content-Type': 'application/json' })
    })
    .then((response) => {
      if (response.status === 204) {
        delete schedules[index];
  
        this.setState({ schedules });
      }      
    });
}

Change the ScheduleTable's tag in the render() of SchedulePage to:

<ScheduleTable 
          schedules={this.state.schedules} 
          deleteSchedule={this.deleteSchedule.bind(this)} />

In ScheduleTable.js,

Modify propTypes to:

static propTypes = {
    schedules: PropTypes.array.isRequired,
    deleteSchedule: PropTypes.func.isRequired
};

Replace the contents of the tbody section in the render() with:

{this.props.schedules.map((s, i) => (
              <tr key={s.id}>
                <td>{s.name}</td>
                <td>{s.db}</td>
                <td>{s.host}</td>
                <td>{s.schedule === 'weekly' ? `${s.day} @ ${s.time}` : `Daily @ ${s.time}`}</td>
                <td>
                  <div className="buttons are-small">
                    <button className="button is-outlined">View</button>
                    <button className="button is-outlined">Update</button>
                    <button 
                      onClick={(_) => this.props.deleteSchedule(i)} 
                      className="button is-outlined is-danger">
                      Delete
                    </button>
                  </div>
                </td>
              </tr>
            ))}

Update schedules - commit

Let us provide the functionality to update schedules.

In SchedulePage.js,

Add updating to state to keep track of the schedule index we're currently updating:

state = {
    schedules: [],
    updating: -1,
    endpoint: 'api/schedules/'
};

Modify updateSchedule() to handle updates:

updateSchedule(schedule) {
    let { state: { schedules, updating } } = this;

    schedules = updating === -1 ? 
      [...schedules, schedule] : 
      schedules.map(s => s.id === schedule.id ? schedule : s);

    updating = -1;
    
    this.setState({ schedules, updating });
}

Add a method selectForUpdate() that handles update clicks on the schedule table:

selectForUpdate(updating) {
    this.setState({ updating });
}

Update render() to:

render() {
    const { state: { updating, schedules } } = this;

    return (
      <React.Fragment>
        <ScheduleForm 
          key={updating}
          schedule={updating === -1 ? null : schedules[updating]}
          endpoint={this.state.endpoint} 
          updateSchedule={this.updateSchedule.bind(this)} />
        <ScheduleTable 
          schedules={this.state.schedules}
          selectForUpdate={this.selectForUpdate.bind(this)}
          deleteSchedule={this.deleteSchedule.bind(this)} />
      </React.Fragment>
    );
}

In ScheduleTable.js,

Modify propTypes:

static propTypes = {
    schedules: PropTypes.array.isRequired,
    deleteSchedule: PropTypes.func.isRequired,
    selectForUpdate: PropTypes.func.isRequired
};

Modify the update button on the row from <button className="button is-outlined">Update</button> to:

<button 
     onClick={(_) => this.props.selectForUpdate(i)} 
     className="button is-outlined">
     Update
</button>

Modify deleteSchedule() to handle cases where the schedule being updated is deleted:

deleteSchedule(index) {
    if (! confirm('Are you sure?')) return;

    let { state: { endpoint, schedules, updating } } = this;
    const { id } = schedules[index];

    fetch(`${endpoint}${id}`, {
      method: 'delete',
      headers: new Headers({ 'Content-Type': 'application/json' })
    })
    .then((response) => {
      if (response.status === 204) {
        delete schedules[index];

        if (updating === index) updating = -1;
  
        this.setState({ schedules, updating });
      }      
    });
}

In ScheduleForm.js,

Modify propTypes:

static propTypes = {
    endpoint: PropTypes.string.isRequired,
    updateSchedule: PropTypes.func.isRequired,
    schedule: PropTypes.object
};

Modify state:

state = {
    dbs: ['mysql', 'postgresql'],
    schedules: ['daily', 'weekly'],
    days: ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'],
    isFormValid: false,
    action: this.props.schedule ? 'Update' : 'Create',
    form: this.props.schedule ? this.props.schedule : { ...this.defaultForm }
  };

Modify handleSubmit() to support updating schedules via PUT requests:

handleSubmit = (e) => {
    e.preventDefault();
    e.stopPropagation();

    const { state: { form }, props: { endpoint, updateSchedule } } = this;

    fetch(form.id ? `${endpoint}${form.id}` : endpoint, {
      method: form.id ? 'put' : 'post',
      body: JSON.stringify(form),
      headers: new Headers({ 'Content-Type': 'application/json' })
    })
    .then((response) => {
      if (Math.floor(response.status / 100) === 2) {
        this.clearForm();
        return response.json();
      }
    })
    .then(schedule => !!schedule ? updateSchedule(schedule) : null);
};

In the render(), add this to the top:

const { action, days, dbs, form, schedules, isFormValid } = this.state;

and replace the text of the submit button with {action} schedule.

View schedules - commit

First, remove the is-two-fifths class from ScheduleForm.js's enclosing div. Since we'll be adding another column to the right of ScheduleTable, we want to share the screen equally between the 3 components.

Create ./frontend/src/components/Schedule.js and paste in:

import React, { Component } from 'react';
import PropTypes from 'prop-types';

class Schedule extends Component {
  static propTypes = {
    schedule: PropTypes.object.isRequired
  };

  state = {};

  render() {
    const { schedule } = this.props;

    return (
      <div className="column">
        <h2 className="subtitle">
          <strong>{schedule.name}</strong>
        </h2>
        <h4>{schedule.db}://{schedule.host}:{schedule.port}/{schedule.name}</h4>
        <h6>
          Schedule: {schedule.schedule === 'weekly' ? `${schedule.day} @ ${schedule.time}` : `Daily @ ${schedule.time}`}
        </h6>
        <hr/>
      </div>
    );
  }
}

export default Schedule;

In ScheduleTable.js,

We'll add another prop to receive our view handler:

static propTypes = {
    schedules: PropTypes.array.isRequired,
    deleteSchedule: PropTypes.func.isRequired,
    selectForUpdate: PropTypes.func.isRequired,
    view: PropTypes.func.isRequired
};

We'll then add an onClick handler to the view button:

<button 
    onClick={(_) => this.props.view(i)}
    className="button is-outlined">
    View
</button>

In SchedulePage.js,

Import the Schedule component:

import Schedule from './Schedule';

Add a key to our state to keep track of the schedule that is currently being viewed, like we did for updates:

state = {
    schedules: [],
    updating: -1,
    viewing: -1,
    endpoint: 'api/schedules/'
};

Modify deleteSchedule() to remove the currently viewed schedule if it is deleted:

deleteSchedule(index) {
    if (! confirm('Are you sure?')) return;

    let { state: { endpoint, schedules, updating, viewing } } = this;
    const { id } = schedules[index];

    fetch(`${endpoint}${id}`, {
      method: 'delete',
      headers: new Headers({ 'Content-Type': 'application/json' })
    })
    .then((response) => {
      if (response.status === 204) {
        delete schedules[index];

        if (updating === index) updating = -1;
        if (viewing === index) viewing = -1;
  
        this.setState({ schedules, updating, viewing });
      }      
    });
}

Add the view handler:

view(viewing) {
    this.setState({ viewing });
}

Modify render() to render our Schedule component:

render() {
    const { state: { viewing, updating, schedules } } = this;

    return (
      <React.Fragment>
        <ScheduleForm 
          key={updating}
          schedule={updating === -1 ? null : schedules[updating]}
          endpoint={this.state.endpoint} 
          updateSchedule={this.updateSchedule.bind(this)} />
        <ScheduleTable 
          schedules={this.state.schedules}
          selectForUpdate={this.selectForUpdate.bind(this)}
          deleteSchedule={this.deleteSchedule.bind(this)}
          view={this.view.bind(this)} />
        {viewing === -1 ?
          '' :
          <Schedule 
            key={viewing} 
            schedule={schedules[viewing]} />
        }
      </React.Fragment>
    );
}

Authentication - commit

Since this app is intended to be used by a single person, let's take steps to secure and restrict access to our app. We'll be using Django's default admin authentication functionality.

First, create a superuser by running python3 manage.py createsuperuser and following the prompts.

Add LOGIN_URL = '/admin/login' to ./janitor/settings.py and add Django's authentication decorator in ./frontend/views.py. It should look like this:

from django.shortcuts import render
from django.contrib.auth.decorators import login_required

@login_required
def index(request):
  return render(request, 'frontend/index.html')

With these, our app is no longer open to third-party access.

List backups - commit

Starting from the frontend, add an endpoint prop to the Schedule tag in SchedulePage.jsto

<Schedule 
   key={viewing} 
   endpoint={this.state.endpoint}
   schedule={schedules[viewing]} />

We will be fetching backups when the component is mounted with a schedule in Schedule.js and adding a new component to show our existing backups. The updated component is now:

import React, { Component } from 'react';
import PropTypes from 'prop-types';
import ScheduleBackups from './ScheduleBackups';

class Schedule extends Component {
  static propTypes = {
    schedule: PropTypes.object.isRequired,
    endpoint: PropTypes.string.isRequired
  };

  state = {
    backups: []
  };

  deleteBackup(index) {
    if (! confirm('Are you sure?')) return;
    //
  }

  componentDidMount() {
    const { props: { endpoint, schedule } } = this;

    fetch(`${endpoint}${schedule.id}/backups`)
      .then(response => response.status === 200 ? response.json() : [])
      .then(backups => this.setState({ backups }));
  }

  render() {
    const { props: { schedule }, state: { backups } } = this;

    return (
      <div className="column">
        <h2 className="subtitle">
          <strong>{schedule.name}</strong>
        </h2>
        <h4>{schedule.db}://{schedule.host}:{schedule.port}/{schedule.name}</h4>
        <span className="tag is-dark">
          Schedule: {schedule.schedule === 'weekly' ? `${schedule.day} @ ${schedule.time}` : `Daily @ ${schedule.time}`}
        </span>
        <hr/>
        <button className="button is-fullwidth is-primary is-outlined">Backup now</button>
        <ScheduleBackups 
          backups={backups}
          deleteBackup={this.deleteBackup.bind(this)} />
      </div>
    );
  }
}

export default Schedule;

We added a new component ./frontend/src/components/ScheduleBackups.js to display our backups:

import React, { Component } from 'react';
import PropTypes from 'prop-types';

class ScheduleBackups extends Component {
  static propTypes = {
    backups: PropTypes.array.isRequired,
    deleteBackup: PropTypes.func.isRequired
  };

  render() {
    const styles = { marginTop: '30px' };

    return (
      <div className="backups" style={styles}>
        {!this.props.backups.length ?
          <p>No backups available</p> :
          (
            <table className="table is-striped">
              <thead>
                <tr>
                  <th>File</th>
                  <th></th>
                </tr>
              </thead>
              <tbody>
              {this.props.backups.map((b, i) => (
                  <tr key={i}>
                    <td>{b}</td>
                    <td>
                      <div className="buttons are-small">
                        <a target="_blank" href="javascript:;" className="button is-outlined">
                          Download
                        </a>
                        <button
                          onClick={(_) => this.props.deleteBackup(i)}
                          className="button is-outlined is-danger">
                          Delete
                        </button>
                      </div>
                    </td>
                  </tr>
                ))}
              </tbody>
            </table>
          )
        }
      </div>
    );
  }
}

export default ScheduleBackups;

Let's now provide the backend support for this feature.

Add a new setting variable FILES_ROOT to hold the location where we store our backups:

FILES_ROOT = os.path.join(BASE_DIR, 'backups')

Each schedule will be storing its backups in a sub-directory of FILES_ROOT. Let's add a methods to our Schedule model to provide the schedules' backup storage location and give us a list of all available backups:

from os import path, listdir
...

def backup_path(self):
    return path.join(settings.FILES_ROOT, str(self.id))
    
def list_backups(self):
    return listdir(self.backup_path())

In ./backend/urls.py, add a path to provide a list of a schedule's backups:

from django.urls import path
from .views import ScheduleView, backups

urlpatterns = [
    ...,
    path('schedules/<int:id>/backups', backups)    
]

In ./backend/views.py, add a handler for our path:

from os import path
from django.shortcuts import render
from django.contrib.auth.decorators import login_required
from rest_framework.generics import ListCreateAPIView, UpdateAPIView, DestroyAPIView
from rest_framework.decorators import api_view
from rest_framework.response import Response
from rest_framework import status

from backend.models import Schedule
from backend.serializers import ScheduleSerializer

...

@login_required
@api_view(['GET'])
def backups(request, id):
  try:
    schedule = Schedule.objects.get(pk=id)
  except Schedule.DoesNotExist:
    return Response(status=status.HTTP_404_NOT_FOUND)

  return Response([] if not path.exists(schedule.backup_path()) else schedule.list_backups())

Create backups - commit

Other than the scheduled backups, we might want to create immediate backups. Let's add code to do that. There's a lot of code in this section, do try to keep up as best you can.

This time we'll start from the backend. First a couple of methods in the Schedule model to help with creating backups, as well as an updated list_backups():

from time import gmtime, strftime

...

def list_backups(self):
    backups = listdir(self.backup_path()) if path.exists(self.backup_path()) else []

    backups.sort(key=lambda b : path.getmtime(path.join(self.backup_path(), b)))
    return backups

def new_file_path(self):
    return path.join(self.backup_path(), self.new_file_name())

  def new_file_name(self):
    return '{}_{}.sql'.format(self.name, strftime("%Y-%m-%d_%H:%M:%S", gmtime()))

  def backup_command(self):
    if self.db == 'mysql':
      return 'mysqldump -h {} --port={} -u {} -p{} {} > {}'.format(
        '127.0.0.1' if self.host == 'localhost' else self.host,
        self.port,
        self.username, 
        self.password, 
        self.name,
        self.new_file_path())
        
    elif self.db == 'postgresql':
      return 'PGPASSWORD="{}" pg_dump -U {} -h {} --port={} {} > {}'.format(
        self.password,
        self.username,
        self.host,
        self.port,
        self.name,
        self.new_file_path())

    else:
      return 'echo 0'
Note: I've only made sure these commands work in a Unix-like environment. If you're on Windows, you may have to modify the commands for your environment.

In ./backend/urls.py add path('schedules/<int:id>/backups/create', backup) to urlpatterns and don't forget to import backup at the top.

Create a new file ./backend/utils.py where we'll put our backup code:

import errno
import logging
from os import path, makedirs, remove, rmdir, listdir
import sched, time, datetime
from django.conf import settings
from .models import Schedule

logger = logging.getLogger(__name__)

def create_dir(dirname):
  if not path.exists(dirname):
    try:
      makedirs(dirname)
    except OSError as e:
      if e.errno != errno.EEXIST:
        raise

def run_backups(schedules):

  from subprocess import run

  for schedule in schedules:
    logger.info('Running scheduled backup for {}'.format(schedule))

    create_dir(schedule.backup_path())
    run(schedule.backup_command(), shell=True)
    remove_expired_backups(schedule)


def removed_deleted_backups():
  logger.info('Remove folders for non-existent schedules')

  ids = list(map(lambda s: s.id, Schedule.objects.all()))

  for backup_dir in listdir(settings.FILES_ROOT):
    if int(backup_dir) not in ids:
      rmdir(path.join(settings.FILES_ROOT, backup_dir))


def remove_expired_backups(schedule):
  backups = schedule.list_backups()

  if len(backups) is 0:
    return
  
  backups.sort(key=lambda b : path.getmtime(path.join(schedule.backup_path(), b)))
  
  num_to_delete = len(backups) - schedule.keep

  if num_to_delete > 0:
    for b in backups[:num_to_delete]:
      try:
        remove(path.join(schedule.backup_path(), b))
      except OSError:
        pass

run_backups is the function that executes the backup command. After creating a new backup, we do some house keeping in remove_expired_backups() to delete any stale backups for that schedule.

In addition to creating a backup() handler we take care of authentication and other things, so here's the full ./backend/views.py at this point:

from os import path
import logging
from django.shortcuts import render
from django.contrib.auth.decorators import login_required
from rest_framework.generics import ListCreateAPIView, UpdateAPIView, DestroyAPIView
from rest_framework.decorators import api_view
from rest_framework.response import Response
from rest_framework import status
from rest_framework.authentication import SessionAuthentication, BasicAuthentication
from rest_framework.decorators import authentication_classes

from backend.models import Schedule
from backend.serializers import ScheduleSerializer
from .utils import run_backups, removed_deleted_backups


class CsrfExemptSessionAuthentication(SessionAuthentication):
  def enforce_csrf(self, request):
    return

class ScheduleView(ListCreateAPIView, UpdateAPIView, DestroyAPIView):
  authentication_classes = [CsrfExemptSessionAuthentication, BasicAuthentication]
  queryset = Schedule.objects.all()
  serializer_class = ScheduleSerializer


@login_required
@api_view(['GET'])
@authentication_classes((CsrfExemptSessionAuthentication, BasicAuthentication))
def backups(request, id):
  try:
    schedule = Schedule.objects.get(pk=id)
  except Schedule.DoesNotExist:
    return Response(status=status.HTTP_404_NOT_FOUND)

  return Response([] if not path.exists(schedule.backup_path()) else schedule.list_backups())

@login_required
@api_view(['POST'])
@authentication_classes((CsrfExemptSessionAuthentication, BasicAuthentication))
def backup(request, id):
  logger = logging.getLogger(__name__)

  try:
    schedule = Schedule.objects.get(pk=id)

    existing_backups = schedule.list_backups()
    
    from threading import Thread
    thread = Thread(target=run_backups, args=([schedule],), daemon=True)
    thread.start()
    logger.info('Running an immediate backup for {}'.format(schedule))
    thread.join()

    updated_backups = schedule.list_backups()

    if len(existing_backups) is 0:
      file_name = 'ERR' if len(updated_backups) is 0 else updated_backups[-1]
    else:
      file_name = 'ERR' if existing_backups[-1] == updated_backups[-1] else updated_backups[-1]

  except Schedule.DoesNotExist:
    file_name = 'ERR'

  if file_name is not 'ERR':
    return Response({ 'backup': file_name }) 
  else:
    return Response(status=status.HTTP_500_INTERNAL_SERVER_ERROR)

That's all that's needed in the backend. Over on the frontend in Schedule.js, we update our backup button to:

<button
          onClick={(_) => this.backupNow()}
          disabled={this.state.busy}
          className="button is-fullwidth is-primary is-outlined">
          {this.state.busy ? 'Busy...' : 'Backup now'}
        </button>

Add a busy property to our state:

state = {
    backups: [],
    busy: false
  };

And finally implement the backupNow() handler:

backupNow() {
    if (this.state.busy) return;

    this.setState({ busy: true });

    const { props: { endpoint, schedule } } = this;

    fetch(`${endpoint}${schedule.id}/backups/create`, {
        method: 'post',
        headers: new Headers({ 'Content-Type': 'application/json' })
      })
      .then(response => response.status === 200 ? response.json() : null)
      .then((backup) => {
        if (backup) {
          let backups = this.state.backups;
          backups.push(backup.backup);

          this.setState({ backups });
        } else {
          alert('Could not create backup');
        }
      })
      .finally(() => this.setState({ busy: false }));
  }

Go ahead and create backups. Of course, it will only work if your schedules have the correct information and the appropriate database servers are installed.

Delete backups - commit

Thanks to the preparatory work we did previously on the frontend, all we need to do now is complete the implementation of deleteBackup():

deleteBackup(index) {
    if (! confirm('Are you sure?')) return;
    
    let { props: { endpoint, schedule: { id } }, state: { backups } } = this;

    fetch(`${endpoint}${id}/backups/${backups[index]}/delete`, {
      method: 'delete',
      headers: new Headers({ 'Content-Type': 'application/json' })
    })
    .then((response) => {
      if (response.status === 204) {
        delete backups[index];
  
        this.setState({ backups });
      }      
    });
  }

For the backend portion, we first add the path path('schedules/<int:id>/backups/<str:file>/delete', delete_backup) to urlpatterns in ./backend/urls.py (remember to include the import for delete_backup).

In ./backend/views.py, add the following:

from os import path, remove
from django.conf import settings

...


@login_required
@api_view(['DELETE'])
@authentication_classes((CsrfExemptSessionAuthentication, BasicAuthentication))
def delete_backup(request, id, file):
  remove(path.join(settings.FILES_ROOT, str(id), file))
  
  return Response(status=status.HTTP_204_NO_CONTENT) 

Now, we can delete unwanted backups.

Download backups - commit

In ./backend/urls.py, add path('schedules/<int:id>/backups/<str:file>', download) to urlpatterns and add the import for the download view.

In ./backend/views.py, add:

from django.views.decorators.http import require_GET
from django.http import Http404, HttpResponse

...

@login_required
@require_GET
def download(request, id, file):
  file_path = path.join(settings.FILES_ROOT, str(id), file)
  if not path.exists(file_path):
    raise Http404
  else:
    with open(file_path, 'rb') as fh:
      response = HttpResponse(fh.read(), content_type='text/plain')
      response['Content-Disposition'] = 'attachment; filename={}'.format(path.basename(file_path))
      return response

On the frontend, add a downloadEndpoint prop to `ScheduleBackups.js`:

static propTypes = {
    backups: PropTypes.array.isRequired,
    downloadEndpoint: PropTypes.string.isRequired,
    deleteBackup: PropTypes.func.isRequired
};

Add a downloadHref() to resolve download links:

downloadHref(file) {
    return this.props.downloadEndpoint.replace('FILE', file);
}

Change the href on the download link to href={this.downloadHref(b)}.

In Schedule.js, update the render() function:

render() {
    const { props: { schedule }, state: { backups, busy, downloadEndpoint } } = this;

    return (
      <div className="column">
        <h2 className="subtitle">
          <strong>{schedule.name}</strong>
        </h2>
        <h4>{schedule.db}://{schedule.host}:{schedule.port}/{schedule.name}</h4>
        <span className="tag is-dark">
          Schedule: {schedule.schedule === 'weekly' ? `${schedule.day} @ ${schedule.time}` : `Daily @ ${schedule.time}`}
        </span>
        <hr/>
        <button
          onClick={(_) => this.backupNow()}
          disabled={busy}
          className="button is-fullwidth is-primary is-outlined">
          {busy ? 'Busy...' : 'Backup now'}
        </button>
        <ScheduleBackups 
          backups={backups}
          downloadEndpoint={downloadEndpoint}
          deleteBackup={this.deleteBackup.bind(this)} />
      </div>
    );
  }

Update the state to:

state = {
    backups: [],
    busy: false,
    downloadEndpoint: `${this.props.endpoint}${this.props.schedule.id}/backups/FILE`
  };

That's all to implement backup downloads. Try downloading a few backups.

Replacing our time input with a time picker in our form - commit

In the rules array of webpack.config.js, add:

{
    test: /\.css$/,
    loader: 'style-loader!css-loader'
}

Install the loaders and react-flatpickr with npm i -D react-flatpickr style-loader css-loader.

In ScheduleForm.js, import Flatpickr:

import Flatpickr from 'react-flatpickr';
import 'flatpickr/dist/flatpickr.min.css';

Add a handler for the time input:

updateTime = ([date]) => {
    this.setState({ 
      ...this.state, 
      form: { ...this.state.form, time: `${date.getHours()}:${date.getMinutes()}` }, 
      isFormValid: this.validateForm() 
    });
  }

Replace the time input with:

<Flatpickr 
                    data-enable-time
                    required
                    options={{ enableTime: true, noCalendar: true, dateFormat: 'H:i' }}
                    className="input"
                    name="time"
                    value={form.time}
                    onChange={this.updateTime} />

Refactor backend backup code - commit.

Let's refactor our backup code to make scheduling easier. Edit run_backups to take one schedule instead of a list of schedules. Rename run_backups to run_backup and make its argument schedule instead of schedules:

def run_backup(schedule):
  from subprocess import run

  logger.info('Running scheduled backup for {}'.format(schedule))

  create_dir(schedule.backup_path())
  run(schedule.backup_command(), shell=True)
  remove_expired_backups(schedule)

Then add a new function run_backups that takes a list of schedules and calls run_backup for each of them in a process:

def run_backups(schedules):
  from multiprocessing import Process

  processes = []

  for schedule in schedules:
    process = Process(target=run_backup, args=(schedule,))
    processes.append(process)
    process.start()

  for process in processes:
    process.join()

Change the backup code our backup handler in ./backend/views.py to:

    ...
    schedule = Schedule.objects.get(pk=id)

    logger.info('Running an immediate backup for {}'.format(schedule))

    existing_backups = schedule.list_backups()
    run_backups([schedule])
    updated_backups = schedule.list_backups()
    ...

Now, we have our backup implementation detail nicely contained.

Refactor frontend backup list - commit

If you've created many backups you'll notice that while expired backups may be deleted on the backend, they'll still appear in the frontend until the page is refreshed and downloading would be broken for those backups as they're no longer available. To fix that, we'll have our backup API call return the list of available backups instead of the most recent.

Our backup handler now becomes:

def backup(request, id):
  logger = logging.getLogger(__name__)

  try:
    schedule = Schedule.objects.get(pk=id)

    logger.info('Running an immediate backup for {}'.format(schedule))

    run_backups([schedule])

    return Response(schedule.list_backups())

  except Schedule.DoesNotExist:
    return Response(status=status.HTTP_500_INTERNAL_SERVER_ERROR)

In our Schedule.js component, the second then piece of code is updated to:

...
.then((backups) => {
        if (backups) {
          this.setState({ backups });
        } else {
          alert('Could not create backup');
        }
})
...

Now, our backup lists always mirrors the available backups on the backend.

Refactoring MySQL backup command - commit

If you've been running backups for MySQL databases, you'll notice this warning in the Django logs:

mysqldump: [Warning] Using a password on the command line interface can be insecure.

To make that warning go away, let's refactor our backup command for MySQL to pass the password as an environment variable:

...
      return 'MYSQL_PWD="{}" mysqldump -h {} --port={} -u {} {} > {}'.format(
        self.password,
        '127.0.0.1' if self.host == 'localhost' else self.host,
        self.port,
        self.username,
        self.name,
        self.new_file_path())

...

Fix key clashes - commit

If you try to view and update the same schedule, React will warn in the console that both keys are the same. Solving this problem is easy - we use string keys with different prefixes.

In SchedulePage.js, we change ScheduleForm's key to

key={`f-${updating}`} 

and Schedule's key to

key={`s-${viewing}`} 

Ensure time string always has 5 characters - commit

Refactor ScheduleForm.js's updateTime() to:

  updateTime = ([date]) => {
    let minutes = date.getMinutes();
    if (minutes < 9) minutes = `0${minutes}`;

    this.setState({
      ...this.state,
      form: { ...this.state.form, time: `${date.getHours()}:${minutes}` },
      isFormValid: this.validateForm()
    });
  }

Store backups using local time - commit

You would have noticed that our backup names use UTC time. Let us replace that with local time. In ./backend/models.py, remove from time import gmtime, strftime and update new_file_name() to:

  def new_file_name(self):
    from pytz import timezone
    from datetime import datetime
    return '{}_{}.sql'.format(self.name, datetime.now(timezone('Africa/Lagos')).strftime('%Y-%m-%d_%H:%M:%S'))

Install pytz if it doesn't already exist in your environment using pip install pytz.

Running scheduled backups in the background - commit

The standard way to run our backup code periodically would be to use Celery. But that would require the use of Redis or another queue broker like RabbitMQ or Kafka. That's an overkill for simple, low-traffic cases, but it's left as an exercise to you if you want to explore that approach.

In our case, we'll just check for eligible backups every minute and run those. We'll also check for and delete backups for schedules that have been deleted.

There are many ways to run code periodically in Python. One way using threading.Event is:

import threading, time
def foo():
  time.sleep(4)
  print time.ctime()
  
WAIT_TIME_SECONDS = 10
nextPeriod = WAIT_TIME_SECONDS
ticker = threading.Event()
nextPeriod = 0
nextTime = time.time()
while not ticker.wait(nextPeriod):
  foo()
  nextTime = nextTime + WAIT_TIME_SECONDS #next execution time
  nextPeriod = nextTime - time.time()     #next execution period

This method accounts for the execution time of the target code and so avoids drift.

We're going to use my fork of a nice library I came across to handle backup scheduling (the original library does not account for the job run time). While the library can be installed with pip install timeloop, that's not the case for my fork so we'll install directly from GitHub.

To install, run

pip3 install --upgrade https://github.com/ukchukx/timeloop/zipball/master

--upgrade will ensure our fork replaces the original if it is already installed.

Add a function in ./backend/utils.pyto fetch eligible schedules and pass them on to be backed up:

def run_eligible_backups():
  import calendar
  from pytz import timezone

  now = datetime.datetime.now(timezone('Africa/Lagos'))
  day_string = calendar.day_name[now.weekday()]
  time_string = '{}:{}'.format(now.hour, now.minute if now.minute > 9 else '0{}'.format(now.minute))

  logger.info('Look for and run eligible backups at {}'.format(now))

  # Filter out weekly schedules that aren't due today
  schedules = filter(lambda s : True if s.schedule == 'daily' else s.day == day_string, Schedule.objects.all())
  # Remove schedules that are not due this minute
  schedules = filter(lambda s : s.time == time_string, schedules)
  run_backups(list(schedules))

Then, we need to create a worker that will execute this every minute. Since we need to register our worker only once when the app starts up, we'll put it in ./backend/urls.py as Django only executes this file once during route registration:

from datetime import timedelta
from timeloop import Timeloop
from .utils import remove_deleted_backups, run_eligible_backups

...

worker = Timeloop()

@worker.job(interval=timedelta(seconds=60))
def scheduled_tasks():
  remove_deleted_backups()
  run_eligible_backups()

worker.start(block=True)

If you run into issues running with block=True, use block=False.

That's all, folks! Of course, the UI could use some work, but I'm not a designer so there's that.

I decided that it would be better to choose from the available databases on the server than letting users enter database names. You can check out the commit.

Beyond these, there are things I need for my deployment like logging, a helpful Readme file, Docker configuration and CI/CD setup.