Security
Application servers (web servers, containers etc) are expected to be exposed to customers/ untrusted external actors directly and undergo much stronger security testing for this purpose. Database servers by comparison frequently have vulnerabilities found, and if exposed directly would likely be fairly subject to exploit.
Application logic & permissioning can often be non-trivial. While database systems offer some limited capabilities, it would probably normally be more cohesive to centralize these in a more capable system (application logic).
Decoupling
Allowing customers to couple directly to your physical datamodel, causes a problem in that you become contractually/ commercially obliged to maintain that exact same datamodel. This is very undesirable, since it makes it impossible to maintain/ improve/ refactor your datamodel since changing it will break customers. (Management will tell you not to.)
This is particularly bad if you can't see how your customers are using it -- ie. if you give them a raw DB connection & can't even yourself parse/ rewrite what they are doing.
Portability between backends is also an issue. While the security and stored proc/ scripting facilities the DB offers you are limited and poor tools for the job, even worse they are vendor-specific. When you want to migrate to a different database for performance/ scalability/ cost reasons, you will find yourself stuck.
The preferred solution is to contract to a "logical model", which is somewhat decoupled from your physical implementation. This also has the benefit that it generally gives a simpler, clearer and more useful model to external parties.
Better API Structure
Several potential improvements:
- As mentioned, defining a nice clear logical model is normally easier for your customers to consume.
- Offering it by REST makes it much more broadly available, from a vast range of client software & tools, than requiring client software to include a specific DB library, open a connection & run SQL.
- API standards such as GraphQL can give you really nice & simultaneous powerful generalized graph access and data retrieval across your logical model -- ie. many of the advantages of SQL -- while giving a better degree of permissioning and control.
Development Effectiveness
Software engineering tools -- IDEs, debuggers, frameworks, libraries, error handling, logging, performance and profiling -- are probably two orders of magnitude better for application languages (Java, C#.. also other langs like Node JS, Rust, Go) than for stored procedures & embedded database code.
Given lifetime maintenance costs are 4-10x that of initial development, even a "very small" initial project of say 7 days exposing data to customers is likely to incur large lifetime cost differences.
On the development side I would expect 3-4x productivity difference to use reasonable application-language tools & a modern framework (Spring Boot, GraphQL or somesuch). On the customer side I would expect things to be much easier to consume the data & far less disruption (since the API will be able to be stable).
Those claiming there would be no development-side costs to expose SQL are probably omitting the cost of trying to implement security rules, trying to patch datamodel issues, and trying to fix security problems after-the-fact.
How much is it going to cost to write a custom DB connection proxy to read the database wire protocol, parse SQL queries and block security concerns at the SQL query level? Because if you allow a raw SQL connection and DB rules are inadequate (which we pretty much know they are), that's what your security fallback will be.
Recommendation
Do yourself and your customers a favour, get out a GraphQL framework and build a logical model.