Mindscape Blog » Blog Archive » Using a LightSpeed model against multiple databases

Using a LightSpeed model against multiple databases

LightSpeed has supported multiple databases since those early days when it was carved on clay tablets and shipped by cart to eager customers across Sumeria. And it’s always been easy to use the same model in a different database: you just change this:

context.ConnectionString = "Data Source=store.db3";
context.DataProvider = DataProvider.Sqlite3;

to this:

context.ConnectionString = "Data Source=localhost;Initial Catalog=store;Integrated Security=True";
context.DataProvider = DataProvider.SqlServer2005;

In most cases — basically unless you have data type portability issues or provider-specific quirks such as different transaction support — you’ll be able to keep using your existing code without change from one database engine to another.

The LightSpeed designer extends that portability to design time. By changing the connection string and data provider on a model, and choosing Update Database, you can rapidly create much of your schema on the new database, rather than having to manually translate the SQL CREATE scripts from one dialect to another. In fact, this helps with portability of runtime code as well: the designer knows how to map the same CLR type (say Guid) into different SQL data types for different databases, so you’ll rarely run into problems with uniqueidentifier vs. char(36) or bit vs. tinyint. The schemas generated by the designer aren’t always perfect, but they’re a huge leg up. (The forthcoming migrations feature in LightSpeed 3 will allow you to take greater control of script generation, without sacrificing portability.)

The runtime support for multiple databases shouldn’t be too surprising — after all, one of the benefits of using an object-relational mapper is that it abstracts away the differences between databases. In fact, even Microsoft’s much-maligned Entity Framework allows you to move a model from one database to another, and you hardly need to copy and paste large chunks of XML or craft connection strings that look like Perl code at all. Design-time support however is less common, so if you need to develop schemas for multiple back ends, or if you’re prototyping against one engine but aren’t committed to it for your final product, LightSpeed would well be the product for you!