Hibernate certainly can work in a small application through e.g. Quarkus Native you can easily have even a CLI app that runs just a few milliseconds.
Nobody stepped up so far to provide a SQLite dialect, but you will be happy to hear that with Hibernate 6 you can use the hibernate-community-dialects which contains a dialect for SQLite that is maintained by the community.