Watch + SQLite

You could use CoreData for this.

So does SQLite exist on the Apple Watch?

Yes.

How can I create an SQLite database-based app in Xcode?

I don’t understand this question; the watch doesn’t really have document-based apps.

Is this static data that’s always bundled within your app? Or something that the user generates? Or initial data that the user might modify?

Share and Enjoy
—
Quinn “The Eskimo!”
Apple Developer Relations, Developer Technical Support, Core OS/Hardware

let myEmail = "eskimo" + "1" + "@apple.com"

What I mean is that there is a lot of data. I am designing a language-learning app where I have in a table a word in one language and then the same word in English. That data is static, but I will also want to store other user data that maps to these words such as whether the user has already seen this word.

At the moment I am thinking of using two plists, so that I read both files in when I open the app, but I will only have to save the user-related data upon exit.

But I want to make sure this is the appropriate way to store this data. I have a feeling SQLite will be better and offer quicker data retrieval, etc. but I haven't been able to find documentation on how to create an SQLite database within a Watch app. Thank you.

Storing thousands of entries in a property list is generally not a great idea. It’ll work, but you can only access a property list by loading the whole thing into memory, which is not a good option on memory-constrained platforms like the watch.

Storing this is SQLite probably makes sense, although it depends on how you access the data. For example, imagine a

Word

Row  English            German
---  -------            ------
1    butcher            Metzger
2    baker              Bäcker
3    candlestick maker  Kerzenmacher
… and so on …

You might want to create indexes over this table so that you can search the English column for “maker” and quickly find row 3, or search the German column for “back” and quicker find row 3. SQLite can handle this sort of thing, but it’s definitely a more advanced use case.

That data is static, but I will also want to store other user data that maps to these words such as whether the user has already seen this word.

When dealing with large databases, it’s important to separate the read-only stuff that’s part of your app (or downloadable content, but downloadable by all users) and the read/write stuff that’s specific to your current user. Without this, you might end up trying to back up your large read-only data set, which will run afoul of the iOS Data Storage Guidelines.

In this case, I’d recommend a second database with a

HasSeen

I haven't been able to find documentation on how to create an SQLite database within a Watch app.

There’s nothing special about a watchOS here. The techniques you use for this on iOS will work just fine on watchOS.

The only wrinkle that watchOS brings to the table is the need for syncing. If you want the user’s progress on their watch to be reflected on their phone, and vice versa, you’ll need a syncing strategy. Then again, the same could be said of syncing between the users phone and iPad.

This, btw, is another good reason to separate out this read/write data.

Share and Enjoy
—
Quinn “The Eskimo!”
Apple Developer Relations, Developer Technical Support, Core OS/Hardware

let myEmail = "eskimo" + "1" + "@apple.com"

Would you be able to provide a bit of guidance as to how to package a database with your app.

Should I be creating a sqlite file externally and then importing it into my package. Or do I still need to in Xcode/Swift open that sqlite file for the raw data and then use SQL to create the new table. I'm a bit confused how it all works.

You are always going to need SQL to access the data. For the static data, you would likly have some build-time script to generate the static SQL table. That table would then be a read-only resource inside the app bundle just like an image or a plist file would be. The updates would be a dynamically-generated SQL table. In that case, you would also have to worry about syncing as eskimo says.

Hi john,

Thanks for that. For the first part, the "build-time script to generate the static SQL table", do you have any examples/resources that show this. For example, if I have a table with 10,000 rows, it doesn't make sense for me to have in my code 10,000 INSERT INTO statements does it? Can I just add an sqlite file to my package like you would with a plist, and then connect to it?

Would you be able to provide a bit of guidance as to how to package a database with your app.

There’s two parts to this:

• How the SQLite database should be embedded in your app? (A)

• How to build that SQLite database? (B)

With regards A, you wrote:

Can I just add an sqlite file to my package like you would with a plist, and then connect to it?

Absolutely. Your goal should be to minimise the amount of work you do at runtime on each client device, which means you want to push as much work as possible to build time. Packaging a ready-to-roll SQLite database in your app is a good way to do this.

With regards B, the mechanism for creating the database kinda depends on the source of your input data:

• If it’s something you hand craft, you could use an SQLite app to set it up.

• If you get it in some serialised form, like a

  .txt

  file, you’d typically write a script to parse that data and output an SQLite database, either directly (using the SQLite API) or as SQL commands that you can feed into the

  sqlite3

  command-line tool.

Share and Enjoy
—
Quinn “The Eskimo!”
Apple Developer Relations, Developer Technical Support, Core OS/Hardware

let myEmail = "eskimo" + "1" + "@apple.com"

Thanks for your guidance so far, eskimo 🙂

So I discovered SQLite.swift on github, and I was able to create a read-only database for my static data and then create a read-write database to store user-related data. Now, I've created the connections to the database all in my ExtensionDelegate file under the applicationDidFinishLaunching() function.

But if all the logic of my app is in my other views and classes, how do I give those files access to the database connection I've just created? I tried to make my Connection a global variable, but you aren't allowed to make them global like other variables. So there must be a more elegant way of doing it?

Yes, but that is a build-time operation. Then you package the SQLite database in with your app just like other resources.

When you open that file at runtime, make sure to open it read-only.

You can do wueries in SQLite that span multiple files. The syntax is a little tricky id I remember correctly.

Don’t get tripped up with concurrency issues. In the MVC (Model View Controller) paradigm, your database is part of the model. It would be best to serialize your database operations. SQLite does support concurrent connections, but I think you would probably need multiple connections for that. If you serialize, you’ll be fine.

By serialise do you mean wrap the database connection into its own Class. create an object of that class so everything else can have access to it?

No. Serializing access is the solution to the problem you have just described. Serializing involves using something like a serial queue to ensure that the database connection is only being used by one section of code at a time. A traditional database would support multiple database connections. As an embedded, file-based database, SQLite doesn't have the same concurrency guarantees. Rather than using your app to explore the limits of SQLite's support for concurrency, I suggest just serializing access.

Hi eskimo, going back on this point

"When dealing with large databases, it’s important to separate the read-only stuff that’s part of your app (or downloadable content, but downloadable by all users) and the read/write stuff that’s specific to your current user. Without this, you might end up trying to back up your large read-only data set, which will run afoul of the iOS Data Storage Guidelines.

In this case, I’d recommend a second database with a

HasSeen

By seperating the two databases, I now need to do a left outer between my reference table and user data table. But the tables belong to seperate databases, and I don't know how to do that in sqlite.swift. For simplicity, shouldn't I just keep everything in one table (from one database)?

For simplicity, shouldn't I just keep everything in one table (from one database)?

There’s different levels of simplicity. Making a copy of the database and then mutating that copy will make your SQLite code simpler, sure. However, it introduces other problems:

• You have to make sure you don’t trip over the iOS Data Storage Guidelines.

• If the database is huge, you could potentially run out of disk space making that copy, making your app non-functional for users with limited disk space.

• You have to decide how to handle upgrades. If the user’s data is merged into your default data, you need a way to extract that data from your current database and apply it to the new database. I’m sure that’s feasible, but is it easier than separating the databases?

Share and Enjoy
—
Quinn “The Eskimo!”
Apple Developer Relations, Developer Technical Support, Core OS/Hardware

let myEmail = "eskimo" + "1" + "@apple.com"