I am wondering is it possible to create a PowerApp mobile app which can function without internet connection and perhaps store data from the database (i.e SharePoint List or SQL Database) locally on the device in case the app needs to be used offline ?
Yes, it is possible to develop offline-capable canvas apps.
Here is what you can do:
- Determine when an app is offline, online, or in a metered connection by using the Connection signal object.
- Use collections and leverage the LoadData and SaveData functions for basic data storage when offline.
Microsoft documentation: Develop offline-capable canvas apps
There are some limitations with using offline capabilities. You can check it in documentation and references given above.