Dapper micro ORM, database agnostic and MySql Guid type

I a开发者_如何学编程m experimenting Dapper on a pet project. I am using SQLite to run all the tests and MySql for "production". However I am not sure how to best use Dapper to handle database agnostic situation.

The particular problem I am having is with MySql which doesn't support Guid type for primary key therefore I am using varchar(40) as the type (SQLite supports unique identifier which is a guid). So here comes the problem if I have a generic repository as below I would get into trouble when trying to select from MySql database. Because the type of Id property is Guid and Dapper will throw "Error parsing column 10" because varchar type doesn't match guid type.

If I change the Id property from guid to int then the raw sql inside the GetById would be even trickier which I am not sure how to even write. It will be something like, 1. start transaction, 2. insert, 3. select last inserted id and return it. So am I going to go with if database type is mysql, then use last_insert_id, or if it's sqlite then use last_insert_rowid? Because the raw sql syntax would be quite different from database to database...

public IEnumerable<T> GetById(Guid id) //convention: Id is always of type Guid.
{
  return UnitOfWork.DbConnection.Query<T>(
      string.Format(
        "select * from {0} where Id = @Id", typeof (T).Name), new {Id = id});
}

Other examples would be to limit number of rows returned (especially for paging) and so on. So how am I going to write database agnostic raw sql queries with dapper? And maybe in my situation Dapper isn't suitable? Perhaps I should use the same old NHibernate here. Any suggestions? Am I doing it wrong? Thanks!

Try to use CHAR(36) as the datatype for the primary key in MySQL, this get transalted to Guid by the MySQl Connector - I am using MySQL connector version 6.3.4. Works with Dapper as well.

Two questions

1. If all you're doing are unit tests then you shouldn't connect to database in the first place at all.

2. If you're doing integration (and any other beyond that) why don't you rather run tests on the production DB version because some particularities may make your test succeed but production code will fail. Integration tests are supposed to be run on similar configuration as production otherwise they're not relevant.