While I’m not sure it can be considered ‘mainstream’, Firebird (www.firebirdsql.org) is well-established and can run with a server process as well as without one.

I can totally vouch for SQLite in this case, however.