For an extension, to modify database data, is it better to use SQL or API

Question

Simple question here, From a computing and 'civicrm correctness' point of view, is using SQL (via CRM_Core_DAO::executeQuery or similar) or using the API ( civicrm_api3('Contact', 'create', $params)) better? Thanks, V

Answer 1

Generally, it is wisest to always use the API. Many necessary updates actually touch multiple tables and the API takes care of it nicely. This is especially true with financial updates. The API also encapsulates the database structure so as to effectively hide any database structure changes that may happen over time.

IMHO, pretty much the only time that SQL might be called for is if you are doing a huge bulk update wherein using the API would take excessively long.

Answer 2

Agree, the API is decidedly the best way as it does multiple stuff and should continue to function over upgrades when there are database changes. I only use SQL when either the API does not do what I need, with custom data because it is easier most of the times or when doing bulk stuff.