Proposal — Datasette JSON API changes for 1.0

[asg017]

refs #1509

To prepare for Datasette 1.0, we want to standardize and document all the HTTP API endpoints that every Datasette instance offers. This proposal goes over single single standard JSON API endpoint we should implement + document for Datasette 1.0.

In general, there are very few breaking changes:

• The awkward /.json, (ie https://latest.datasette.io/.json) endpoint will be removed.
• The /database/table.json?sql=select... endpoint for getting JSON results of an SQL query would be replaced with /database/-/query , but we will redirect the former to the latter so it shouldn't be "breaking"
• the keys/values in a few seldom-used JSON endpoints will be updated

(I will be editing this top-level comment while the spec changes)

Instance-level endpoints

/.json

Will be deprecated.

/-/instance.json

Info about the entire Datasette instance.
- Instance-level metadata (title, description, etc.)
- A "initial look" at databases, tables, queries for the entire Datasette instance (first 100)

Show interface

interface Instance {

  // only provided when _extra=metadata
  metadata?: {
    title?: string;
    description?: string;
  }
  databases: {
    count: number;
    entries: InstanceDatabaseEntry[];
  }
  tables: {
    count: number;
    entries: InstanceTableEntry[];
  }
  views: {
    count: number;
    entries: InstanceViewEntry[];
  }
  queries: {
    count: number;
    entries: InstanceQueryEntry[];
  }
}

interface InstanceDatabaseEntry {
  database: string;
  name: string;
  // TODO
}
interface InstanceTableEntry {
  database: string;
  name: string;
  // TODO
}
interface InstanceViewEntry {
  database: string;
  name: string;
  // TODO
}
interface InstanceQueryEntry {
  database: string;
  name: string;
  // TODO
}

/-/databases.json

A paginated view of all the databases in the instance

Show interface

interface InstanceDatabases {
  databases: [];
  // only when _extra=metadata
  metadata?: DatabaseMetadata;
  // URL params to next page, if any
  next?: string;
  // full URL to next page, if any
  next_url?: string;
}

interface InstanceDatabase {
  name: string;
}

/-/resources.json

A paginated view of all the tables, views, and queries in each database for the entire instance.

Show interface

interface XXX {
  // URL params to next page, if any
  next?: string;
  // full URL to next page, if any
  next_url?: string;
}

Open question: Since /-/resources is a super-set of tables/views/queries, do we even need them?

These other endpoints would be unaffected:

• /-/versions.json
• /-/plugins.json
• /-/settings.json
• /-/config.json
• /-/threads.json
• /-/actor.json

Database Endpoints

/$DB.json

Returns info about a specific database named $DB for the Datasette instance.

Show interface

interface Database {
  private: boolean;

  // provided only if _extra=metadata
  metadata?: DatabaseMetadata; 

  tables: {
    count: number;
    truncated: boolean;
    
    // first 100 tables
    entries: DatabaseTableEntry[];
  };
  
  views: {
    count: number;
    truncated: boolean;
    
    // first 100 views
    entries: DatabaseViewEntry[];
  };
  
  queries: {
    count: number;
    truncated: boolean;
    
    // first 100 canned queries
    entries: DatabaseQueryEntry[];
  };
}

interface DatabaseTableEntry {
  // TODO
}
interface DatabaseViewEntry {
  // TODO
}
interface DatabaseQueryEntry {
  // TODO
}

/$DB/-/resources.json

Returns "resources" (tables, views, canned queries) for the given database.

interface XXX {
  // URL params to next page, if any
  next?: string;
  // full URL to next page, if any
  next_url?: string;
}

/$DB/-/query.json?sql=...

Perform an SQL query on the specific database and return the results.

interface DatabaseQuery {
  ok: boolean;
  truncated: boolean;
  rows: QueryRow[];
  
  // URL params to next page, if any
  next?: string;
  // full URL to next page, if any
  next_url?: string;
}
interface QueryRow {
  [column_name: string]: any;
};

"Resource" (Table, View, and Query) endpoints

/$DB/$RESOURCE.json

Returns info about the specified resource (table, view, query, etc.).

Show interface

interface Resource {
  ok: boolean;
  name: string;
  resource_type: 'table' | 'view' | 'query';
  database: string; 

  // num of row entries in resource. null when too large to count
  count?: number;
  
  primary_keys: string[];
  primary_key_values: string[];
  
  rows: ResourceRow[];
  
  // URL params to next page of rows, if any
  next?: string;
  // full URL to next page of rows, if any
  next_url?: string;
}

interface ResourceRow {
  [column_name: string]: any;
}

_extra=database_metadata

_labels=on

Row endpoints

Returns info about a specific row, and optionally about the table/database of the row..

/-/$DB/$TABLE/$ID[s].json

interface Row {
  ok: boolean;
  truncated: boolean;
  row: QueryRow;

  // TODO: foreign keys
  // TODO: labels

  // only when _extra=instance_metadata
  instance_metadata?: InstanceMetadata;
  // only when _extra=database_metadata
  database_metadata?: DatabaseMetadata;
  // only when _extra=table_metadata
  table_metadata?: TableMetadata;
}
interface QueryRow {
  [column_name: string]: any;
};

[phildini]

My one and maybe only comment here is that there doesn't seem to be any logic I can determine around when an api starts with /$DB/ and when it starts with /-/ and when it starts with /-/$DB/

This feels like an implementation detail getting hoisted to the interface, and if that could be different it would aid in comprehension and the inevitable countless "arg, right, /-/" that devs are likely to run into.

[simonw]

Datasette's core URL design works like this:

• / - the homepage
• /database - stuff relating to that database
• /database/resource - stuff relating to that table, SQL view or configured canned query (they share a namespace)
• /database/table/rowids - the page for a specific row in a table

The /-/ prefix is then used for other endpoints relating to those concepts.

So we have a family of things under /-/ relating to the instance:

• /-/settings
• /-/databases

Then things under /database/-/ relating to that database:

• /database/-/query
• /database/-/tables

And things under /database/table/-/ relating to that table:

• /database/table/-/insert - from the JSON write API: https://docs.datasette.io/en/latest/json_api.html#the-json-write-api

We may even have /database/table/rowids/-/... endpoints at some point in the future.

[phildini]

Is there a verb or noun that /-/ represents?

When I look at the proposal, how should I "read" /$DB/$RESOURCE.json vs /-/$DB/$TABLE/$ID[s].json?

It seems like it "should" be /$DB/$TABLE/$ID[s].json, so that makes me think I'm misunderstanding the use of /-/.

(if this is an API standard I'm not aware of, happy to read those docs instead, but I couldn't find a reference on the JSON API page of datasette)

[brandonrobertz]

Is there a verb or noun that /-/ represents?

I think of it as "datasette function" as everything that follows a dash isn't a database name or a resource inside the DB, but a method provided by datasette that works on the resource before it.