simonw
Proposal — Datasette JSON API changes for 1.0
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;
};
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.
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.
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)
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.
