How to create and manage databases using the Kinsta API

At Kinsta, we help to support thousands of websites of all sizes. One thing that unites them all is the need for a database. Regardless of your site’s size, your database contains its information. This is why we let you create and manage databases using the Kinsta API.

While you can manage your databases using the MyKinsta dashboard, the Kinsta API gives you greater flexibility if you need a programmatic approach. This lets you create, update, and delete those databases and read them to a certain extent. However, the big benefit is integrating the Kinsta API – and, by extension, your database – with your existing tools and workflows.

This post will show you how to create and manage databases using the Kinsta API. It will also cover its benefits, how to access the endpoints, and where this approach will fit into your overall workflow. Let’s start with those benefits and capabilities.

Understanding the capabilities of the Kinsta API

The Kinsta API is the programmatic way to interact with your Kinsta server. We offer many endpoints to cover various ways to manage your sites, such as working with your environments, WordPress themes and plugins, application metrics, and more.

This also extends to your databases through typical CRUD architecture:

• Using a single endpoint, you can create a new database for your site, choose a server location, and set credentials.
• There’s a method to fetch all of the databases for your server using company ID. From there, you can fetch a specific database and its details using its individual ID.
• The API lets you modify the size and display name of your database. This might help in some resource-scaling applications.
• You can remove a database when you no longer need it. This is straightforward and uses a single command.

On the whole, you can leverage these endpoints and begin to streamline your whole database management workflow. Later, we’ll explore this in more detail. First, let’s discuss why you should use the Kinsta API to manage your databases.

The benefits of managing databases through the Kinsta API

Of course, we wouldn’t provide a dedicated API to manage your databases without it giving you a few compelling advantages over the MyKinsta dashboard. Both can fit into your workflow, although the Kinsta API has a few specific ways to unlock new possibilities.

1. You can streamline your current management processes

A primary benefit of managing your databases through the Kinsta API is how you can slim down the steps you would usually take. Using the MyKinsta dashboard, you already have an efficient workflow. For instance, each WordPress website gives you access to your site’s database through phpMyAdmin:

This gives you a familiar interface to make changes. For applications, the Databases screen in MyKinsta will be your port of call:

However, you can skip over all these steps using our API endpoints. This should be a snap to incorporate if you run other programmatic services for your site. The time you save by automating this workflow could also positively impact your efficiency.

2. There are almost limitless automation possibilities available to you

As with all of the Kinsta API’s endpoints, you can automate tasks that would otherwise need you to access the MyKinsta dashboard. We’ll talk more about the integration opportunities later. In short, if you already run existing tools and scripts, automating your database management can become part of that process.

For example, you may want to build in the ability to create a new database whenever you provision a new site. You could set predefined criteria within your code, tools, and scripts to delete databases on an automatic basis. Any workflows that incorporate scaling your resources could also benefit here, given how you can update a database’s resource type.

As with focused streamlining, automating your workflow will further impact the time (and money) you spend on database management.

3. Potential integration with other tools and platforms

APIs, in general, offer an excellent way to integrate with almost every other tool and platform available, even where there is no existing API. You may use services such as Zapier or If This Then That (IFTTT) to connect tools together, which your Kinsta server could be a part of.

In fact, many setups require you to pull in other services for a smooth experience. Consider your continuous integration and deployment (CI/CD) pipelines that use TeamCity, Travis CI, or Buddy. The Kinsta API, as part of your toolchain, can help you create a cohesive workflow from development to production.

Over the next few sections, we’ll cover the different methods and requests the Kinsta API gives you. At the end of the post, you’ll learn how to use the information you fetch within some example scenarios.

What you need to manage your databases with the Kinsta API

Using the databases endpoint is a straightforward process, much like using any other Kinsta API endpoint. We won’t dive into all of the subtleties of each action or workflow here, although later on, we’ll discuss this more.

Accessing the databases endpoint will need you to have some information to hand, central to which is a valid API token. Generating this will let you access the API, which is true for every endpoint. In fact, we have an authentication endpoint for this purpose.

You can create an API key on the Company settings > API Keys screen through the MyKinsta dashboard.

To access most endpoints, you will need your Company ID, too. This is essentially the ID for the server, and you can find it within your browser’s toolbar when logged into the MyKinsta dashboard:

The typical workflow when using any Kinsta API endpoints is to validate your API key, fetch data associated with the Company ID, fetch data associated with the site’s ID, and process that data. This final step requires you to provide the right parameters for the request.

Of course, this will differ depending on what you want to achieve. As we go through managing your databases, we’ll cover those parameters.

Creating a new database using the Kinsta API

Creating your database requires the most work of all the requests we cover here, yet it’s still a breeze to action. Doing so involves sending a POST request, and there are eight required attributes required for that request to be valid. These include the server location, resource type, database type, and database credentials.

With these parameters in place, you can look to validate your API key and create your database:

import fetch from 'node-fetch';


async function run() {
  const resp = await fetch(
`https://api.kinsta.com/v2/databases`,
{
   method: 'POST',
   headers: {
     'Content-Type': 'application/json',
     Authorization: 'Bearer <YOUR_TOKEN_HERE>'
   },
   body: JSON.stringify({
     company_id: '54fb80af-576c-4fdc-ba4f-b596c83f15a1',
     location: 'us-central1',
     resource_type: 'db1',
     display_name: 'test-db',
     db_name: 'test-db',
     db_password: 'example-password',
     db_user: 'example-user',
     type: 'postgresql',
     version: '15'
   })
}
  );

  const data = await resp.json();
  console.log(data);
}

run();

For every endpoint, you’ll receive the requested data in JSON format:

{
  "database": {
"id": "54fb80af-576c-4fdc-ba4f-b596c83f15a1"
  }
}

Note that if the request fails, you’ll see that information within the return JSON:

{
  "message": "No or invalid API key provided to the request",
  "status": 401,
  "data": null
}

As with every request you make, you should look to keep the data you send and receive safe. Validating your API key is one way to do this, but there are other important factors here:

• Don’t ‘hard code’ information into your requests, such as IDs or your API key. Instead, use environment variables to almost ‘anonymize’ your code.
• Check for updates to the Kinsta API on a regular basis, as endpoint details may change or more modern data protection methods could be available.
• Debugging your API requests could be something you spend a lot of time on, especially for complex integrations. This is where the returned response codes from your request, your Kinsta error logs, and typical debugging tools and techniques will come in useful.

The good news is much of this information can transfer to other actions relating to the databases endpoint. The next section will look at how to delete them.

Retrieving and deleting existing databases

Fetching and deleting your databases both take seconds to do and reuse a lot of the same code you already implemented when creating those databases. The GET request simply needs you to specify the method within your code:

import fetch from 'node-fetch';

async function run() {
  const query = new URLSearchParams({
    internal: 'true',
    external: 'true'
  }).toString();

  const id = 'YOUR_id_PARAMETER';
  const resp = await fetch(
    `https://api.kinsta.com/v2/databases/${id}?${query}`,
    {
    method: 'GET',
    headers: {
      Authorization: 'Bearer <YOUR_TOKEN_HERE>'
    }
    }
  );

  const data = await resp.text();
  console.log(data);
}

run();

This will return a list of information relating to your databases that includes details of your CPU and memory limits, the internal hostname and port, and much more:

{
  "database": {
    "id": "54fb80af-576c-4fdc-ba4f-b596c83f15a1",
    "name": "unique-db-name",
    "display_name": "firstsite-db",
    "status": "ready",
    "created_at": 1668697088806,
    "memory_limit": 250,
    "cpu_limit": 250,
    "storage_size": 1000,
    "type": "postgresql",
    "version": "14",
    "cluster": {
      "id": "54fb80af-576c-4fdc-ba4f-b596c83f15a1",
      "location": "europe-west3",
      "display_name": "Frankfurt, Germany Europe"
      },
    "resource_type_name": "db1",
    "internal_hostname": "some-name.dns.svc.cluster.local",
    "internal_port": "5432",
    "internal_connections": [
    {
      "id": "54fb80af-576c-4fdc-ba4f-b596c83f15a1",
      "type": "appResource"
    }
    ],
    "data": {
      "db_name": "firstsite-db",
      "db_password": "password",
      "db_root_password": "password",
      "db_user": "username"
    },
    "external_connection_string": "postgresql://username:password@localhost:31866/firstsite-db",
    "external_hostname": "firstsite-db-postgresql.external.kinsta.app",
    "external_port": "31866"
  }
}

Deleting a database is just as simple. You send the DELETE method and the ID to the databases endpoint:

…
const resp = await fetch(
  `https://api.kinsta.com/v2/databases/${id}`,
  {
   method: 'DELETE',
   headers: {
…

However, unlike using the MyKinsta dashboard, you must exercise caution when deleting databases with the API. Here, you won’t get any confirmation message or ‘buffer.’ As such, you may also want to build in some error handling or checks to ensure you really do want to delete the database for good.

How the Kinsta API helps you update databases

We’re saving database updates until last because while they seem simple and limited on the surface, this request can also offer some unique opportunities.

On the surface, updates give little scope for change. The only parameters available to alter are the database name and its resource type:

{
  "resource_type": "db1",
  "display_name": "test-db"
}

This is where database updates can slot well into your entire development or system management workflow. For example, the database could be one that moves between states and environments. At different points, you could carry out an update that renames the database based on the project conventions, status of a sprint, or essentially whatever you wish.

The resource type is a simple way to change the size and performance of your database on the fly. This lets you adapt that database to changing project variables or even public demand for your site. Let’s talk about this more next.

The significance of resource_type in vertical scaling

Scalability is a vital element of a modern website, and it should be no different for yours. As such, the Kinsta API offers the resource_type parameter. When it comes to vertical scaling, this could be at the forefront of your strategy. Best of all, it takes all of one line to change your database’s resources to allocate greater or fewer:

import fetch from 'node-fetch';

async function run() {
  const id = 'YOUR_id_PARAMETER';
  const resp = await fetch(
    `https://api.kinsta.com/v2/databases/${id}`,
    {
      method: 'PUT',
      headers: {
       'Content-Type': 'application/json',
       Authorization: 'Bearer <YOUR_TOKEN_HERE>'
      },
    body: JSON.stringify({
      resource_type: 'db1',
      display_name: 'test-db'
    })
  }
  );

  const data = await resp.json();
  console.log(data);
}

run();

You have eight different types to choose from, and in general, the higher the number, the greater the resources you allocate – so db8 will give you more than db1.

This can give you a super-quick way to scale up or down, depending on your current needs.

For example, if you experience more traffic or have to perform more intensive database tasks, you can boost your resources to a higher tier.

Integration with other Kinsta API endpoints

By extension, you can utilize the databases endpoint alongside other Kinsta API endpoints to build out a full-featured database management system or incorporate that management into your other workflows.

One great approach is the vertical scaling opportunity from the last section. However, there are plenty more based on your site’s needs:

• You could create a database at the same time you spin up a new application. This can include all relevant information, such as server location and credentials.
• As part of your deployment process, you could update the resource type of the database based on the load you expect to receive.
• Requesting your log files and site metrics can mean you have a way to create an automated and programmatic method of scaling your site’s database resources without your full input.

This brings up a crucial element of your database management workflow, which is using the information you request throughout. Let’s explore this in the penultimate section.

Using database information from the Kinsta API in your workflow

Many projects will consist of multiple stages, such as initial development, staging, testing, and production. The availability within the Kinsta API can help you handle many tasks in association with your other tools. Creating and managing databases can be an integral cog in this wheel.

For example, when you create a new feature branch within your choice of version control system (VCS), you could trigger a process of creating a new database using the Kinsta API.

Here’s a simplified example of how you can trigger database creation using the API:

import fetch from 'node-fetch';

async function createDatabase(databaseName) {
  const apiToken = 'your_api_token';
  const companyId = 'your_company_id';

  const response = await fetch('https://api.kinsta.com/v2/databases', {
    method: 'POST',
    headers: {
      'Content-Type': 'application/json',
      'Authorization': `Bearer ${apiToken}`
    },
    body: JSON.stringify({
      company_id: companyId,
      location: 'us-central1',
      resource_type: 'db1',
      display_name: databaseName,
      db_name: databaseName,
      db_password: 'example-password',
      db_user: 'example-user',
      type: 'postgresql',
      version: '15'
    })
  });


  if (response.ok) {
    const data = await response.json();
    console.log(`Database '${databaseName}' created successfully.`);
    console.log('Database details:', data);
  } else {
    console.error(`Error creating database '${databaseName}':`, response.statusText);
  }
}


// Usage example
const featureBranchName = 'feature/new-blog-section';
const databaseName = `db_${featureBranchName}`;
createDatabase(databaseName);

Here’s a multi-use, typical example where we define a function to create a database that makes a POST request to the databases endpoint. Our usage example shows the process: a variable holds the Git feature branch path, which we then use as a parameter for the databaseName. From there, we can trigger the creation process based on the dynamic database name.

With this approach to automating database creations, you can ensure stages or features get a dedicated database. This can make it easier to manage the development workflow, give you a cleaner base to work from and reduce the risk of conflicts.

Integrating database information into collaboration tools

Another common and valuable use case for managing your databases with the Kinsta API is to deliver status updates to your collaboration tools, such as Slack or Microsoft Teams. For instance, you could run a separate channel that only posts database statuses.

Doing this means you can keep your team in the loop about the status and availability of databases. Not only does this foster better communication and collaboration, but it can increase how proactive you are towards errors and issues, too.

There are lots of other benefits to this type of integration:

• Enhance visibility. You’re able to update everyone about the status of your databases. This ensures everyone is aware of any potential issues or upcoming maintenance activities.
• Improve responsiveness. You can also notify relevant team members when a database requires attention. This is the direct catalyst of being proactive, which we talked about.
• Facilitate discussion. The centralized platform lets you and your team discuss topics relating to the database in question. This collaborative effort can boost your troubleshooting, knowledge sharing, and more.
• Streamline communication. The automated flow of messages means you eliminate the need for manual notifications and updates.

Linking the Kinsta API with a tool authentication endpoint seems complex on paper, but in practice, it doesn’t take much:

import fetch from 'node-fetch';
const { IncomingWebhook } = require('@slack/webhook');

// Set up the Slack webhook URL (using an environment variable)
const slackWebhookUrl = process.env.SLACK_WEBHOOK_URL;
const webhook = new IncomingWebhook(slackWebhookUrl);

async function sendSlackNotification(message) {
  try {
    await webhook.send({
    text: message
    });
    console.log('Slack notification sent successfully.');
  } catch (error) {
    console.error('Error sending Slack notification:', error);
  }
}

async function getDatabases() {
  const apiToken = process.env.KINSTA_API_TOKEN;
  const companyId = process.env.KINSTA_COMPANY_ID;

  const query = new URLSearchParams({
    company: companyId,
    limit: '10',
    offset: '3'
      }).toString();

  try {
    const response = await fetch(`https://api.kinsta.com/v2/databases?${query}`, {
      method: 'GET',
      headers: {
       'Authorization': `Bearer ${apiToken}`
      }
    });


if (response.ok) {
   const data = await response.json();
   console.log('Retrieved databases:', data);

   // Check the status of each database and send Slack notifications if necessary
   data.forEach(database => {
     if (database.status !== 'ready') {
       const message = `Database '${database.display_name}' is in status '${database.status}'. Please check.`;
       sendSlackNotification(message);
     }
   });
} else {
   console.error('Error retrieving databases:', response.statusText);
}
  } catch (error) {
    console.error('Error retrieving databases:', error);
  }
}

// Usage example

getDatabases();

In this code snippet, we define a function that uses a Slack webhook to send messages to a Slack channel. Then, we run a GET request to retrieve a list of databases associated with our server. For those databases without a ‘ready’ status, we send a notification to the Slack channel.

These are only two quick ways to integrate the Kinsta API with other platforms to manage your databases. Regardless, you can navigate better project outcomes and greater efficiency while you develop a better all-around service.

Summary

Managing your databases is such a vital aspect of running a WordPress project or application that we give you the databases endpoint within the Kinsta API. You can use this to streamline your database management process and automate specific tasks that you would otherwise need the MyKinsta dashboard for.

While the methods to achieve these tasks are simple, you have a lot of control at your fingertips. For instance, you can simply provision or delete new databases if you wish. There are plenty of creative ways to utilize these requests, such as resource scaling, general database ‘housekeeping’, and much more.

We’d love to hear your thoughts on creating and managing databases using the Kinsta API. Share your experiences and insights in the comments section below!

The post How to create and manage databases using the Kinsta API appeared first on Kinsta®.