Proposal — Datasette JSON API changes for 1.0 #2360
Description
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;
};