# Database APIs ### Create a Database Export Job To export a database, create a database export job: `POST https://app.pixiebrix.com/api/databases/records/jobs/` The payload should contain an export job request: ```typescript { // A name/label for the export. Will be included export artifact name name: string, // One or more databases from which to export databases: UUID[], // Export type, defaults to "application/json" media_type?: "application/json" | "application/xlsx" | "text/csv", filters?: { // ISO8601 timestamp start_date?: Timestamp, end_date?: Timestamp, } } ``` PixieBrix will respond with a job: ```json {"id":"6a3a88cc-797d-40c1-82ce-9fd15e0e3d02","status":"STARTED"} ``` Call the `GET https://app.pixiebrix.com/api/databases/records/jobs/:id/` endpoint to fetch the job details. Upon success, the job detail will include a `data` link to the exported data. ### Retrieve Organization Database Records {% hint style="info" %} You can retrieve up to 10,000 records per request via the records endpoint. To bulk export records across one or more databases, [#create-a-database-export-job](#create-a-database-export-job "mention"). {% endhint %} To retrieve organization database records (e.g., for use with a Business Intelligence tool such as Tableau or PowerBI), use the databases and records endpoints: * `GET /api/organizations/:organizationId/databases/`: list the Organization's databases * `GET /api/databases/:databaseId/records/`: lists the contents of a database The records endpoint also supports the `test/csv` content type, see [#content-type](https://docs.pixiebrix.com/making-an-api-request#content-type "mention"). #### Filtering Records The endpoint supports the following *optional* query parameters to filter by record creation date: * `start_date`: the `YYYY-MM-DD` for data to include (inclusive) * `end_date`: the `YYYY-MM-DD` for data to include (inclusive) Both `_date` query parameters are *optional.* For example, to get all records since a date, use the `start_date` query parameter by itself: * `/api/databases/:databaseId/records/?start_date=2022-05-19` The endpoint also supports filtering by a string data value by using a `data__` param: * `data__field_name=value` To filter for null vs. non-null, use the `__isnull` suffix: * `data__field_name__isnull=true` For example: * `data__first_name=david` * Field names are case-sensitive and values should be URL encoded. For example, to filter by “user email”: `data__user%20email%3Ddavid%40pixiebrix.com` ### Retrieve Archived Organization Database Records To retrieve organization database records that have been archived to S3, use `GET /api/organizations/:organizationId/databases/:databaseId/record-archives/`. Note, only version 2.0 of this endpoint is supported (see [#version](https://docs.pixiebrix.com/making-an-api-request#version "mention")) which returns paginated results. The response is a list objects. Each object includes a file which consists of all records that were updated on that particular date in [JSONL format](https://jsonlines.org/): ``` [ {"file": "2023-01-04.jsonl", ...}, {"file": "2023-01-02.jsonl", ...}, {"file": "2023-01-01.jsonl", ...}, ] ``` The list is ordered by date is descending order by default. You can list by date in ascending order using the `ordering=date` query param: ``` # GET /api/organizations/:organizationId/databases/:databaseId/record-archives/?ordering=date [ {"file": "2023-01-01.jsonl", ...}, {"file": "2023-01-02.jsonl", ...}, {"file": "2023-01-04.jsonl", ...}, ] ``` If a date is skipped, like “2023-01-03” is in the example above, then there are no records for that date. Every file is accessible for download for 1 hour. After time expires, you will have to hit the endpoint again to get a fresh S3 link to download it. Note, the archive does NOT reflect all changes to a Record. The archive file for a given date is generated after the date has ended. Therefore, if a Record is written to 100 times on a given date, then the archive will only display the last update to that Record. #### Data Retention Policy Every database has a limit of 500,000 records. Once that limit is exceeded, a nightly cron job will archive all records from the oldest date(s) to S3 until the database’s record count falls back under the limit. Archived records are only accessible through the Database Record Archive API. They are not accessible by the Database Record API described in [#retrieve-organization-database-records](#retrieve-organization-database-records "mention"). ### Database Assets An asset is a digital resource, such as an image, that can be stored, accessed, and used by a system or application. Here is the overall flow for uploading and downloading assets:
#### List Database Assets (Metadata) ``` GET /api/databases/:databaseId/assets/ ``` #### Download a Database Asset An asset must be marked as `is_uploaded=true` to download the asset. Otherwise this endpoint will return a 404 Not Found. ``` GET /api/databases/:databaseId/assets/:assetId ``` #### Retrieve a Database Asset Metadata ``` GET /api/databases/:databaseId/assets/:assetId?meta=true ``` Response body: ``` { "id": ":assetId", "download_url": "https://app.pixiebrix.com/api/databases/:databaseId/assets/:assetId/", "filename": "my-file.png", "is_uploaded": true, "created_at": "2024-11-15T20:42:47.670046Z", "updated_at": "2024-11-15T20:42:48.172576Z" } ``` #### Create a Database Asset ``` POST /api/databases/:databaseId/assets/ ``` Request body: ``` { "filename": "my-file.png" } ``` #### Update a Database Asset This is used to mark an asset as uploaded. ``` PATCH /api/databases/:databaseId/assets/:assetId ``` Request body: ``` { "is_uploaded": true } ```