I have not used this particular service (i have used Oracle) but Yes you should be able to use these databases with AppSheet. Oracle is a relational database like SQL Server or MySQL, just more powerful - at least its enterprise versions are - and it follows many of the same syntax and protocols. There are some differences.
Keep in mind that although the database itself is free, AppSheet still has to support connecting to the database from its servers. That part is NOT free. Your apps will still require a plan that allows database connectivity. Those plans are now priced per user - I think $20/user for the cheapest.
Also, check the plans for the Oracle database service. The connectivity itself might be free but there is likely charges for the AMOUNT OF DATA stored within the database. Free for small databases but as they grow, charges are incurred when data reaches a certain threshold. This is as it should be and is in line with many other cloud based services.
And it is indeed the case. I just checked their site from the your link above and they have this note with all of the limits the free plan has of which many I don’t understand the meaning:
"Use your Always Free resources as long as you want with no time
constraints—subject only to the capacity limits noted."
Is there some technical reason why you are considering a database as the backend data store?