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 Configure

​

Preview the generated types

A preview is shown, containing the models, fields and types that were imported from the Database schema.

You can change any of the types (eg., swapping a primitive type with a more specific semantic type), by clicking on the pencil icon next to the type name, and search for the desired type.

Once you’re satisfied, click Save, and the schema will be updated.

​

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.

​

Adding a database data source manually

Instead of using the UI to add your database, you can add a database by defining a a connection in your connections.conf file.

Here is an example for adding a Postgres database connection:

jdbc { // The root element for database connections
   another-connection { // Defines a connection called "another-connection"
      connectionName = another-connection // The name of the connection.  Must match the key used above.
      jdbcDriver = POSTGRES // Defines the driver to use.  See below for the possible options
      connectionParameters { // A list of connection parameters.  The actual values here are defined by the driver selected.
         database = transactions // The name of the database
         host = our-db-server // The host of the database
         password = super-secret // The password
         port = "2003" // The port
         username = jack // The username to connect with
      }
   }
}

To see all the supported databases, and their connection configurations, read configuring database connections

​

Describing tables in Taxi

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 com.orbitalhq.jdbc.Table

@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.

​

Querying databases

To expose a database as a source for queries, the database must have a service and table operation exposed.

Here’s an example:

import com.orbitalhq.jdbc.DatabaseService

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

The @DatabaseService annotation contains the following parameters:

​

Sample queries

​

Fetch everything from a table:

find { Customer[] }

​

Fetch a single value from a table:

find { Customer( CustomerId == 123 ) }

​

Fetch values by criteria:

find { Customer[]( DateOfBirth <= '1989-10-01' && CountryOfBirth == 'NZ' ) }

​

Join two tables

find { Customer[] } as (customer:Customer) -> {
  name : FirstName
  // defines a join between the Customer and Purchase tables
  purchases : Purchases[](CustomerId == customer.id) 
}

​

Join two tables, transforming data

find { Customer[] } as (customer:Customer) -> {
  name : FirstName
  // defines a join between the Customer and Purchase tables
  purchases : Purchases[](CustomerId == customer.id) as {
     // Inside this scope we have access to both Customer data and Purhcase data
     productName : ProductName
     price : ProductPrice
  // Be sure to include the array marker, as we're defining an array of
  // objects (Purchase[] -> OurType[])
  }[] // <--- array marker
}

​

Fetching from a database, enrich from another source

As with all TaxiQL queries, enriching data from multiple sources requires simply asking for the data you need - Orbital works out the correct integration.

Assuming a schema with a database such as:

import com.orbitalhq.jdbc.Table
import com.orbitalhq.jdbc.DatabaseService

@Table(connection = "customers-database", schema = "public" , table = "customer" )
closed model Customer {
  @Id
  id : CustomerId inherits Int
  name : CustomerName inherits String
}

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

And we also have an API that exposes balance information:

closed model CustomerBalance {
   customerId : CustomerId
   balance : CurrentBalance
}

service AccountBalanceService {
   @HttpOperation(url="https://fakeurl/customers/{id}/balance", method = "GET" )
   operation getCustomerBalance(@PathVariable id:CustomerId):CustomerBalance
}

The below call assumes we’re fetching customer details from our database, then enriching against an API call (API )

find { Customer(CustomerId == 123) } as {
  name : CustomerName // this information comes from the database
  currentBalance : CurrentBalance // An API call is made to fetch account balance
}

Or, to fetch that same data for all customers:

find { Customer[] } as {
  name : CustomerName // this information comes from the database
  currentBalance : CurrentBalance // An API call is made to fetch account balance
}[]

​

Writing data to a database

To expose a database table for writes, you need to provide a write operation in a service, specifying the write behaviour:

import com.orbitalhq.jdbc.Table
import com.orbitalhq.jdbc.DatabaseService
import com.orbitalhq.jdbc.UpsertOperation

@Table(connection = "customers-database", schema = "public" , table = "customer" )
closed model Customer {
  @Id
  id : CustomerId inherits Int
  name : CustomerName inherits String
}

@DatabaseService(connection = "customers-database")
service CustomerService {
   table customers : Customer[]
   
   @UpsertOperation
   write operation saveCustomer(Customer):Customer
}

In this example, the saveCustomer operation will attempt to perform an upsert.

​

Table creation

If the database table does not exist, Orbital will create it when first attempting to write.

If the database table does exist, but with a different schema, writes may fail.

No schema migrations are performed.

​

Example queries

When writing data from one data source into a database, it’s not neccessary for the data to align with the format of the persisted value.

Orbital will automatically adapt the incoming data to the format required by the db.

This may involve projections and even calling additional services if needed.

​

Inserting a static value into a database

// inserting a static value into a database
given { customer : Customer = 
  {
    customerId : 123,
    name : "Jimmy Smitts"  
  } 
}
call CustomerService::saveCustomer

​

Stream data from Kafka into a database

import com.orbitalhq.jdbc.Table
import com.orbitalhq.jdbc.DatabaseService
import com.orbitalhq.jdbc.UpsertOperation

// Common, shared types:
type StockSymbol inherits String
type StockPrice inherits Decimal

// Database definitions:
@Table(connection = "prices-database", schema = "public" , table = "stock-price" )
closed model StockPrice {
  @Id
  symbol : StockSymbol
  price : StockPrice
}

@DatabaseService(connection = "prices-database")
service PriceService {
   table stockPrices : StockPrice[]
   
   @UpsertOperation
   write operation savePrice(StockPrice):StockPrice
}

// Kafka definitions:
// Note that field names don't align - orbital
// handles this for us.
closed model PriceUpdateMessage {
  ticker : StockSymbol
  lastTradedPrice : StockPrice
}

Then, the query:

stream { PriceUpdateMessage } 
call PriceService::savePrice

Orbital writes each message received from Kafka into the db - creating the table if required, and transforming the Kafka message to the format defined by StockPrice