For anything that starts as a side project, SQLite covers it. The moment you need to ask this question the answer is usually "not yet." Ship first, migrate when it actually hurts
Most software is stateful and needs to persist state across restarts, so I would argue that one needs at least SQLite.
On SQLite being safe default: in practice it means supporting multiple databases, say SQLite and Postgres, this is more complicated that supporting just Postgres. As soon as a project leaves localhost and enters cloud development you need talk to a database over network, which warrants MySQL or Postgres.
Which is more complicated: supporting a docker container with mysql or Postgres for local development OR supporting multiple databases in the project?
Of course, the answer could be “it depends”, I but I would not call SQLite a default choice. It would be if you are writing desktop or mobile app, but for anything like a web app it’s a questionable choice.