SQLite3 is an awesome cross-platform relational database. Some of the major benefits of SQLite include its simplicity and easy management. Everything is stored in a single file and there is no authentication.
One of the big drawbacks with a SQLite database is that there is a global write-lock. Only one write operation can occur at any time. This can create a bottleneck for very write-intensive applications.
SQLite is used in production in many embedded and web applications. For example, you can use SQLite in combination with the Sinatra web application framework to persist data. If you are interested in learning how to use Sinatra, check out my Sinatra Tutorial.
Ruby is an incredibly productive and useful language. Combining Ruby with SQLite3 is a natural fit that opens many possibilities. Add in a simple web framework like Sinatra and you have an incredibly powerful but simple set of tools for building a web application.
If you need a database-agnostic library, something that you can use with SQLite and then easily configure to start using MySQL without rewriting all of your code, you want to use an ORM. ORM stands for Object-Relational-Mapping and the most popular one for Ruby is ActiveRecord.