We [1701 Software] plan to release a new product in the next few days called SQLdeLite. It is designed around our experience with various database systems and our belief that heavy ORM’s are not good. They tend to lock you in to a very particular way of using your databases and generally are not well optimized.
SQLdeLite is a low level SQL database abstraction layer. It’s primarily focused around the things that make managing and querying your databases difficult or time consuming. Here were the design goals:
-
Be a drop in replacement for the existing SQLiteDatabase and CubeSQLServer classes. All existing properties and methods should work out of the box. You will not have to change any of your existing code to start using SQLdeLite. MySQL and PostgreSQL coming by end of the week.
-
Currently if you wish to properly query a database you need to parameterize the query using prepared statements. Prepared statements provide both speed optimizations and protect you from SQL injection attacks. However actually doing this for each database is a chore. SQLdeLite provides query helpers in the form of database methods (SQLdeLiteSelect and SQLdeLiteExecute) that rapidly speed up your development. You can now do something like:
myDb.SQLdeLiteSelect(“SELECT * FROM Users WHERE Username = #?# AND Password = #?#”, txtUsername.Text, txtPassword.Text)
This would return a RecordSet from your database. Behind the scenes it used prepared SQL statements appropriate for that database server to return the data.
- Assist with creating and managing your table schemas through database migrations. By marking classes as an instance of a SQLdeLite.Table object you gain immediate schema capabilities. Public non-computed properties that are strings/integers/doubles will be compared with the table schema. If the table does not exist a “CreateTableSchema” event is raised in the database object.
If the table is missing particular fields or the data types do not match then a “UpdateTableSchema” event is raised. You can choose to update your schema or just return True. Again, the goal is flexibility and power, it does not force you to do anything. The table and class schemas are hashed and stored in the database object so subsequent queries do not involve introspection.
There is an example provided of using more complex types like dates. However the intent is to keep it low level. You should save Date.SQLDateTime as opposed to just Date. Perhaps use these classes as super’s for more advanced classes with computed properties? Or use them in conjunction with your existing classes solely for the purpose of schema migrations and management.
- If you are like us you spend a lot of time inserting data into your database. If you have an object that has been mapped to a SQLdeLite.Table then inserting is super easy. For instance you can do:
Dim newUser As New User(myDb, “ID”) // ID being the primary key column
newUser.Username = “testuser”
newUser.Password = “hashedPassword”
newUser.Insert(“ID”) // ID being the primary key column
This is a nice little helper to save you some time. SQL Insert statements are largely the same and theres no point wasting time writing methods to insert every known possibility. SQLdeLite saves you time!
- It would be nice if we didn’t have to pass in all the variables at the end of the method parameters. It gets a little unwieldy if you have a query with multiple ?'s and at the end of the Execute method you have: “variable1, variable2, variable3, variable4, variable5, etc.”
So we provide the ability to use the object itself to update the database. Here’s an example
newUser.Execute(“UPDATE Users SET Password = #password# WHERE Username = #username#”)
So while we don’t like full on conventional ORM’s we can use the schema info we already have to enhance the experience. We do this without boxing you into any corners, paradigms, etc.
SQLdeLite does not write your queries for you. It just makes using SQL significantly more enjoyable. Download here: http://www.1701software.com/downloads/SQLdeLite_1.0_beta.zip
Anyone who provides any meaningful feedback by next Monday on this thread or via email to phillip@1701software.com will receive a free license. Thanks for your time and assistance!