Describing Databases

​

Overview

Orbital can connect to databases to fetch data when running a query.

In order to understand the data that is present in a database table, Orbital uses a Taxi Schema for that table. The taxi schema describes the table - it’s columns and the data they hold.

Because we’re using Taxi here, the column descriptions are richer than things like String or Integer - instead using rich semantic tags like FirstName, LastName, or EmailAddress

In this guide, we’ll learn how to create a Taxi Schema for a specific database table - both through the user interface, and by directly editing a schema

​

Using the UI

The UI allows you to connect a database table directly, without having manually edit taxi files. Through the UI, Orbital will connect to the database, and create:

• A series of types for each column in the database table
• A model that describes the database table
• A service that exposes query capabilities for the table

​

Importing a new table

• From the home page, click Add a data source.
  • Alternatively, Click Schema Explorer in the left-hand navigation menu, then click “Add new”
• For the schema type to import, select “Database table”

• Click the connection name drop-down, and select the connection for your database
  • If you haven’t yet created your connection, you can click “Add new connection”. This guide has more details.
• Select the table from the drop-down
• Specify a namespace for the taxi types, models and services that will be created
• Click Create

At this stage you’ll be taken to a screen that allows you to preview the information gathered from the database table. In this form, you can click to explore the created models, types and services that were generated.

​

Changing the assigned types

Orbital has assigned reasonable defaults to all the fields. Specifically

• Id’s have been tagged
• Foreign keys have been mapped
• For all other fields, new semantic types have been created.

If the columns in your database map to exisitng semantic types, you may wish to update the definitions. To do this:

• Select a Model from the table on the left-hand side
• Click on the blue link for the column you wish to change the type of
• A search dialog is displayed
• From here, you can search for existing types in your catalog, or create a new type

You can also click to edit documentation for any of the types, models and services.

• Once you’re satisfied with the edits to your table and types, click Save
• The schema has been created and written to the taxi project configured in your schema server.

​

Required permissions

In order to view, create or edit connected database tables through the UI, users must have the following permissions granted.

See (this guide)[/how-to-guides/auth/manage-user-permissions] for more information on role based security.

​

Editing a taxi schema

Taxi files define the mappings of data models and the services that expose them. In this guide, we’ll describe how expose a new database table to Orbital, and make it queryable.

Before starting, in your taxi project, create a new file under the src/ directory. It’s up to you what you name it. For this example, customers.taxi is a good start.

​

Databases, and pull-based schema definitions

As discussed in publishing schemas to Orbital, there are different ways for Orbital to consume schema information - either by data sources pushing their information directly to Orbital (well suited for application APIs), or by pulling from git-based repositories that describe the schemas.

While the push model is preferred, it’s not currently supported for databases. We’re looking into ways to embed Taxi metadata into DDL schema definitions. For now, you’ll need to maintain taxi definition file that describes the database.

​

Defining a table mapping

Tables are exposed to Orbital using the annotation @com.orbitalhq.jdbc.Table on a model.

Fields names in the model are expected to align with column names from the database.

Here’s an example:

import io.orbital.jdbc.Table

namespace demo.customers {

  @Table(connection = "films-database", schema = "public" , table = "customer" )
  model Customer {
    @Id // Use @Id to denote the primary key
    customerId : CustomerId
    firstName : CustomerFirstName? // Nullable columns should have the Taxi nullable symbol
    lastName : CustomerLastName
  }
}

The @Table annotation contains the following parameters:

​

Mapping the primary key

Use an @Id annotation to define the column that represents the primary key. At this stage, composite keys are not supported.

​

Exposing a database service

Orbital uses Taxi Services to expose query capabilities. To configure Orbital to be able to query the database table, a service must be declared, and annotated with the @com.orbitalhq.jdbc.DatabaseService annotation.

Here’s an example:

import io.vyne.jdbc.DatabaseService

namespace demo.customers {
   @DatabaseService(connection = "films-database")
   service CustomerService {
      table customers : Customer[]
   }
}

The @DatabaseService annotation contains the following parameters:

The service should expose a query operation, which returns an array of the mapped model type.

The operation should also define the query capabilities (filtering and aggregating) you wish to expose.

​

Publishing changes to Orbital

If you’ve connected your project as a git-based project, you’ll need to commit and push your changes to the remote git repository. They’ll then be picked up om the next poll, and be available to use in Orbital.

You should now see your new types and services present in the data catalog of Orbital.