Creating a SQLite Connection


SQLite is a light-weight database that's easy to use and simple to connect to. Learn how to make your own SQLite database in this lesson.

Video recorded using: Ignition 8.0


(open in window)

[00:00] In this lesson we'll take a look at creating SQLite database connections. SQLite is a lightweight self-contained SQL Database. It's designed to be embedded into other programs. This differs from Client-Server Database Management Systems like Postgres and MariaDB, which are standalone programs. What's nice about SQLite is that you don't need to install any separate software to use it with your Gateway. Once you have Ignition installed, you can create a connection and get started. Speaking of getting started, I'm looking at my Gateway. I am under the config section here, under databases and connections. I do have a MySQL database connection, but we're going to ignore that. And instead, I'm going to create a SQLite Database. So I'll click on the create new database connection link. And from my list of options here, I'm going to select the SQLite option. I'll click next. And let's give this connection a name here, much like other database connection types and ignition. We need to provide a connect URL. Unlike other connection types, we simply need to provide a file path to either an existing SQLite Database file or provide a path to a nonexistent file, which will cause this connection to create the database for us. Now off-screen here, I have a folder I created inside of my Ignition Installation Directory. I simply called it My Databases. It's empty right now, but I'm going to have the SQLite Database Connection, create the database inside of this folder. Now I'm going to select this path and copy. We can move this out of the way for now. I'll head back to the connect URL and I'm going to replace everything after the 'JDBC:SQLite' part here. Just got a backspace so that you can see what I did, and I'm going to paste that path I just copied. Now you'll notice the backslashes here. I'm using a Windows Operating System, obviously. So backslashes are used in between folders and files, but the driver would work here with forward slashes as well. As long as the folders along the path exist. Now we do need to point this URL to a file. So I'm going to add a backslash and then type in the name of the file I want to create. So how about Test.DB. Looking at the username and password here, we don't use any authentication for SQLite, so I'll leave those blank. Be mindful of storing sensitive information here in these connections. Let's scroll down and create the connection. And we can see it's valid and it's up and running. If we head back to our directory, we can see that the database was created. So now we can use the SQLite Database Connection to start storing things. It works just like any other SQLite Database Connection, in that you can create tables in here, and start writing queries. We can also use this alongside other Ignition features such as named queries or the alarm journal. Now let's try that one more time. I'm going to create another database connection and just like last time I'll select a SQLite. We'll hit next again. And you may have noticed under these examples here, there's this memory example. Let's take a look at that. So first of all, let's give this a name here. So how about in-memory? So one of the things you can do because SQLite is such a lightweight database, is that instead of creating the database as a file that sits on the file system, the gateway can actually create the database and keep it in-memory. So we can simply select this example here. I'll go and copy that. And I'm going to replace what I have up here, with what I just copied. Now in this version of the software I'm using there's a slight typo in the example. We need a colon at the end, which I'll just add here. And of course we'll take care of that example in future versions. But I'll go ahead and scroll down now. I'll create the connection. And now we have yet another valid database connection. This time, no file. The database just lives in the gateway's memory. Now as you've probably guessed by creating a database that sits only in-memory. Once you shut down your gateway or restart your gateway, all of the entries inside of that database will be gone. So you'd really only want to use this in-memory option for temporary things. You wouldn't store a longterm records inside of this. Now let's end with a caveat here, talking about the SQLite connection options. SQLite isn't really designed to compete with Client Server Style Databases. It's more designed for smaller scale interactions and applications. If you're just getting started with ignition or you're working on a demonstration or proof of concept, then it's a fine database to use. However, in a production environment, we don't recommend you use this as a historian database. Lots of simultaneous queries, such as those generated by our historian systems, our alarm journals and our audit profiles end up straining the SQLite Connection quite a bit. In those cases, you're going to want to use a stand-alone Client-Server Style SQL Database. Instead this option is ideal if you want it to store non-production data. Things like user preferences or configurations made during runtime. Say you have an application where users can enter values in, and you needed to store those temporarily. Instead of writing those values into tags, you could insert the values into a table, that's just sitting in a SQLite Database.

You are editing this transcript.

Make any corrections to improve this transcript. We'll review any changes before posting them.