The Databases API: Platform Saga Part VI

We previously learned about creating database groups, and adding locations to groups to replicate databases automatically. In this chapter, we'll learn how to create and manage databases using Databases API.

Before you continue, make sure you've read the previous chapters in this series:

#The Databases API

The Turso Platform API provides you with a set of endpoints to manage databases within your groups. You can create, delete, create from backup, create a branch database, and much more.

A Database is made up of the following properties:

DbId — The unique ID for the database (created and managed automatically).
Name — The unique name of the database (provided by you).
Hostname — The DNS hostname used to connect to the database.
primaryRegion — The primary location code for the group.
regions — All location codes for the group, including the primary.
group — The group the database belongs to.
is_schema — A boolean indicating if the database is a schema database.
schema — The database ID of the schema database, if is_schema is set.
block_reads — The current status fro blocked reads.
block_writes — The current status for blocked writes.
allow_attach — The current status for allowing the database to be attached to another.
version — The sqld version used by the group for databases.
sleeping — A boolean indicating if the database/group is sleeping.

#Database Types

Turso provides two types of databases, individual and schema databases. An individual database is a standalone database that can be used for any purpose. A schema database is a special database that is used to control the schema of other databases — learn more about Multi-DB Schemas.

#List all Databases

Using the Turso Platform API we can fetch a list of databases from the API:

curl -L https://api.turso.tech/v1/organizations/{organizationName}/databases \
  -H 'Authorization: Bearer TOKEN'

This returns a JSON of all groups for your provided organization:

{
  "databases": [
    {
      "Name": "my-db",
      "DbId": "0eb771dd-6906-11ee-8553-eaa7715aeaf2",
      "Hostname": "[databaseName]-[organizationName].turso.io",
      "block_reads": false,
      "block_writes": false,
      "allow_attach": false,
      "regions": ["lhr", "bos", "nrt"],
      "primaryRegion": "lhr",
      "version": "0.22.22",
      "group": "default",
      "is_schema": false,
      "schema": "",
      "sleeping": true
    }
  ]
}

By default, it will list all databases in all groups... but what if you want to filter by group? Turso let's you do that using a query parameter!

Simply pass the name of your group to the group query parameter:

curl -L https://api.turso.tech/v1/organizations/{organizationName}/databases?group=default \
  -H 'Authorization: Bearer TOKEN'

#Create a Database

You can create hundreds and thousands of databases using the Turso Platform API. To create a database, you'll need to give it a name, and provide the name of the group.

The following reuqest creates a database called my-first-db in the group default:

curl -L -X POST 'https://api.turso.tech/v1/organizations/{organizationName}/databases' \
  -H 'Authorization: Bearer TOKEN' \
  -H 'Content-Type: application/json' \
  -d '{
      "name": "my-first-db",
      "group": "default"
  }'

This will return the newly created group as JSON:

{
  "database": {
    "DbId": "0eb771dd-6906-11ee-8553-eaa7715aeaf2",
    "Hostname": "my-first-db-[organizationName].turso.io",
    "Name": "my-first-db"
  }
}

#Retrieve a Database

To retrieve a specific database, you can fetch it by its name:

curl -L 'https://api.turso.tech/v1/organizations/{organizationName}/databases/my-first-db' \
  -H 'Authorization: Bearer TOKEN'

The response will contain the database as JSON:

{
  "Name": "my-first-db",
  "DbId": "0eb771dd-6906-11ee-8553-eaa7715aeaf2",
  "Hostname": "my-first-db-[organizationName].turso.io",
  "block_reads": false,
  "block_writes": false,
  "allow_attach": false,
  "regions": ["lhr", "bos", "nrt"],
  "primaryRegion": "lhr",
  "version": "0.22.22",
  "group": "default",
  "is_schema": false,
  "schema": "",
  "sleeping": true
}

#Retrieve Database Configuration

Each database contains metadata about its configuration. This includes details about its size limit, whether ATTACH is allowed, and the status of read/write are blocked.

You can fetch the configuration of a database by appending /configuration to the URL above we used when retrieving the database itself:

curl -L -X GET 'https://api.turso.tech/v1/organizations/{organizationName}/databases/{databaseName}/configuration' \
  -H 'Authorization: Bearer TOKEN'

This will return the configuration as JSON:

{
  "size_limit": "10000",
  "allow_attach": true,
  "block_reads": true,
  "block_writes": true
}

The size_limit is the maximum size of the database in bytes.

#Update Database Configuration

You can PATCH the database configuration using the same URL as above for its configuration:

curl -L -X PATCH 'https://api.turso.tech/v1/organizations/{organizationName}/databases/{databaseName}/configuration' \
  -H 'Authorization: Bearer TOKEN' \
  -H 'Content-Type: application/json' \
  -d '{
      "size_limit": "500mb",
      "allow_attach": true
  }'

When setting the size_limit, you can pass the value as units, for example: 500mb, 1gb, etc.

#Delete a Database

You can delete a database by using the DELETE HTTP method:

curl -L -X DELETE 'https://api.turso.tech/v1/organizations/{organizationName}/databases/{databaseName}' \
  -H 'Authorization: Bearer TOKEN'

This will immediately delete the database and all its data.

The response will contain the deleted database name as JSON:

{
  "database": "my-first-db"
}

#Fetch Database Usage

You can fetch the usage and activity of a single database in a given time period by using the /usage endpoint. Usage contains information about the number of rows read, written, and the total size of the database.

You can filter the usage by providing a from and to query parameter using a ISO 8601 date format:

curl -L -X GET 'https://api.turso.tech/v1/organizations/{organizationName}/databases/{databaseName}/usage?from=2024-01-01T00:00:00Z&to=2024-12-01T00:00:00Z' \
  -H 'Authorization: Bearer TOKEN'

This returns your database, including its DbId and total usage:

{
  "database": {
    "uuid": "0eb771dd-6906-11ee-8553-eaa7715aeaf2",
    "total": {
      "rows_read": 0,
      "rows_written": 0,
      "storage_bytes": 8192
    }
  }
}

#Fetch Database Stats

You can fetch the top queries, including the affected rows read and written using the Turso Platform API:

curl -L -X GET 'https://api.turso.tech/v1/organizations/{organizationName}/databases/{databaseName}/stats' \
  -H 'Authorization: Bearer TOKEN'

This returns the top_queries array for the given database as JSON:

{
  "top_queries": [
    {
      "query": "SELECT COUNT(*), CustomerID FROM Orders GROUP BY CustomerID HAVING COUNT(*) > 5;",
      "rows_read": 123,
      "rows_written": 4567
    }
  ]
}

These top queries help better shape your database and optimize its performance. You can get AI Insights on these queries by logging into the Turso Dashboard.

#Create a Database Token

You can create a Database Token using the Turso Platform API:

curl -L -X POST 'https://api.turso.tech/v1/organizations/{organizationName}/databases/{databaseName}/auth/tokens' \
  -H 'Authorization: Bearer TOKEN'

This will return a response that contains the token:

{
  "jwt": "TOKEN"
}

Tokens by default never expire, and cannot be retrieved again.

#Create an expiring access token

You can create a Database Token that expires after a certain amount of time. For example, to expire a token in 2 weeks, 1 day, and 30 minutes, we can pass that as a value to the query string expiration:

curl -L -X POST 'https://api.turso.tech/v1/organizations/{organizationName}/databases/{databaseName}/auth/tokens?expiration=2w1d30m' \
  -H 'Authorization: Bearer TOKEN'

#Create a read-only access token

Tokens by default enable full-access to databases, but you can create a read-only token by passing the query string authorization, and the access type required:

curl -L -X POST 'https://api.turso.tech/v1/organizations/{organizationName}/databases/{databaseName}/auth/tokens?authorization=read-only' \
  -H 'Authorization: Bearer TOKEN'

#Create a token with specific permissions

You can create a Database Token with specific permissions by passing permissions to the body of a request as JSON. This is useful if you're using the ATTACH statement with one database to read from others — learn more.

For example, to create a token that can only read from databases db1, db2, and db3:

curl -L -X POST 'https://api.turso.tech/v1/organizations/{organizationName}/databases/{databaseName}/auth/tokens' \
  -H 'Authorization: Bearer TOKEN' \
  -H 'Content-Type: application/json' \
  -d '{
      "permissions": {
        "read_attach": {
          "databases": ["db1", "db2", "db3"]
        }
      }
  }'

#Create a database from an existing database

You can also create a database from an existing one for use in development — branching, or to restore a previous copy of a database. This is done by passing the seed object in the request body with the type as database:

curl -L -X POST 'https://api.turso.tech/v1/organizations/{organizationName}/databases' \
  -H 'Authorization: Bearer TOKEN' \
  -H 'Content-Type: application/json' \
  -d '{
      "name": "new-database",
      "group": "default",
      "seed": {
        "type": "database",
        "name": "existing-database-name"
      }
  }'

To create a database from a specific point in time, you can pass the timestamp (using an ISO 8601 date format) in the seed object:

curl -L -X POST 'https://api.turso.tech/v1/organizations/{organizationName}/databases' \
  -H 'Authorization: Bearer TOKEN' \
  -H 'Content-Type: application/json' \
  -d '{
      "name": "new-database",
      "group": "default",
      "seed": {
        "type": "database",
        "name": "existing-database-name",
        "timestamp": "2024-01-01T00:00:00Z"
      }
  }'

The timestamp must be within the permitted time for your organization plan — see pricing.

#Upload and create from SQLite dump

You can also upload a SQLite dump that can be used to create a new database from that uploaded dump. Using the multipart/form-data content type, pass the SQLite dump as file:

curl -L -X POST 'https://api.turso.tech/v1/organizations/{organizationName}/databases/dumps' \
  -H 'Authorization: Bearer TOKEN' \
  -F 'file=@"~/Downloads/dump.sql"'

Once uploaded, you will get back the dump_url which we can use to create a database using the same seed object as before, but this time with the type as dump:

curl -L -X POST 'https://api.turso.tech/v1/organizations/{organizationName}/databases' \
  -H 'Authorization: Bearer TOKEN' \
  -H 'Content-Type: application/json' \
  -d '{
      "name": "new-database",
      "group": "default",
      "seed": {
        "type": "dump",
        "url": "DUMP_URL"
      }
  }'

That's it! You know have all of the knowledge required to create organizations, groups, query locations, create and replicate databases within groups globally using the Turso Platform API.