Export databases in Django with a simple view

Django November 9th, 2022 3 minute read.

I love SQLite. It's simple and it doesn't come with the additional ~$15/mo cost that follows around all the managed PostgreSQL plans out there. One of the challenges though, is how do I backup my database? There's got to be an easier way than SSH and the command line. My solution was to create a view that downloads or emails the database to you when you enter a specific link into the browser of your choice.

The first version of this downloaded the .sqlite3 database as-is. After some further testing, I found that compressing the database into a ZIP file could decrease the size by as much as 10-15x. So with a bit more complexity, v2 zips up the file before downloading it.

Step 1: Add an entry to urls.py

Pick a URL, that when visited, will return a database file to download.

urls.py

from django.urls import path

from app.views import export_database_view

urlpatterns = [
    path("export-db/", ExportDBView.as_view(), name="export_db"),
]

Step 2: Add the view

I only have 1 user... me, so I use the @login_required() decorator to protect from unauthorized downloads. Other projects may need a different decorator to further restrict access.

views.py

from io import BytesIO
from pathlib import Path
from zipfile import ZIP_DEFLATED, ZipFile

from django.contrib.auth.mixins import UserPassesTestMixin
from django.db import connection
from django.http.response import FileResponse
from django.utils import timezone
from django.views import View


class StaffRequiredMixin(UserPassesTestMixin):
    raise_exception = True

    def test_func(self):
        return self.request.user.is_staff


class ExportDBView(StaffRequiredMixin, View):
    def get(self, request):
        db_path = Path(connection.settings_dict["NAME"])
        timestamp = timezone.localtime().strftime("%Y-%m-%d-%H-%M-%S")
        zip_filename = f"db {timestamp}.zip"

        # Use BytesIO as an in memory zip file for the database
        buffer = BytesIO()
        with ZipFile(buffer, mode="w", compression=ZIP_DEFLATED) as zf:
            zf.write(db_path, db_path.name)  # write db file into zip file
        buffer.seek(0)

        return FileResponse(buffer, as_attachment=True, filename=zip_filename)

Step 3: Tests

import pytest
from django.urls import reverse


@pytest.mark.django_db
def test_regular_user_cant_access_db_export(client):
    url = reverse("export_db")
    response = client.get(url)
    assert response.status_code == 403


@pytest.mark.django_db
def test_admin_user_cant_access_db_export(admin_client):
    url = reverse("export_db")
    with pytest.raises(FileNotFoundError):
        admin_client.get(url)