datasette-edit-schema

Datasette plugin for modifying table schemas

⚠️ The latest alpha release depends on Datasette 1.09a. Use version 0.7.1 with older releases of Datasette.

Features

• Add new columns to a table
• Rename columns in a table
• Modify the type of columns in a table
• Re-order the columns in a table
• Rename a table
• Delete a table
• Change the primary key of a table to another column containing unique values
• Update the foreign key constraints on a table
• Add an index (or unique index) to a column on a table
• Drop an index from a table

Installation

Install this plugin in the same environment as Datasette.

pip install datasette-edit-schema

Usage

Navigate to /-/edit-schema/dbname/tablename on your Datasette instance to edit a specific table.

Use /-/edit-schema/dbname to create a new table in a specific database.

By default only the root actor can access the page - so you'll need to run Datasette with the --root option and click on the link shown in the terminal to sign in and access the page.

Permissions

The edit-schema permission provides access to all functionality.

You can use permission plugins such as datasette-permissions-sql to grant additional access to the write interface.

These permission checks will call the permission_allowed() plugin hook with three arguments:

• action will be the string "edit-schema"
• actor will be the currently authenticated actor - usually a dictionary
• resource will be the string name of the database

You can instead use more finely-grained permissions from the default Datasette permissions collection:

• create-table allows users to create a new table. The resource will be the name of the database.
• drop-table allows users to drop a table. The resource will be a tuple of (database_name, table_name).
• alter-table allows users to alter a table. The resource will be a tuple of (database_name, table_name).

To rename a table a user must have both drop-table permission for that table and create-table permission for that database.

For example, to configure Datasette to allow the user with ID pelican to create, alter and drop tables in the marketing database and to alter just the notes table in the sales database, you could use the following configuration:

databases:
  marketing:
    permissions:
      create-table:
        id: pelican
      drop-table:
        id: pelican
      alter-table:
        id: pelican
  sales:
    tables:
      notes:
        permissions:
          alter-table:
            id: pelican

Events

This plugin fires create-table, alter-table and drop-table events when tables are modified, using the Datasette Events system introduced in Datasette 1.0a8.

Screenshot

Development

To set up this plugin locally, first checkout the code. Then create a new virtual environment:

cd datasette-edit-schema
python3 -mvenv venv
source venv/bin/activate

Or if you are using pipenv:

pipenv shell

Now install the dependencies and tests:

pip install -e '.[test]'

To run the tests:

pytest