Database connectors: delete support and native SQL queries

Two additions to our database connectors in this release: deleting rows through TaxiQL, and running SQL you’ve written yourself.

​

Deleting rows

Databases connected to Orbital have long supported reads, inserts, updates and upserts. Deletes now join the family - annotate a write operation with @DeleteOperation:

@DatabaseService(connection = "customers-database")
service CustomerService {
   @DeleteOperation
   write operation deleteCustomer(Customer):DeleteResult

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

Rows are matched on the model’s @Id fields (composite keys work too - rows match on the combination), and you can pass a single instance or a whole array. Invoking it looks like any other mutation:

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

You get back a deletedCount telling you how many rows went away. Deletes run in a single transaction, so a failure partway through rolls the whole thing back rather than leaving you half-deleted.

​

Native SQL queries

Orbital’s table operations cover most day-to-day querying, and keep your queries portable across databases. But sometimes you just want to write the SQL yourself - a join, an aggregation, or something dialect-specific. The new @SqlQuery annotation is that escape hatch:

@DatabaseService(connection = "films-database")
service FilmStats {
   @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[]
}

The SQL is passed to your database exactly as written, and result columns map back to your Taxi model’s fields by name or alias. One annotation covers both directions: a plain operation runs the SQL as a query, while a write operation runs it as a statement (UPDATE, DELETE, INSERT) and returns the number of rows affected.

Operation parameters bind into the SQL with the :parameterName syntax:

@SqlQuery(sql = "SELECT title, release_year AS releaseYear FROM film WHERE release_year > :year")
operation recentFilms(year : ReleaseYear) : Film[]

Values are always bound as prepared-statement parameters - never spliced into the SQL text - so user-supplied values can’t alter your query.

Both features work across all of Orbital’s SQL database connectors. Full details are in the database docs, including native query behaviour notes.