Manage databases

A database is a container for SQL objects such as schemas, tables, views, functions, and indexes. In the OptiTech object hierarchy, a database exists within a branch of a project. There is a limit of 500 databases per branch.

If you do not specify your own database name when creating a project, your project's default branch is created with a database called optitechdb, which is owned by your project's default role (see Manage roles for more information). You can create your own databases in a project's default branch or in a child branch.

All databases in OptiTech are created with a public schema. SQL objects are created in the public schema, by default. For more information about the public schema, refer to The Public schema, in the PostgreSQL documentation.

Databases belong to a branch. If you create a child branch, databases from the parent branch are copied to the child branch. For example, if database mydb exists in the parent branch, it will be copied to the child branch. The only time this does not occur is when you create a branch that includes data up to a particular point in time. If a database was created in the parent branch after that point in time, it is not duplicated in the child branch.

OptiTech supports creating and managing databases from the following interfaces:

• OptiTech Console
• OptiTech CLI
• OptiTech API
• SQL

Manage databases in the OptiTech Console

This section describes how to create, view, and delete databases in the OptiTech Console.

The role that creates a database is automatically made the owner of that database. The optitech_superuser role is also granted all privileges on databases created in the OptiTech Console. For information about this role, see The optitech_superuser role.

Create a database

To create a database:

1. Navigate to the OptiTech Console.
2. Select a project.
3. Select Branches from the sidebar.
4. Select the branch where you want to create the database.
5. Select the Roles & Databases tab.
6. Click Add database.
7. Enter a database name, and select a database owner.
8. Click Create.

View databases

To view databases:

1. Navigate to the OptiTech Console.
2. Select a project.
3. Select Branches from the sidebar.
4. Select the branch where you want to view databases.
5. Select the Roles & Databases tab.

Delete a database

Deleting a database is a permanent action. All database objects belonging to the database such as schemas, tables, and roles are also deleted.

To delete a database:

1. Navigate to the OptiTech Console.
2. Select a project.
3. Select Databases from the sidebar.
4. Select a branch to view the databases in the branch.
5. For the database you want to delete, click the delete icon.
6. In the confirmation dialog, click Delete.

Manage databases with the OptiTech CLI

The OptiTech CLI supports creating and deleting databases with optitech databases create --name <database_name> and optitech databases delete <database_name>. If you omit --branch, the CLI uses the project's default branch. For full instructions and options, see OptiTech CLI commands — databases.

Manage databases with the OptiTech API

Database actions performed in the OptiTech Console can also be also performed using the OptiTech API. The following examples demonstrate how to create, view, update, and delete databases using the OptiTech API. For other database-related methods, refer to the OptiTech API Reference.

In OptiTech, a database belongs to a branch, which means that when you create a database, it is created in a branch. Database-related requests are therefore performed using branch API methods.

The jq option specified in each example is an optional third-party tool that formats the JSON response, making it easier to read. For information about this utility, see jq.

Prerequisites

A OptiTech API request requires an API key. For information about obtaining an API key, see Create an API key. In the cURL examples below, $OPTITECH_API_KEY is specified in place of an actual API key, which you must provide when making a OptiTech API request.

Create a database with the API

The following OptiTech API method creates a database. To view the API documentation for this method, refer to the OptiTech API Reference.

The role specified by owner_name is the owner of that database.

POST /projects/{project_id}/branches/{branch_id}/databases

The API method appears as follows when specified in a cURL command. The project_id and branch_id are required parameters, and a database name and owner are required attributes.

curl 'https://console.optitech.com/api/v2/projects/dry-heart-13671059/branches/br-morning-meadow-afu2s1jl/databases' \
  -H 'Accept: application/json' \
  -H "Authorization: Bearer $OPTITECH_API_KEY" \
  -H 'Content-Type: application/json' \
  -d '{
  "database": {
    "name": "mydb",
    "owner_name": "casey"
  }
}' | jq

{
  "database": {
    "id": 2889509,
    "branch_id": "br-morning-meadow-afu2s1jl",
    "name": "mydb",
    "owner_name": "casey",
    "created_at": "2025-08-04T08:14:14Z",
    "updated_at": "2025-08-04T08:14:14Z"
  },
  "operations": [
    {
      "id": "b51c8ece-b78e-49f7-8ec1-78b37cbae3c4",
      "project_id": "dry-heart-13671059",
      "branch_id": "br-morning-meadow-afu2s1jl",
      "endpoint_id": "ep-holy-heart-afbmgcfx",
      "action": "apply_config",
      "status": "running",
      "failures_count": 0,
      "created_at": "2025-08-04T08:14:14Z",
      "updated_at": "2025-08-04T08:14:14Z",
      "total_duration_ms": 0
    }
  ]
}

List databases with the API

The following OptiTech API method lists databases for the specified branch. To view the API documentation for this method, refer to the OptiTech API Reference.

GET /projects/{project_id}/branches/{branch_id}/databases

The API method appears as follows when specified in a cURL command. The project_id and branch_id are required parameters.

curl 'https://console.optitech.com/api/v2/projects/hidden-cell-763301/branches/br-blue-tooth-671580/databases' \
  -H 'Accept: application/json' \
  -H "Authorization: Bearer $OPTITECH_API_KEY" | jq

{
  "databases": [
    {
      "id": 1139149,
      "branch_id": "br-blue-tooth-671580",
      "name": "optitechdb",
      "owner_name": "casey",
      "created_at": "2023-01-04T18:38:23Z",
      "updated_at": "2023-01-04T18:38:23Z"
    },
    {
      "id": 1140822,
      "branch_id": "br-blue-tooth-671580",
      "name": "mydb",
      "owner_name": "casey",
      "created_at": "2023-01-04T21:17:17Z",
      "updated_at": "2023-01-04T21:17:17Z"
    }
  ]
}

Update a database with the API

The following OptiTech API method updates the specified database. To view the API documentation for this method, refer to the OptiTech API Reference.

PATCH /projects/{project_id}/branches/{branch_id}/databases/{database_name}

The API method appears as follows when specified in a cURL command. The project_id and branch_id are required parameters. This example updates the database name value to database1.

curl -X PATCH 'https://console.optitech.com/api/v2/projects/dry-heart-13671059/branches/br-morning-meadow-afu2s1jl/databases/mydb' \
  -H 'Accept: application/json' \
  -H "Authorization: Bearer $OPTITECH_API_KEY" \
  -H 'Content-Type: application/json' \
  -d '{
  "database": {
    "name": "database1"
  }
}' | jq

{
  "database": {
    "id": 2889509,
    "branch_id": "br-morning-meadow-afu2s1jl",
    "name": "database1",
    "owner_name": "casey",
    "created_at": "2025-08-04T08:14:14Z",
    "updated_at": "2025-08-04T08:14:14Z"
  },
  "operations": [
    {
      "id": "2f8c0a6a-33b5-4d56-964b-739614b699c0",
      "project_id": "dry-heart-13671059",
      "branch_id": "br-morning-meadow-afu2s1jl",
      "endpoint_id": "ep-holy-heart-afbmgcfx",
      "action": "apply_config",
      "status": "running",
      "failures_count": 0,
      "created_at": "2025-08-04T08:17:22Z",
      "updated_at": "2025-08-04T08:17:22Z",
      "total_duration_ms": 0
    }
  ]
}

Delete a database with the API

The following OptiTech API method deletes the specified database. To view the API documentation for this method, refer to the OptiTech API Reference.

DELETE /projects/{project_id}/branches/{branch_id}/databases/{database_name}

The API method appears as follows when specified in a cURL command. The project_id, branch_id, and database_name are required parameters.

curl -X 'DELETE' \
  'https://console.optitech.com/api/v2/projects/dry-heart-13671059/branches/br-morning-meadow-afu2s1jl/databases/database1' \
  -H 'Accept: application/json' \
  -H "Authorization: Bearer $OPTITECH_API_KEY" | jq

{
  "database": {
    "id": 2889509,
    "branch_id": "br-morning-meadow-afu2s1jl",
    "name": "database1",
    "owner_name": "casey",
    "created_at": "2025-08-04T08:14:14Z",
    "updated_at": "2025-08-04T08:14:14Z"
  },
  "operations": [
    {
      "id": "4cd4881b-2807-4377-a76d-8e7d39bc5448",
      "project_id": "dry-heart-13671059",
      "branch_id": "br-morning-meadow-afu2s1jl",
      "endpoint_id": "ep-holy-heart-afbmgcfx",
      "action": "apply_config",
      "status": "running",
      "failures_count": 0,
      "created_at": "2025-08-04T08:19:39Z",
      "updated_at": "2025-08-04T08:19:39Z",
      "total_duration_ms": 0
    }
  ]
}

Manage databases with SQL

You can create and manage databases in OptiTech with SQL, as you can with any standalone Postgres installation. To create a database, issue a CREATE DATABASE statement from a client such as psql or from the OptiTech SQL Editor.

CREATE DATABASE testdb;

Most standard Postgres CREATE DATABASE parameters are supported with the exception of TABLESPACE. This parameter requires access to the local file system, which is not permitted in OptiTech.

The role that creates a database is the owner of the database.

For more information about database object privileges in Postgres, see Privileges.

Rename a database with SQL

To rename a database, use ALTER DATABASE. Postgres won't let you rename a database while you're connected to it, so connect to a different database on the same branch first (for example, the default optitechdb), then run:

ALTER DATABASE old_db_name RENAME TO new_db_name;

If open connections to the database block the rename, terminate them first:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'old_db_name'
  AND pid <> pg_backend_pid();

The rename is instant, and data, schemas, tables, roles, and grants are unaffected. The database name is part of every connection string for the database, so update any application, script, or stored secret that references the old name. You can also rename a database with the OptiTech API.

Delete a database with SQL

To delete a database, use DROP DATABASE. As with renaming, connect to a different database on the same branch first, since Postgres won't drop the database you're connected to:

DROP DATABASE old_db_name;

Deletion is permanent. All schemas, tables, indexes, and other objects in the database are dropped along with it. You can also delete a database from the OptiTech Console, the OptiTech CLI, or the OptiTech API.

Transfer database table ownership between roles

In OptiTech, roles created via the Console, CLI, or API are members of optitech_superuser but are not full Postgres superusers. This means you can't directly transfer ownership of a database table from one role to another using ALTER TABLE ... OWNER TO.

The workaround is to introduce a shared group role that both roles belong to. You transfer ownership to the group, then the destination role can claim ownership for itself.

1. Connect as the database owner role and run:

   -- Create a group role with no login
   CREATE ROLE table_owners NOLOGIN;
   
   -- Grant schema access to the group
   GRANT USAGE, CREATE ON SCHEMA public TO table_owners;
   
   -- Add both roles to the group
   GRANT table_owners TO current_owner;
   GRANT table_owners TO new_owner;

   Replace current_owner and new_owner with the actual role names.

2. Still connected as current_owner, transfer the table to the group:

   ALTER TABLE your_table OWNER TO table_owners;

3. Connect as new_owner. Transfer ownership from the group to yourself:

   ALTER TABLE your_table OWNER TO new_owner;

4. Verify ownership:

   \dt your_table

   The Owner column should now show new_owner.

5. Leave the table_owners group role in place if you need to transfer other tables later, or drop it when you're done:

   DROP ROLE table_owners;

   DROP ROLE table_owners works only after that role no longer owns any objects and has no blocking dependencies.

Transfer ownership for multiple objects

The numbered steps above show how to transfer one table with ALTER TABLE ... OWNER TO.

If you need to transfer ownership for everything a role owns in a database, use REASSIGN OWNED instead of running ALTER ... OWNER TO for each table.

REASSIGN OWNED includes tables and other object types owned by the role, such as views, materialized views, sequences, functions, schemas, and types.

Connect as current_owner and move all owned objects to the shared group:

REASSIGN OWNED BY current_owner TO table_owners;

Then connect as new_owner and move those objects from the group to the destination role:

REASSIGN OWNED BY table_owners TO new_owner;

REASSIGN OWNED applies within the current database context. Run it in each database where you need to transfer ownership.

Reserved database names

The following names are reserved and cannot be given to a database:

• postgres
• template0
• template1