API: update SQL functional api (GH6300)

First draft for the functional api part of #6300.