Unit-testing sql interactions

Ruby
1

I’m relatively new to SQL, and I’d like to get my hands a bit dirtier.
So I’m writing a toy application (in Ruby, of course) to let me get
some more experience. And I’m avoiding high-level SQL libraries
(e.g. ActiveRecord) because I don’t think I’d learn as much about SQL
that way.

The application will use MySQL, but I don’t feel comfortable writing
code without unit tests, and I’d rather not use MySQL there. So the
way I’d imagine this working is going through some sort of
database-independent layer, which I can either hook up to MySQL or
some sort of in-memory database.

Questions:

  1. Is that a reasonable way to proceed?

  2. What’s the right database-independent layer? ruby-odbc?

  3. What should I use for the fast in-memory database for unit testing?
    Googling suggests that SQLite is a possibility, but it’s not clear
    to me that it’s the best choice for this task.

Thanks,
David C.
[email protected]