How to add SQL editor to your django admin.

Overview

Hey there! 😎 In this tutorial, you will learn how to add a SQL Editor 🌈 to your django admin interface having read-only access to the database. You can find the source code on this github repo. Let's jump right in, it's gonna be a lot of fun!

Introduction
About Codemirror
Editor theming and addons
Create a template
Send Query over API call
Execute your SQL
Conclusion
Resources

Introduction

Before creating a SQL editor you can argue that why the hell do we even need a SQL editor in django admin as we have all the features of django therein already. Having an admin interface with filters and search is not enough when you are working on a real-world project.

As it happens that are a lot of cases when you wish you had a SQL editor there in the admin interface then it would have saved you a lot of effort to get short insights from DB directly instead of using some BI tool. Having a SQL editor with read-only access (SELECT queries) can help you to find what you need from your DB in no time and it would be just a SQL query away.

About Codemirror

Codemirror is a web-based editor that is easy to configure and supports all sorts of programming languages. There is another popular option ace c9 which is also good and can be used for this tutorial. But we are going to stick with codemirror for our tutorial and you can find the link to their website in the resources section below.

Editor theming and addons

Codmirror is highly customizable and it provides a lot of themes to choose from and a lot of plugins to add to your editor. Let's see how to configure the editor step by step

Step1:- Download the codemirror project

It doesn't come as a pip or npm package so you have to download the whole project to your computer in a folder separate from your django application. The whole project is divided into lots of folders inside which you can find the addon or theme of your choice. We won't be adding all those to our project.

Step2:- Select your theme

Go inside the codemirror folder and you will find a folder named theme where you can find all sorts of theming options for your editor in form of CSS file! Just grab that file and add it inside static/yourAppName/css folder of your django application. For this tutorial, I have used dracula.css.

This was easy, right! But wait you also need one more file to get it all working codemirror.css which you can find inside the lib folder. And this is all to setup the styles of your editor.

Step3:- Select plugins and addons

All the files specified below go inside static/yourAppName/js folder in your django application.

First, you have to add codemirror.js file as this is the base javascript file for your editor.

Then add the sql.js which you can find inside mode/sql folder.
Then add these files matchbrackets.js , closebrackets.js , closetag.js which you can find inside addon/edit folder.
Add active-line.js from addon/selection folder and then add comment.js file from addon/comment folder.

All these addons will make your SQL editor easier to write your queries. You can surely explore more addons as there are many which can enhance the user experience.

After following all the above steps your static folder should look something like this

static
    - yourAppName
        - css
            - codemirror.css
            - dracula.css
        - js
            - active-line.js
            - closebrackets.js
            - closetag.js
            - codemirror.js
            - comment.js
            - matchbrackets.js
            - sql.js

Create a template

Now that you have selected theme and addons, its time to create a HTML template to display your SQL editor. Add the following file inside templates/admin folder

live_editor.html

{% extends 'admin/base_site.html' %} {% load static %} {% block content %}
<html lang="en">
    <head>
        <!-- load codemirror base javascript file and jquery to make http calls -->
        <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
        <script src="{% static 'starwars/js/codemirror.js' %}"></script>
        <!-- load codemirror theme and some custom css-->
        <link rel="stylesheet" href="{% static 'starwars/css/style.css' %}" />
        <link href="{% static 'starwars/css/codemirror.css' %}" type="text/css" rel="stylesheet" />
        <link href="{% static 'starwars/css/dracula.css' %}" rel="stylesheet" />
        <!-- load remaining addons for SQL editor -->
        <script src="{% static 'starwars/js/active-line.js' %}"></script>
        <script src="{% static 'starwars/js/matchbrackets.js' %}"></script>
        <script src="{% static 'starwars/js/closebrackets.js' %}"></script>
        <script src="{% static 'starwars/js/comment.js' %}"></script>
        <script src="{% static 'starwars/js/sql.js' %}"></script>
    </head>
    <body>
        <div class="parent_flex_container">
            <span class="page_header_text">SQL code editor</span>
            <button class="uploadBtn" id="submit">Run Query</button>
        </div>
        <!-- SQL editor will be a textarea -->
        <form method="POST" action="" id="editor-form" enctype="multipart/form-data">
            {% csrf_token %}
            <textarea id="editor"></textarea>
        </form>
        <br />
        <div class="parent_flex_container">
            <h2>Query results <span id="rowsCount"></span></h2>
            <button class="downloadBtn" id="downloadReport" style="display:none">
                Download Results
            </button>
        </div>
        <table style="display:block;max-height:60vh;overflow-x: scroll;white-space: nowrap;"></table>
        <script>
            // your javascript logic will go here
        </script>
    </body>
</html>
{% endblock %}

After adding the above html, you should be able to see this at localhost:8000/admin/live-editor

Send Query over API call

After adding SQL editor to the admin we want to send the SQL query to the python to be executed on the Database. So we will use jquery to make an API call. Let's see the process:-

live_editor.html

// your html code
<table style="display:block;max-height:60vh;overflow-x: scroll;white-space: nowrap;"></table>
{{endpoint|json_script:"endpoint"}}
<script>
    $(document).ready(() => {
        let endpoint = JSON.parse($("#endpoint").text());
        // codemirror editor configuration
        var editor = CodeMirror.fromTextArea(document.getElementById("editor"), {
            mode: "sql",
            theme: "dracula",
            lineNumbers: true,
            styleActiveLine: true,
            matchBrackets: true,
            autoCloseBrackets: { highlightNonMatching: true },
        });
        // detect event whenn user want to comment some code
        document.addEventListener("keydown", function (event) {
            if (event.ctrlKey && event.key === "/") {
                editor.toggleComment();
            }
        });
        // call the api when user clicks on Run Query button
        $("#submit").click((e) => {
            e.preventDefault()
            // make a POST request to django
            $.ajax({
                type: "POST",
                dataType: "json",
                url: endpoint,
                data: {
                    // grab the query written in the editor
                    query: JSON.stringify(editor.getDoc().getValue()),
                    csrfmiddlewaretoken: $("input[name=csrfmiddlewaretoken]").val(),
                    action: "post"
                },
                beforeSend: () => {
                    // clear the output table before each submission
                    $("table th").remove()
                    $("table tr").remove()
                },
                success: (data) => {
                    if (data.error) {
                        $("table").append(`<th>An Error Occurred</th>`)
                        $("table").append(`<tr><td>${data.error}</td></tr>`)
                    }
                    else {
                        $("#rowsCount").text(`: ${data.rows.length} rows returned`)
                        // populate table headers
                        data.columns.forEach((obj) => {
                            $("table").append(`<th>${obj}</th>`);
                        });
                        // populate table rows
                        data.rows.forEach((obj) => {
                            let cols = "";
                            Object.entries(obj).map(([key, val]) => {
                                cols = cols + `<td>${val}</td>`;
                            });
                            $("table").append(`<tr class="child">${cols}</tr>`);
                        });
                    }
                },
                complete: () => {
                    $("#downloadReport").show();
                },
            })
        });
        // handle download report event
        $("#downloadReport").click(() => {
            var html = document.querySelector("table").outerHTML;
            export_table_to_csv(html, "query_results.csv");
        });
        function export_table_to_csv(html, filename) {
            var csv = [];
            var rows = document.querySelectorAll("table tr");

            for (var i = 0; i < rows.length; i++) {
                var row = [];
                var cols = rows[i].querySelectorAll("td, th");

                for (var j = 0; j < cols.length; j++) row.push(cols[j].innerText);

                csv.push(row.join(","));
            }
            download_csv(csv.join("\n"), filename);
        }

        function download_csv(csv, filename) {
            var csvFile;
            var downloadLink;

            csvFile = new Blob([csv], { type: "text/csv" });
            downloadLink = document.createElement("a");
            downloadLink.download = filename;
            downloadLink.href = window.URL.createObjectURL(csvFile);
            downloadLink.style.display = "none";
            document.body.appendChild(downloadLink);
            downloadLink.click();
        }
    });
</script>

Following is a detailed explanation of the code in plain English:-

First, define the configuration of the editor as per the codemirror documentation.
Handle the event where the user wants to comment out some lines of code.
Make a POST request to django with the query via AJAX call when the user hits the Run Query button.
- Here before making the request we are doing some DOM manipulation which clears the table below the editor
- And after the successful response, we will populate the table either with error or the data which has arrived from response.
Lastly, we are giving an option to the user if he wishes to download the tabular response in a csv file.

Execute your SQL

So far we have discussed how to make an API call having the user query to the django on a particular endpoint. Now in this section, we would be looking at how to process user queries and return the response. Also, we want to make sure that the user is only allowed to run SELECT statements and avoid running any other DML queries. Let's see how to achieve that.

Below we have defined a new path in urls.py file to render our editor and handle requests in views.py.

urls.py

from django.contrib import admin
from django.urls import path

from starwars.views import live_sql_editor

urlpatterns = [
    path('admin/live-editor/', live_sql_editor, name="sql_editor"),
    path('admin/', admin.site.urls)
]

Now let's move on to our main logic where we will handle requests made by AJAX.

views.py

import json
import sqlparse

from django.http import HttpResponse
from django.shortcuts import render
from django.db import connection

def live_sql_editor(request):
    if request.method == "POST":
        # read the query from request
        user_query = json.loads(request.POST.get("query"))
        columns = []
        queryset = None
        error_message = None # handling error messages
        if user_query:
            # open a connection to the DB
            with connection.cursor() as cursor:
                try:
                    # only allow SELECT queries to be run. it will also allow (with CTEs)
                    parsed_statement = sqlparse.parse(user_query)
                    for statement in parsed_statement:
                        if statement.get_type() != "SELECT":
                            raise Exception("Invalid query! Only select statements are allowed")
                    # execute SQL with cursor
                    cursor.execute(user_query)
                    columns = [col[0] for col in cursor.description]
                    queryset = cursor.fetchall()
                except Exception as e:
                    error_message = str(e)
        context = {
            "columns": columns,
            "rows": queryset,
            "error": error_message
        }
        return HttpResponse(json.dumps(context), content_type="application/json")
    context = {
        "endpoint": "/admin/live-editor/"
    }
    return render(request, "admin/live_editor.html", context)

Following is a brief explanation of the above code:-

Read the query coming from a request in a variable
Open a connection to the DB with connection.cursor()
Parse the SQL query from the request and check if the query is SELECT only
Execute the query if it's fine and return the resultant queryset of the query

Have a look at the example results of the final version.

Conclusion

In this tutorial, we have seen how we can easily configure a SQL code editor in django admin which only allows users to run read-only queries. This approach can save time when you want to get short insights from your DB in production. Also, this adds a really cool feature for admins who use this admin portal day-to-day.

There are some enhancements that can be done to improve the experience of this feature, for example, the error messages are not very informative which can create friction while running large queries. Another one is, that if it returns large number of rows then it will jank the DOM which again affects the user experience. I will try to address these issues in the future and will also update this blog.

I hope you have learned some new things from this article and if you do then please drop a like and share with your colleagues and friends. See you in the next one 👋!

Resources

Official website of Codemirror.
Download codemirror project here.
Checkout SQLParse documnetation to filter SELECT statements here.