New Vertabelo Feature: Document-Oriented Vertabelo API

Updated on: November 17, 2023

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
• Document Identification
• Accessing Data
• Download a Document Tree
• Download a Physical Data Model as XML
• Download a Physical Data Model as an SQL Script
• Download a Logical Data Model as XML
• Download an SQL Script
• Download a Migration Script
• Managing Document (Model) Sharing
• List users with document sharing
• Send invitations to other team members
• Cancel document sharing
• Import a Physical Data Model as XML
• Importing the physical model
• Importing the logical model
• Summary

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="<token_value>"

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: text/xml
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: 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.

Managing Document (Model) Sharing

The API also allows you to see which team members the document (model, SQL script) has been shared with. You can also send invitations to other team members, change the document permission type, and cancel sharing.

All the API methods described below require:

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

List users with document sharing

To see users you have shared the document with, send a HTTP GET request to the URL /api/v2/share/users/$MODEL_ID.

Related curl example:

curl -u "$TOKEN:" -H "Accept: application/json" https://my.vertabelo.com/api/v2/share/users/$MODEL_ID

The sample response looks like the following:

[
    {
        "accessType": "owner",
        "email": "member1@gmail.com",
        "name": "Member 1"
    },
    {
        "accessType": "editor",
        "email": "member2@gmail.com",
        "name": "Member 2"
    },
    {
        "accessType": "viewer",
        "email": "member3@gmail.com",
        "name": "Member 3"
    }
]

Send invitations to other team members

To send a document invitation to another user, send a HTTP POST request to the URL /api/v2/share/invite/$MODEL_ID with the content (BODY):

{
    "accessType": "viewer",
    "email": "member4@gmail.com",
    "notify": true,
    "invitePersonalMessage": "You are invited to edit the document"
}

Related curl example:

curl -u "$TOKEN:" -H "Accept: application/json" -H "Content-Type: application/json" --data-raw '{
    "accessType": "viewer",
    "email": "member4@gmail.com",
    "notify": true,
    "invitePersonalMessage": "You are invited to edit the document"
}' https://my.vertabelo.com/api/v2/share/invite/$MODEL_ID

As a response, the server will return an "ok" message (in case of correct execution) or an error message (in case of incorrect execution).

Cancel document sharing

To cancel sharing, send a HTTP POST request to the URL /api/v2/share/delete/$MODEL_ID with the content (BODY):

{
    "email": "member4@gmail.com"
}

Related curl example:

curl -u "$TOKEN:" -H "Accept: application/json" -H "Content-Type: application/json" --data-raw '{
    "email": "member4@gmail.com"
}' https://my.vertabelo.com/api/v2/share/delete/$MODEL_ID

As a response, the server will return an "ok" message (in case of correct execution) or an error message.

Import a Physical Data Model as XML

The API allows you to import the model as an XML file, either as a completely new document or to an existing document. In the latter case, you can overwrite existing data (using the overwrite option) or add new objects to those already existing in the document (using the add option).

These API functions are equivalent to user interface functions: "My Vertabelo -> Create new document" and "Import -> Import from XML". Import can apply to both physical and logical models.

All the API methods described below require:

1. An authorization header with a username equal to $TOKEN and an empty password in the format Authorization: basic (base64($TOKEN:))
2. An accept header with a JSON mime type: Accept: application/json
3. A content type header with a JSON mime type: Accept: text/xml

Importing the physical model

To import a physical model from an XML file as a new document, send an HTTP POST request to the URL: /api/v2/model/new with the XML file as content (body). Related curl example:

curl -u "$TOKEN:" -H "Accept: application/json" -H "Content-Type: text/xml" --data-binary "@/tmp/physical_model.xml" https://my.vertabelo.com/api/v2/model/new

As a response, the server will return an "ok" status and the ID of the created document (in case of correct execution) or an error message (in case of incorrect execution).

In the example above, a new document will be created in the root folder and given a default name. To have the document created in a different folder and with a name in the API request, the folderId and name parameters should be added to the URL.

Related curl example:

curl -u "$TOKEN:" -H "Accept: application/json" -H "Content-Type: text/xml" --data-binary "@/tmp/physical_model.xml" 'https://my.vertabelo.com/api/v2/model/new?folderId=$FOLDER_ID&name=My_custom_name'

To import a physical model from an XML file to an existing document by overwriting its content, send an HTTP POST request to the URL: /api/v2/model/overwrite/$MODEL_ID with the XML file as content (body).

Related curl example:

curl -u "$TOKEN:" -H "Accept: application/json" -H "Content-Type: text/xml" --data-binary "@/tmp/physical_model.xml" https://my.vertabelo.com/api/v2/model/overwrite/$MODEL_ID

To import a physical model from an XML file by adding new objects to an existing document, send an HTTP POST request to the URL: /api/v2/model/add/$MODEL_ID with the XML file as content (body).

Related curl example:

curl -u "$TOKEN:" -H "Accept: application/json" -H "Content-Type: text/xml" --data-binary "@/tmp/physical_model.xml" https://my.vertabelo.com/api/v2/model/add/$MODEL_ID

In both of the above scenarios, the server will return an "ok" status (in case of correct execution) or an error message (in case of incorrect execution).

Importing the logical model

The import of the logical model is similar to the logical one, only the service addresses differ.

To import a logical model from an XML file as a new document, send an HTTP POST request to the URL: /api/v2/logical/model/new with the XML file as content (body).

Related curl example:

curl -u "$TOKEN:" -H "Accept: application/json" -H "Content-Type: text/xml" --data-binary "@/tmp/logical_model.xml" https://my.vertabelo.com/api/v2/logical/model/new

As a response, the server will return an "ok" status and ID of the created document (in case of correct execution) or an error message (in case of incorrect execution).

In the example above, a new document will be created in the root folder and given a default name. To create the document in a different folder and include a name in the API request, add the folderId and name parameters to the URL.

Related curl example:

curl -u "$TOKEN:" -H "Accept: application/json" -H "Content-Type: text/xml" --data-binary "@/tmp/logical_model.xml" 'https://my.vertabelo.com/api/v2/logical/model/new?folderId=$FOLDER_ID&name=My_custom_name'

To import a logical model from an XML file to an existing document by overwriting the document content, send an HTTP POST request to the URL: /api/v2/logical/model/overwrite/$MODEL_ID with the XML file as content (body).

Related curl example:

curl -u "$TOKEN:" -H "Accept: application/json" -H "Content-Type: text/xml" --data-binary "@/tmp/logical_model.xml" https://my.vertabelo.com/api/v2/logical/model/overwrite/$MODEL_ID

To import a logical model from an XML file to an existing document by adding new objects to its content, send an HTTP POST request to the URL: /api/v2/logical/model/add/$MODEL_ID with the XML file as content (body).

Related curl example:

curl -u "$TOKEN:" -H "Accept: application/json" -H "Content-Type: text/xml" --data-binary "@/tmp/logical_model.xml" https://my.vertabelo.com/api/v2/logical/model/add/$MODEL_ID

In both of the above scenarios, the server will return an "ok" status (in case of correct execution) or an error message (in case of incorrect execution).

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.