Testers Needed - SQLdeLite

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:

  1. 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.

  2. 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.

  1. 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.

  1. 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!

  1. 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!

Looks good. A painless way to get to prepared statements. I particularly like #5 where I can put the substitutions in-line.

Forgot to mention if you have trouble running it because of CubeSQL just delete the DatabaseCubeSQL class. You need the CubeSQL plugin in order to use it.

Looks similar to ActiveRecord from Bob Keeney, right?

Theres a small amount of overlap with the Insert capabilities. With SQLdeLite you still write your queries, it just makes it better. You have total control over it and it helps you manage your schemas from one app version to the next. You don’t generate code although I guess you could for the classes if you wanted. It’s not designed for that and the class-ORM features are totally optional.

Looks very interesting, look forward to see how it works.

ActiveRecord is an ORM that hides lot of the SQL from the developer. Not all but lots of it. This doesnt hide the SQL but enhances the SQL that the developer writes. Or at least that is what I have gotten from looking at it.

You got it. It’s a low level add-on to the existing classes to make them better. It doesn’t try to replace SQL at all.

And I thought I’d be gaming tomorrow for a change!

Small update, you no longer need to pass in the primary keys for Insert statements. So it’s now:

Dim newUser As New User(myDb)
newUser.Username = “testuser”
newUser.Password = “hashedPassword”

Thanks to all our testers who provided feedback.

very interesting! :slight_smile:

I’m embarrassed to say it took me until today to really give this a good lookover. Great stuff Phillip. Thank you for sharing this.

It has been open sourced. See https://github.com/1701software/SQLdeLite

Thanks Markus for pointing that out. We have some plans to make a commercial SQLdeLite+ product that builds on it. The base that you see today will remain open source for at least SQLite and CubeSQL.

Are there any speed comparisons to for example ActiveRecord?

I have not done any comparisons. These two libraries aim to do two different things. ActiveRecord uses class bindings to represent the functions you need for each table. You either have to build these yourself or use a generator tool like ARGen.

SQLdeLite does not have any mappings to your tables unless you want schema comparison and easy inserts. It uses introspection and database-specific queries to identify what needs to occur. Performance is maximized by caching the results of the introspection so it only occurs once.

I like to write SQL. I do not want a tool to abstract me from the SQL. However I don’t want to spend a lot of extra time creating pointless prepared statement bindings.

ActiveRecord turns a table into an object (think class). You can add certain event handlers to the objects (before save, after update, etc) that will run and do something automagically for you. You can also have methods that are specific to a specific instance of an object or could be a generic method to object class.

I can write basic SQL. But when you get into inner/outer joins, subselects, etc it goes over my head. By writing code like a programmer does to an object(s) I dont have to deal with SQL with AR. If you are a SQL person (like it, good at writing it, etc) you may or may not like AR as it obfuscates it most of the time.

I havent tried SQLdeLite yet. Hopefully soon.