New Vertabelo Feature: Document-Oriented Vertabelo API

One of the signs of a decent SaaS solution is its public API. A good API helps users to incorporate some automation into an application.

Based on our experience in using other SaaS services, we have introduced a more document-oriented version of our public API. In this article, we will demonstrate how to use Vertabelo API to fetch a list of documents (models), SQL scripts, and data models in various formats.

Why do we call our API document-oriented? We believe that users should easily access their documents (models, SQL scripts) through the API, simply by copying its URL.

Generating a Token

First of all, you have to enable access to the API. You'll need to have at least an individual account or be a member of a company plan to do this.

1. Go to the My profile page.

2. In the left-hand menu, click on Settings.

3. Click on Enable API Access.

4. You'll get a generated API token. This token will be used to identify you when you connect to the Vertabelo API.

Note: You may also disable API access here. You can enable it again to get a new token.

Document Identification

All documents are identified by their URL. The document list is identified by the URL https://my.vertabelo.com/drive, while each model has a unique URL; you can simply copy these as needed. Please see the images below.

The document tree URL

The URL of the physical model. The unique identifier of the model is "WCs…"

Accessing Data

Vertabelo’s document-oriented API functionality allows users to easily fetch:

1. Lists of models and SQL scripts as a document tree in the JSON format.
2. A physical data model as an XML file or a corresponding SQL result.
3. A logical data model as an XML file.
4. An SQL script.
5. A migration script.

Examples

All the following examples use the curl tool. We assumed there is a “$TOKEN” variable available, which stores the public token generated as described in an earlier section of this article. A “:” character in the user parameter (-u option) is intended.

You can use the following command to declare the “$TOKEN” variable:

export TOKEN=""

Download a Document Tree

To fetch a document tree, we need to make a GET HTTPS request to https://my.vertabelo.com/drive with the following HTTP headers:

1. An accept header with a JSON mime type: Accept: application/json
2. An authorization header with a username equal to $TOKEN and an empty password, in the following format: Authorization: Basic (base64($TOKEN:))

Here is the corresponding curl example:

curl  -u "$TOKEN:" -H "Accept: application/json" https://my.vertabelo.com/drive
-----
Result:
{
  "documents": [
    {
    "name": "Logical Model Demo",
    "type": "logical model",
    "link": "https://my.vertabelo.com/logical/model/logical-model-id-here"
    },
    {
    "name": "MySQL demo physical data model",
    "type": "physical model",
    "link": "https://my.vertabelo.com/model/model-id-here"
    },
    {
    "name": "Sample SQL script",
    "type": "SQL script",
    "link": "https://my.vertabelo.com/sql-editor/sql-script-id-here"
    }
  ]
}

The result is a JSON file with a list of the documents (logical and physical models, SQL scripts to which you have access) in your Vertabelo account. Please note that the link property is a valid document URL and can be used for further requests.

Download a Physical Data Model as XML

Our API allows users to download their physical models as XML files. Downloading a model follows the same pattern as the document tree example. It is a GET HTTP request to the URL of the model with the following parameters:

1. An accept header with an XML mime type: Accept: application/text
2. An authorization header with a username equal to $TOKEN and an empty password, in the following format: Authorization: Basic (base64($TOKEN:))

The corresponding curl example:

curl  -u "$TOKEN:" -H "Accept: text/xml" https://my.vertabelo.com/model/$MODEL_ID > model.xml

In the above example, a physical model is saved in the XML format with the name model.xml.

Download a Physical Data Model as an SQL Script

Users can also download SQL scripts generated from their models. To generate an SQL script through the API please, change the header in the previous example to Accept:

Users can also download SQL scripts generated from their models. To generate an SQL script through the API please, change the header in the previous example to Accept: text/sql

The corresponding curl example:

curl -u "$TOKEN:" -H "Accept: text/sql" https://my.vertabelo.com/model/$MODEL_ID > model.sql

The above example saves the DDL script generated from the physical model as the model.sql text file.

Download a Logical Data Model as XML

Logical models can be also downloaded from the Vertabelo API. Downloading a logical data model is almost exactly the same as downloading a physical model. The only difference is a different model URL pattern: https://my.vertabelo.com/logical/model/...

The corresponding curl example:

curl  -u "$TOKEN:" -H "Accept: text/xml" https://my.vertabelo.com/logical/model/$LOGICAL_MODEL_ID > logical-model.xml

,

In the above example, the logical model is saved in the XML format with the name logical-model.xml.

Download an SQL Script

Vertabelo allows users to store and edit their models in the SQL format. To fetch an SQL script, follow the previous patterns. The header Accept: text/sql is expected.

Here is the corresponding curl example:

curl  -u "$TOKEN:" -H "Accept: text/sql" https://my.vertabelo.com/sql-editor/$SCRIPT_ID > script.sql

The above example stores an SQL script as the scripts.sql text file.

Download a Migration Script

In Vertabelo, you can generate migration scripts. This makes it easy to change versions of a database structure. The migration script allows us to choose two versions of a model and generate an SQL script that transforms the model from one version to the other. The script may perform different SQL operations, such as creating new tables, renaming or adding new columns, and creating indexes. We may generate the migration scripts manually using the Vertabelo web application, but it is also possible to create them using API.

To do that, we need to prepare the request in JSON format:

{
"source_model_id" : "from_identifier",
"source_version_id": "from_tag",
"target_model_id" : "to_identifier",
"target_version_id": "to_tag"
}

Here, from_identifier and to_identifier need to be replaced by the source and target identifiers of the physical model. We can obtain them from the URLs. We also need to choose the source and target model versions. We need version tags for that – see how to tag a version of your data model here. Then, simply put the correct version tag as from_tag and to_tag in the JSON. If you wish to migrate from/to the latest version, just use an empty string: "".

Your final JSON should resemble something like this:

{
"source_model_id" : "our-model-id-abc123",
"source_version_id": "v1",
"target_model_id" : "our-model-id-def456",
"target_version_id": "v2"
}

When the JSON is ready, save it as a migrate.json file.

Now, you can query the API. Here is the corresponding curl example:

curl -u $TOKEN: -H "content-type: application/json" -X POST https://my.vertabelo.com/api/v2/migration/migrate -d @migrate.json > migration-script.sql

The command above stores an SQL script as a text file named migration-script.sql.

Summary

As you can see, accessing the Vertabelo API is as simple as writing an HTTP request.

If you'd like to get access to other Vertabelo features, please contact our support service.