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.