Simple Talk is now part of the Redgate Community hub - find out why

Using SQLite with PowerShell and SQL Server

When you combine PowerShell and SQLite, you can perform powerful magic. Phil Factor is in awe of SQLite and gives a brief demonstration of how easy it is to use. Just to encourage anyone who is unfamiliar with the database, he includes a giant-sized SQLite version of the old PUBS database that the first generation of RDBMS developers cut their teeth on.

SQLite

 Although I dearly love using SQL Server, I wouldn’t use it in every circumstance; there are times, for example, when just isn’t necessary to use a Server-based RDBMS for a data-driven application. The open-source SQLite is arguably the most popular and well-tried-and-tested database ever. It is probably in your phone, and used by your browser. Your iTunes will use it. Most single-user applications that need to handle data will use SQLite because it is so reliable and easy to install.

sqlite0

 It is specifically designed as a zero-configuration, embedded, relational database with full ACID compliance, and a good simple dialect of SQL92. The SQLite library accesses its storage files directly, using a single library, written in C, which contains the entire database system. Creating a SQLite database instance is as easy as opening a simple cross-platform file that contains the entire database instance. It requires no administration.

 A huge range of utility applications are available to make the creation and administration of databases maintenance of the data easy. In scripts, I can pass tables, and their data, easily between SQL Server and SQLite, using either ODBC, a NET library or a nice, simple command-line file that is reminiscent of SQLCMD. Of course, there is some compromise since there aren’t nearly so many data types in SQLite and, like most NoSQL databases, the data is represented in storage as a string. There are many tools around for working on SQLite databases if you’re not a programmer by inclination.

 There are reasons for using SQL Server instead once your application grows, because the performance and simplicity comes at a price. Although SQLite will happily handle data sizes over a gigabyte, it is limited to a single file. Where you need concurrency, ACID compliance isn’t really enough because it relies on file locks, so write transactions are effectively serialized. There is no access control beyond that provided by the operating system. There is no way of using SQLite in a client-server environment. There is also no built-in way of replicating the data or providing redundancy.

 It is the obvious way of handling data in an application, and for transferring data between applications. It is very portable, easy to use, compact, efficient, and reliable.   Because a SQLite database is a single file, it can be used like XML. You can send it as an attachment, query it, export and import data, all using the SQL language you already know. It is therefore a great tool for the DBA. I use it for storing all the test data for Continuous Integration processes, but it has value wherever you would otherwise have to use XML or  JSON. If you are within the SQL and relational mindset, there is no good technical reason to have to change, since SQLite takes care of all the small stuff and the new breed of NewSQL takes care of the jelly-masses of unstructured data.

 Most applications use the SQLite C API to interface with the SQLite database. Many scripting/programming languages have libraries for accessing the SQLite API. Python has its SQLite3 module, so has Ruby and Haskell. There is the Perl module DBI::SQLite, PHP’s data objects with the SQLite driver. Not only can these languages (and many more) use the SQLite C API to access the SQLite db For .NET programmers, SQLite  presents a number of different ways of accessing it. You can perform just about every operation by using the command-line interface. You can opt for the ADO.NET data provider System.Data.SQLite, which works like other data providers and provides a great deal of functionality. PowerShell has a SQLite provider.  You can use an ODBC and JDBC driver. There is even a JavaScript  port. https://github.com/kripken/sql.js  and a java port https://sqljet.com/  You name it, it can use SQLite.

 There is plenty of information on how to use SQLite. Books abound, such as O’Reilly’s ‘Using SQLite’, Apress’s ‘The Definitive Guide to SQLite’, Sams’s SQLite and a host of others.

SQLite and SQL Server.

If, when you are working with SQL Server or any other mainstream RDBMS, you are using CSV or XML for storing data sets, it is worth considering SQLite instead. SQLite databases are just files, and so are easy to copy and transmit. They can be linked to SQL Server via ODBC, but there are plenty of utilities that allow you to transfer data between SQLite and SQL Server.

 To play around with SQLite, I’ve included with this article a SQLite version of the old Pubs database. This is a very fictional ‘publishers, publications and authors’ database that we used to cut our teeth on before AdventureWorks and NorthWind. I’ve added lots more data to it using SQL Data Generator, and transferred it to SQLite. With the SQLite ODBC driver, you can use it to attach to via MS Access, and Libre Office Base. You can do queries to it via Excel. You can link to it via SQL Server. You won’t need me to tell you how to do all that. We’re going to query it using PowerShell.

A simple query with results.

The most useful way of accessing SQLite from scripts is to use the  ADO.NET provider.

sqlite1

If you want to try out a few queries, here is the diagram for the database

Sending a SQL Query to SQL Server and saving it in a table

We can, of course send a result to SQL Server. (it isn’t too easy to detect the datatype in a SQLite result because everything is basically held as a string.)

You get the point, I’m sure. It is easy to script.

Saving information from the file system into a SQLite database

For our next demonstration of using SQLite, we’ll just do the basis of a source-code system. This routine creates a SQLite database that contains a single table. This will be used to contain a list of all the scriptable objects in a database along with their CREATE scripts. We’ll take this from a directory containing all the scripts.  We could do it from SMO, but that will be a distraction at this stage. In this demo, I’m unapologetically taking the scripts that have been created by SQL Compare.

sqlite2

 The name of the directory is the same as the name of the database, and all the subdirectories are named after the type of object scripted (e.g. Tables, Views, functions and so on.) This makes it easy to gather the information that we need. I’ve chosen to lay this out as a script, just as with the first code sample,  rather than a function because I wanted to show something that you can pick apart in the ISE, examine variables, and try parts out.

 A couple of things to note: if you pass SQLite the name of a file in a directory, and the file doesn’t exist, it creates that SQLite file in that directory. You can copy a SQLite database like any other file. When you insert more than a couple of rows, it is quicker and more sociable to wrap them in a transaction. As with any inserts using a data provider, it is much better and more convenient to add parameters via the method provided by the command object. This deals with messy things such as parentheses and escaping illegal characters.

Conclusions

Grey-muzzle database developers like me tend to think more readily  in terms of relational database structures than data documents such as XML when dealing with any sort of data when we’re writing applications or scripting. Relational Database servers are fine when we’re dealing with gazillions of rows of rapidly changing data, but what if we are dealing with an applications’s requirements for local data of just a few megabytes? There are quite a few small-scale relational databases such as HSQLDB, but SQLite is the obvious choice.

References

  1. pure Java implementation of SQLite
  2. SQLite compiled to javascript
  3. SQLite’s homepage
  4. Simple Talk interviews Richard Hipp, the creator of SQLite
  5. System.Data.SQLite

How you log in to Simple Talk has changed

We now use Redgate ID (RGID). If you already have an RGID, we’ll try to match it to your account. If not, we’ll create one for you and connect it.

This won’t sign you up to anything or add you to any mailing lists. You can see our full privacy policy here.

Continue

Simple Talk now uses Redgate ID

If you already have a Redgate ID (RGID), sign in using your existing RGID credentials. If not, you can create one on the next screen.

This won’t sign you up to anything or add you to any mailing lists. You can see our full privacy policy here.

Continue