# Describing Databases

> Learn how to make a table in a database available for Orbital

Source: https://orbitalhq.com/docs/describing-data-sources/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".
 * 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.

| Activity                          | Required permission |
|-----------------------------------|---------------------|
| View the connected tables         | `VIEW_CONNECTIONS`  |
| Create or modify a database table | `EDIT_CONNECTIONS`  |

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

## Defining a database connection in connections.conf
Instead of [using the UI](#using-the-ui) to add your database, you can add a database by defining a 
a connection in your  [`connections.conf`](/docs/describing-data-sources/configuring-connections)  file.

Here is an example for adding a Postgres database connection:

```hocon
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"
         username = jack // The username to connect with
      }
   }
}
```


Database connections are defined under the `jdbc` element within the `connections.conf` file.

```hocon
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"
         username = jack // The username to connect with
      }
   }
}
```

### Supported drivers

### Postgres

To configure a Postgres connection, specify `jdbcDriver = POSTGRES`

Connection parameters are as follows:

| Parameter name | Description                                     |
|----------------|-------------------------------------------------|
| `host`         | The host address of the Postgres database       |
| `port`         | The port to connect to. Defaults to  `5432`     |
| `database`     | The name of the database on the postgres server |
| `username`     | Optional. The username to use when connecting   |
| `password`     | Optional. The password to use when connecting   |

#### Example

```HOCON
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"
         username = jack // The username to connect with
      }
   }
}
```

### MySQL

To configure a MySql connection, specify `jdbcDriver = MYSQL`

Connection parameters are as follows:

| Parameter name | Description                                   |
|----------------|-----------------------------------------------|
| `host`         | The host address of the MySQL database        |
| `port`         | The port to connect to. Defaults to  `3306`   |
| `database`     | The name of the database on the MySql server  |
| `username`     | Optional. The username to use when connecting |
| `password`     | Optional. The password to use when connecting |

#### Example

```HOCON
jdbc {
    mysql-docker {
        connectionName=mysql-docker
        connectionParameters {
            database=test
            host=localhost
            password=my-secret-pw
            port="3306"
            username=root
        }
        jdbcDriver=MYSQL
    }
}
```

### MSSQL Server

To configure a Postgres connection, specify `jdbcDriver = MSSQL`

Connection parameters are as follows:

| Parameter name           | Description                                                                                             |
|--------------------------|---------------------------------------------------------------------------------------------------------|
| `host`                   | The host address of the MSSQL database                                                                  |
| `port`                   | The port to connect to. Defaults to  `1443`                                                             |
| `database`               | The name of the database on the MS SQL server                                                           |
| `username`               | Optional. The username to use when connecting                                                           |
| `password`               | Optional. The password to use when connecting                                                           |
| `schema`                 | Optional. The schema to use - defaults to `dbo`                                                         |
| `trustServerCertificate` | Optional. Forces Orbital to trust the certificate that's provided by the SQL Server. Defaults to `true` |
| `encrypt`                | Optional. Defines if the connection to MSSQL server should be encrypted. Defaults to `true`             |
#### Example

```HOCON
jdbc {
    sqlServerConnection {
        connectionName=sqlServerConnection
        connectionParameters {
            database=Northwind
            encrypt="true"
            host=localhost
            password=ChangeMe
            port="14330"
            schema=dbo
            trustServerCertificate="true"
            username=sa
        }
        jdbcDriver=MSSQL
    }
}
```

### Oracle

_Available since 0.38.0-M2_

To configure an Oracle connection, specify `jdbcDriver = ORACLE`

Connection parameters are as follows:

| Parameter name | Description                                              |
|----------------|----------------------------------------------------------|
| `host`         | The host address of the Oracle database                  |
| `port`         | The port to connect to. Defaults to  `1521`              |
| `service`      | The name of the Oracle service to connect to             |
| `username`     | The username to use when connecting                      |
| `password`     | The password to use when connecting                      |

The connection is built using Oracle's service-name URL format
(`jdbc:oracle:thin:@//host:port/service`).

#### Example

```HOCON
jdbc {
    oracleConnection {
        connectionName=oracleConnection
        connectionParameters {
            host=localhost
            port="1521"
            service=FREEPDB1
            username=system
            password=ChangeMe
        }
        jdbcDriver=ORACLE
    }
}
```

### Redshift

To configure a Postgres connection, specify `jdbcDriver = REDSHIFT`

Connection parameters are as follows:

| Parameter name | Description                                     |
|----------------|-------------------------------------------------|
| `host`         | The host address of the Redshift database       |
| `port`         | The port to connect to. Defaults to  `5439`     |
| `database`     | The name of the database on the Redshift server |
| `username`     | Optional. The username to use when connecting   |
| `password`     | Optional. The password to use when connecting   |

#### Example

```HOCON
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 = REDSHIFT // 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"
         username = jack // The username to connect with
      }
   }
}
```

### Snowflake

To configure a Postgres connection, specify `jdbcDriver = SNOWFLAKE`

Connection parameters are as follows:

| Parameter name | Description                                             |
|----------------|---------------------------------------------------------|
| `account`      | The name of the Snowflake account                       |
| `schema`       | The name of the schema to connect to                    |
| `db`           | The name of the database to connect to                  |
| `warehouse`    | The name of the warehouse where the snowflake db exists |
| `username`     | The username to use when connecting                     |
| `password`     | The password to use when connecting                     |
| `role`         | The role to specify when connecting                     |

#### Example

```hocon
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 = SNOWFLAKE // 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.
        account = mySnowflakeAccount123.eu-west-1
        schema = public
        db = demo_db
        warehouse = COMPUTE_WH
        schema = public
        role = QUERY_RUNNER
      }
   }
}
```

## Describing tables in Taxi

**Note: Before you continue...**
Before you run through this guide, it's worth understanding the basics of [taxi](https://taxilang.org/language-reference/taxi-language/), and how Orbital uses it.

   Also, make sure you have a [taxi project set up](https://taxilang.org/intro/getting-started/), and that it's been [published to Orbital](docs/connecting-data-sources/connecting-a-git-repo/).  If you're running through one of our
   tutorials, we've already taken care of this for you.

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](/docs/connecting-data-sources/schema-publication-methods/), 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:

```taxi
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:

| Parameter  | Description                                                                                                                                                         |
|------------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| connection | The name of a connection, as defined in your [connections configuration file](#defining-a-database-connection-in-connections-conf). |
| schema     | The name of the schema.  Optional, depending on your database                                                                                                       |
| table      | The name of the table                                                                                                                                               |

It's possible to use environment variables in these annotations, as described [here](/docs/deploying/configuring-orbital#environment-variables-in-annotations).

#### Mapping the primary key
Use an `@Id` annotation to define the column that represents the primary key. To map a composite key, annotate
each column that forms the key with `@Id`, and Orbital treats the combination as the primary key.

## 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:

```taxi
import com.orbitalhq.jdbc.DatabaseService

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

The `@DatabaseService` annotation contains the following parameters:

| Parameter  | Description                                                                                                                                                           |
|------------|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| connection | The name of a connection, as defined in your (connections configuration file)[/how-to-guides/connections/manage-database-connection/#defining-a-database-connection]. |

### Sample queries

#### Fetch everything from a table:

```taxi
find { Customer[] }
```

#### Fetch a single value from a table:

```taxi
find { Customer( CustomerId == 123 ) }
```

#### Fetch values by criteria:

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

#### Join two tables

```taxi
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
```taxi
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:

```taxi
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:

```taxi
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 )

```taxi
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:
```taxi
find { Customer[] } as {
  name : CustomerName // this information comes from the database
  currentBalance : CurrentBalance // An API call is made to fetch account balance
}[]
```

#### Using IN and NOT IN operators

When querying databases, you can use in and not in operators to filter against multiple values at once. This is more efficient than using multiple OR conditions and generates optimized SQL.

**Filtering with `IN` operator**

To find records where a field matches any value in a list:

```taxi
// Find customers with specific IDs
find { Customer[]( CustomerId in [123, 456, 789] ) }
```

```taxi
// Find customers from specific countries
find { Customer[]( CountryOfBirth in ["US", "UK", "CA"] ) }
```

**Filtering with `NOT IN` operator**

To find records where a field does not match any value in a list:

```taxi
// Find customers excluding specific IDs
find { Customer[]( CustomerId not in [123, 456] ) }
```

```taxi
// Find customers not from specific countries
find { Customer[]( CountryOfBirth not in ["US", "UK"] ) }
```

**Combining with other conditions**

IN and NOT IN operators can be combined with other conditions using `&&` (AND) and `||` (OR):

```taxi
// Find active customers from specific countries
find { Customer[]( CountryOfBirth in ["US", "UK", "CA"] && Status == "ACTIVE" ) }
```

```taxi
// Find customers either VIP or from specific regions
find { Customer[]( CustomerType == "VIP" || CountryOfBirth in ["US", "UK"] ) }
```

Both in and not in work with any data type - strings, numbers, dates, and custom semantic types.

### Collection options

_Available since 0.38.0_

You can limit, paginate and sort database results using [collection options](/docs/querying/collection-options), written
alongside your filter criteria:

```taxi
find { Customer[]( CountryOfBirth == "GB", orderBy: DateOfBirth desc, offset: 20, limit: 10 ) }
```

For databases, Orbital pushes `limit`, `offset` and `orderBy` into the generated SQL, using the correct dialect for your
driver (for example, MSSQL renders `TOP` / `OFFSET ... FETCH`). This means the database does the work, and only the rows
you asked for are returned over the wire.

| Option                | Behaviour against a database                                                                            |
|-----------------------|--------------------------------------------------------------------------------------------------------|
| `limit`, `offset`     | Pushed into the SQL query.                                                                              |
| `orderBy`             | Pushed into the SQL query when the ordered type maps to a single column. Otherwise Orbital sorts after fetching. |
| `uniqueBy`            | Always applied by Orbital, after fetching.                                                              |

Null ordering is consistent whether the sort runs in SQL or in Orbital: nulls are placed **last** when sorting ascending,
and **first** when sorting descending.

See [collection options](/docs/querying/collection-options) for the execution model, and the
[Taxi language reference](https://taxilang.org/docs/taxiql/querying#collection-options) for the full syntax.

## 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:

```taxi
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.

| Write behaviour | Annotation                           | Comments                                       |
|-----------------|--------------------------------------|------------------------------------------------|
| Insert          | `com.orbitalhq.jdbc.InsertOperation` |                                                |
| Update          | `com.orbitalhq.jdbc.UpdateOperation` | Requires an `@Id` field                        |
| Upsert          | `com.orbitalhq.jdbc.UpsertOperation` | Falls back to an insert if no `@Id` is defined |
| Delete          | `com.orbitalhq.jdbc.DeleteOperation` | Requires an `@Id` field. See [Deleting data from a database](#deleting-data-from-a-database) |

### 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
```taxi
// 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

```taxi
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:

```taxi
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`

### Batching writes

_Available since 0.38.0-M1_

By default, each record is written in its own statement. For high-volume writes — streaming a Kafka topic into
a table, or loading a large result set — you can have Orbital accumulate records and write them in batches.

Add `batchSize` and / or `batchDuration` to the write annotation:

```taxi
@DatabaseService(connection = "prices-database")
service PriceService {
   table prices : StockPrice[]

   @InsertOperation(batchSize = 500, batchDuration = 2000)
   write operation savePrice(StockPrice):StockPrice
}
```

Both parameters are available on `@InsertOperation`, `@UpdateOperation` and `@UpsertOperation`:

| Parameter | Type | Meaning |
|---|---|---|
| `batchSize` | `Int` | Write the batch once this many records have accumulated |
| `batchDuration` | `Int` | Write the batch this many **milliseconds** after the first record in it arrived |

Whichever condition is met first triggers the write. Batching is enabled as soon as either parameter is
present — you don't need both, but you almost always want both:

| If you set | The other defaults to |
|---|---|
| `batchSize` only | A 30 minute timer |
| `batchDuration` only | An unbounded batch size — only the timer writes |

**Warning: Set batchDuration as well as batchSize**
A record isn't returned to the query until the batch containing it has been written. A batch that never
  fills up is written when its timer fires — so if you set `batchSize = 500` and leave `batchDuration` unset,
  a query writing 10 records waits for the 30 minute default before those rows are written and the query
  completes.

  Set `batchDuration` to the longest you're willing to wait for a partly-filled batch.

Batches are scoped to a single query. Each running query keeps its own buffer, which is discarded when the
query completes or is cancelled; two concurrent queries writing to the same table don't share a batch.

Results are unchanged in content, but not in timing: each written record is still returned, and still carries
its generated values, but records are emitted in bursts as each batch completes rather than one at a time.

**Note: Deletes can't be batched**
`@DeleteOperation` doesn't support `batchSize` or `batchDuration`. Declaring them on a delete raises an
  error telling you to remove them.

## Deleting data from a database

_Available since 0.38.0-M2_

To delete rows from a database table, declare a `write operation` annotated with `@DeleteOperation`:

```taxi
import com.orbitalhq.jdbc.Table
import com.orbitalhq.jdbc.DatabaseService
import com.orbitalhq.jdbc.DeleteOperation
import com.orbitalhq.jdbc.DeleteResult

type CustomerId inherits Int
type CustomerName inherits String

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

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

   @DeleteOperation
   write operation deleteCustomer(Customer):DeleteResult

   @DeleteOperation
   write operation deleteCustomers(Customer[]):DeleteResult
}
```

Rows are matched on the model's `@Id` values - other fields are ignored. Models declaring multiple `@Id`
fields are matched on the combination of all of them.

Pass a single instance to delete one row, or an array to delete several.

The return type can be any model with a `deletedCount` field. `com.orbitalhq.jdbc.DeleteResult` is
provided for convenience.

**Requirements for delete operations:**
- The model must have at least one `@Id` field
- If no rows match, the operation returns a `deletedCount` of `0`
- If the table hasn't been created yet (see [Table creation](#table-creation)), the operation returns a
  `deletedCount` of `0` rather than failing, and reports a notice on the query's error stream explaining
  that the table doesn't exist yet, so you can see why nothing was deleted
- The delete runs in a single transaction, so if it fails partway the whole delete is rolled back and the
  table is left unchanged

**Note: Populating non-id fields**
TaxiQL requires mandatory fields to be populated when constructing an instance. As only the `@Id` values
  are used to match rows, declare the other fields as nullable (as in the example above), or populate them in the query.

### Sample delete queries

#### Deleting a single row

```taxi
given { customer : Customer = { id : 123 } }
call CustomerService::deleteCustomer
```

#### Deleting multiple rows

```taxi
given {
   customers : Customer[] = [
      { id : 123 },
      { id : 456 }
   ]
}
call CustomerService::deleteCustomers
```

## Running native SQL queries

_Available since 0.38.0-M2_

For queries that go beyond Orbital's standard table operations - joins, aggregations, or
dialect-specific SQL - use the `@SqlQuery` annotation as an escape hatch. The SQL is passed
to your database as written, and results map back to Taxi models.

```taxi
import com.orbitalhq.jdbc.SqlQuery
import com.orbitalhq.jdbc.DatabaseService
import com.orbitalhq.jdbc.UpdateResult

type FilmTitle inherits String
type ReleaseYear inherits Int
type DirectorName inherits String
type FilmCount inherits Int

// Return types don't need an @Table annotation - any model
// whose field names match the result set's column names (or aliases) works
model Film {
   title : FilmTitle
   releaseYear : ReleaseYear
}

model DirectorFilmSummary {
   director : DirectorName
   filmCount : FilmCount
}

@DatabaseService(connection = "films-database")
service FilmStats {
   @SqlQuery(sql = "SELECT title, release_year AS releaseYear FROM film WHERE release_year > :year")
   operation recentFilms(year : ReleaseYear) : Film[]

   @SqlQuery(sql = """
      SELECT d.name AS director, COUNT(f.id) AS filmCount
      FROM film f
      JOIN director d ON f.director_id = d.id
      GROUP BY d.name
   """)
   operation filmsPerDirector() : DirectorFilmSummary[]

   @SqlQuery(sql = "DELETE FROM film WHERE release_year < :cutoff")
   write operation purgeOldFilms(cutoff : ReleaseYear) : UpdateResult
}
```

One annotation serves both reads and writes:

- A plain `operation` runs the SQL as a query. Rows are matched to the return type's fields by
  column name (or alias), case-insensitively.
- A `write operation` runs the SQL as a statement (`UPDATE`, `DELETE`, `INSERT`), returning the
  number of rows affected. `com.orbitalhq.jdbc.UpdateResult` is provided for this, or declare
  your own model with an `affectedRows` field (`deletedCount` also works, for `DELETE`-shaped
  statements).

### Parameter binding

Parameters use the `:parameterName` syntax, and bind to the operation's parameters by name.
Values are always passed as prepared-statement parameters - never substituted into the SQL text -
so user-supplied values can't alter the query. If the SQL references a parameter the operation
doesn't declare, the query reports an error naming the missing parameter.

### Invoking native queries

Read operations are invoked like any other operation - through a `find` query:

```taxi
given { year : ReleaseYear = 2000 }
find { Film[] }
```

```taxi
find { DirectorFilmSummary[] }
```

Write operations are invoked with `call`:

```taxi
given { cutoff : ReleaseYear = 1980 }
call FilmStats::purgeOldFilms
```

### Behaviour notes

- The table isn't created automatically - the SQL runs against whatever schema exists.
- [Collection options](/docs/querying/collection-options) (`limit`, `offset`, `orderBy`) aren't
  pushed into your SQL - Orbital applies them after fetching. If you need paging inside the query,
  express it in the SQL itself.
- For simple filtering, prefer standard `table` operations, which stay portable across
  databases and integrate with query planning.