It would be great to have the GitHub Actions workflow run tests against multiple PostgreSQL versions to catch this kind of thing in the future https://github.com/simonw/django-sql-dashboard/blob/main/.github/workflows/test.yml

Originally posted by @simonw in https://github.com/simonw/django-sql-dashboard/issues/138#issuecomment-873649061

Clicking this link:

Links to this SQL which throws an error:

select id, name, notes, disabled, previous_names, slug, group from availability_tag

Error:

syntax error at or near "group" LINE 1: ... id, name, notes, disabled, previous_names, slug, group from... ^

This attempts to add support for contributing to the project using Docker Compose, as mentioned in https://github.com/simonw/django-sql-dashboard/issues/120#issuecomment-859152991. My hope is that this will make it easier for contributors (or at least, ones familiar with Docker Compose) to get up and running.

Specifically, it does the following:

• Runs test_project interactively on port 8000
• Runs the Sphinx auto-reloading server on port 8001
• Runs a Postgres database that test_project uses

Instructions

Note: These instructions are now out of date; see this PR's contributing.md for up-to-date instructions.

Setting up the Docker Compose environment can be accomplished with:

docker-compose build

# Wait a few seconds after running this, to give Postgres time
# to create the initial account and database.
docker-compose up -d db

docker-compose run app python manage.py migrate
docker-compose run app python manage.py createsuperuser

Once you've done that, you can run:

docker-compose up

This will start up both the test project and the Sphinx documentation server. If you only want to start one of them, you can use docker-compose up app to start up only the test project, or docker-compose up docs to start up only the documentation server.

You will probably want to visit http://localhost:8000/admin/ to log in as your newly-created superuser, and then visit http://localhost:8000/dashboard/ to tinker with the dashboard UI.

If you want to run the test suite, you can run:

docker-compose run app pytest

To do

• [x] Figure out if this is actually something @simonw wants merged into the codebase (it's fine if not; since this PR solely adds files to the repo and doesn't change existing files, I can always use this stuff separately for my personal use)
• [x] Add documentation about all this to contributing.md.
• [x] See if we can automate the initial manage.py migrate.
• [x] See if we can dynamically use the UID of the current user rather than hard-coding it to 1000--or at least, provide a way to specify a custom value for UID/GID.

For any given SQL query the ability to export the entire resultset as CSV would be incredibly useful.

It could be expensive, so we would want to restrict it to only specific trusted users.

Related to #16. I want to encourage using a separate "dashboard" database alias which is configured something like this:

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql_psycopg2",
        "NAME": "mydb",
    },
    "dashboard": {
        "ENGINE": "django.db.backends.postgresql_psycopg2",
        "NAME": "mydb",
        "OPTIONS": {"options": "-c default_transaction_read_only=on -c statement_timeout=100"},
    },
}

I want to write the tests against this - but I'm running into some trouble because the test framework isn't designed to handle read-only database connections like this. I'm seeing errors like this:

Got an error creating the test database: cannot execute CREATE DATABASE in a read-only transaction

URLs to SQL queries currently look like this:

https://simonwillison.net/dashboard/?sql=InNlbGVjdCAlKG5hbWUpcyBhcyBuYW1lLCB0b19jaGFyKGRhdGVfdHJ1bmMoJ21vbnRoJywgY3JlYXRlZCksICdZWVlZLU1NJykgYXMgYmFyX2xhYmVsLFxyXG5jb3VudCgqKSBhcyBiYXJfcXVhbnRpdHkgZnJvbSBibG9nX2VudHJ5IGdyb3VwIGJ5IGJhcl9sYWJlbCBvcmRlciBieSBjb3VudCgqKSBkZXNjIg%3A1lLfRD%3AvFP_m0s3BxRS2qyiWtlMlE1KRa2qoKItofP1vvK7hdY&sql=InNlbGVjdCBib2R5IGFzIGh0bWwgZnJvbSBibG9nX2VudHJ5IGxpbWl0IDEi%3A1lLfRD%3AEK0KOXcGgYdgD4Yzglbodf806GnbmrdtPridp8m0hlY

The problem here is that if the Django secret is reset these become broken links - there's no easy way to recover the SQL.

Instead, if the signatures do not match, how about populating the forms but NOT executing the SQL queries, and showing a warning message at the top of the page?

The ?sql= parameters could then become ?sql=SELECT ...::oKItofP1vvK7hdY where oKItofP1vvK7hdY is a signature but the rest of the query is in plain text.

Hello!

This project is great. I've run into an issue though. I was trying to run this (unfinished) query:

select m.id, m.pub_name, m.pub_id, m.contents_restricted, 
	array_agg(s.updated_at) as sub_dates
from main_manuscript m
left join main_submission s on m.id=s.manuscript_id
group by m.id, m.pub_name, m.pub_id, m.contents_restricted
order by m.id

The problem is that django-sql-dashboard errors out because it doesn't know how to serialize the s.updated_at DateTime field for working with array_agg.

As far as I could tell the only way to fix this would be to fork the codebase? Am I missing something? Thanks!

Sometimes it's useful to run GIANT queries - queries with a huge copy-pasted list of IDs in them for example.

Right now the POST works but the GET redirect may cause an error.

If this happens, you can instead create a saved dashboard and the query will execute fine. Some kind of utility mechanism for spotting this and automatically handling it might be nice.

Borrow the cog action menu design from Datasette. Actions can include:

• Count values (the existing "count" link)
• Sum / Avg / etc (for columns that are numbers)
• Show not-null values

This is currently only possible in the Django Admin. This will also implement edit permissions, taking over from #27.

Still todo:

• [x] Form to save a dashboard as a saved dashboard
• [x] If dashboard reloads with form errors, scroll down to the form
• [x] "Edit dashboard" link that links to the Django admin
• [x] Custom Django admin code to respect edit policies
• [x] Saved dashboard pages should show their visibility and edit policies

In particular, I'm linking out to external sites in my dashboard descriptions, so it would be great to have those hyperlinked (currently the viewer has to manually copy/paste the URL, which is shown as plain text). Since the package already has both markdown and bleach as dependencies, it doesn't seem like this should be technically complex, although perhaps making it backwards-compatible might be non-trivial...

Anyways, happy to discuss this further and start a PR if it's deemed a good idea.

Hi, thank you for this project!

While starting to run the dashboard interactively using the command docker-compose up I got an error Step 3/11 : COPY setup.py README.md . When using COPY with more than one source file, the destination must be a directory and end with a / The problem was fixed by adding a slash after the final dot in the Dockerfile, just as the error message tells, like so: COPY setup.py README.md ./

I'm running Ubuntu 22.04.1 LTS. Docker version 20.10.18, build b40c2f6 docker-compose version 1.29.2, build unknown

Not sure if this pushes things a bit too far, but I thought it would be nice to be able to have a table on one dashboard, where each row displays a link that would take the user to a different dashboard that displays details about that row (e.g. by passing the id as a parameter for the other dashboard). This could be done e.g. by allowing to render individual cells as markdown or html. Essentially s.th. like this Not sure how difficult that would be though.

Many thanks for this great package!

When creating a dashboard with multiple queries and charts, I'd like to be able to give all or some of them (especially the charts) a title so others can understand what the graph is showing without having to look at the SQL query. So what I ended up doing is having additional queries in between charts such as select '## Users per day' as markdown. It kinda does the job, but it ends up looking a bit weird as the title is then quite far away from the chart itself.

A possible solution could be having an optional title field on the DashboardQuery object. What do you think about that?

I'm trying to explore the functionality of the project for exploring a Postgres database, however I'm encountering the following exception immediately after installation. Maybe I am missing a configuration step? Any help is appreciated.

Internal Server Error: /dashboard/
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/dist-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.UndefinedTable: relation "django_sql_dashboard_dashboard" does not exist
LINE 1: ...id", "auth_group"."name", T6."id", T6."name" FROM "django_sq...
                                                             ^


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/local/lib/python3.8/dist-packages/django/core/handlers/exception.py", line 55, in inner
    response = get_response(request)
  File "/usr/local/lib/python3.8/dist-packages/django/core/handlers/base.py", line 197, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "/usr/local/lib/python3.8/dist-packages/django/contrib/auth/decorators.py", line 23, in _wrapped_view
    return view_func(request, *args, **kwargs)
  File "/usr/local/lib/python3.8/dist-packages/django_sql_dashboard/views.py", line 121, in dashboard_index
    return _dashboard_index(
  File "/usr/local/lib/python3.8/dist-packages/django_sql_dashboard/views.py", line 325, in _dashboard_index
    saved_dashboards = [
  File "/usr/local/lib/python3.8/dist-packages/django/db/models/query.py", line 320, in __iter__
    self._fetch_all()
  File "/usr/local/lib/python3.8/dist-packages/django/db/models/query.py", line 1507, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/usr/local/lib/python3.8/dist-packages/django/db/models/query.py", line 57, in __iter__
    results = compiler.execute_sql(
  File "/usr/local/lib/python3.8/dist-packages/django/db/models/sql/compiler.py", line 1361, in execute_sql
    cursor.execute(sql, params)
  File "/usr/local/lib/python3.8/dist-packages/django/db/backends/utils.py", line 103, in execute
    return super().execute(sql, params)
  File "/usr/local/lib/python3.8/dist-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
  File "/usr/local/lib/python3.8/dist-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/usr/local/lib/python3.8/dist-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.8/dist-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/usr/local/lib/python3.8/dist-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: relation "django_sql_dashboard_dashboard" does not exist
LINE 1: ...id", "auth_group"."name", T6."id", T6."name" FROM "django_sq...
                                                             ^

Passing non-string parameters and default values can be achieved today using something like:

select *
where grade >= cast(%(grade)s as integer)
and is_allowed = cast(coalesce(nullif(%(is_allowed)s,''), 'true') as boolean)

But this is not very readable. Would be nice to support some other patterns, for example:

select *
where grade >= %(grade)d
and is_allowed = %(is_allowed:true)b

I know that psycopg2 supports only string parameters, but it does not seem too difficult to manually handle simple cases (like numbers and booleans) while still caring about SQL injections.

For now, I've proposed pull request #148 to refactor the named-parameter feature within the library, so that developpers can easily extend it according to their needs. That would be usefull in any case.

I think it would also be usefull that simple cases (like those mentionned above) be part of the library. What do you think?