SQLPro For SQLite Lite

(Please consider sponsoring us on Patreon 😄)

Windows

The SQLiteStudio tool is a free GUI tool for managing SQLite databases. It is free, portable, intuitive, and cross-platform. SQLite tool also provides some of the most important features to work with SQLite databases such as importing, exporting data in various formats including CSV, XML, and JSON. Trusted Mac download SQLPro for SQLite Read-Only 1.0.44. Virus-free and 100% clean download. Get SQLPro for SQLite Read-Only alternative downloads. SQLPro for SQLite application provides you with the capacity to see your database and the quest for a given line using the apparatus’ worked as a part of Filter highlight. It will permit you to rapidly execute inquiries using your database and perspective outcomes and the mistake/achievement messages, if there are any.

Downie 4. Our latest release (3.12.1) for Windows:

Windows PortableApp

There is a PortableApp available, but it’s still the previous (3.12.0) release version. It should be updated to 3.12.1 over the next few days:

Note - If for any reason the standard Windows release does not work(e.g. gives an error), try a nightly build (below).

Nightly builds often fix bugs reported after the last release. 😄

macOS

Our latest release (3.12.1) for macOS:

Homebrew

If you prefer using Homebrew for macOS, our latest release can be installed via Homebrew Cask:

Nightly builds

Download nightly builds for Windows and macOS here:

Linux

DB Browser for SQLite works well on Linux.

Arch Linux

Arch Linux provides an up to date version

Install with the following command:

Fedora

Install for Fedora (i386 and x86_64) by issuing the following command:

openSUSE

Debian

Note that Debian focuses more on stability rather than newest features. Therefore packages will typically contain some older version, compared to the latest release.

Update the cache using:

Install the package using:

Ubuntu and Derivatives

Stable release

For Ubuntu and derivaties, @deepsidhu1313provides a PPA with the latest release here:

To add this ppa just type in these commands in terminal:

Then update the cache using:

Install the package using:

Ubuntu 14.04.X, 15.04.X, 15.10.X and 16.04.X are supported for now (untilLaunchpad decides to discontinue building for any series).

Ubuntu Precise (12.04) and Utopic (14.10) are not supported:

  • Precise does not have a new enough Qt package in its repository by default,which is a dependency
  • Launchpad does not support Utopic any more, which has reached its End ofLife

Nightly builds

Nightly builds are available here:

To add this ppa, type these commands into the terminal:

Then update the cache using:

Install the package using:

Other Linux

On others, compile DB4S using the instructionsin BUILDING.md.

FreeBSD

DB Browser for SQLite works well on FreeBSD, and there is a port for it (thanksto lbartoletti 😄). DB4S can be installedusing either this command:

or this command:

Snap packages

Snap Nightlies

Snap Stable

Update note: This tutorial has been updated to Xcode 9, iOS 11, and Swift 4 by Nikolas Burk. The original tutorial was written by Chris Wagner.

Curious about using the SQLite C APIs from Swift? Then read on…

This SQLite with Swift tutorial shows you how to work with the popular database platform from within Swift. In the world of software development, it doesn’t take long before you need to persist app data. In many cases, this comes in the form of data structures. But how do you store it effectively — and efficiently?

Fortunately, some great minds have developed solutions for storing structured data in databases and writing language features to access that data. SQLite is available by default on iOS. In fact, if you’ve used Core Data before, you’ve actually already used SQLite since Core Data is only a layer on top of SQLite that provides a more convenient API.

Throughout this SQLite with Swift tutorial, you’ll learn how to perform the following database operations:

  • Create and connect to a database
  • Create a table
  • Insert a row
  • Update a row
  • Delete a row
  • Query the database
  • Handle SQLite errors

After learning how to perform these fundamental operations, you’ll see how to wrap them up in a more Swift-like manner. This will let you write abstraction APIs for your apps so that you can (mostly) avoid the pain of working with the SQLite C APIs! :]

Finally, I’ll briefly cover the popular open source Swift wrapper SQLite.swift to give you a basic understanding of how underlying frameworks work within a wrapper.

SQLPro For SQLite Lite
Note: Databases, and even just SQLite on its own, are massive topics to cover, so they’re mostly out of scope for this tutorial. It’s assumed that you have a basic understanding of relational database ideology and that you’re primarily here to learn how to use SQLite in conjunction Swift.

Getting Started

Download the starter project for this SQLite with Swift tutorial and open SQLiteTutorial.xcworkspace. From the Project Navigator open the Tutorial playground.

Note: The project is packed up in an Xcode workspace since it uses the SQLite3 dependency as an embedded binary. This binary contains all the functionality for the SQLite code you’re going to write in this tutorial.

Notice that your Playground is configured to run manually instead of autmatically:

This means it will only execute when you explicitly invoke the execution by hitting the “Play” button.

You might also see a destroyPart1Database() call at the top of the page; you can safely ignore this, since the database file is destroyed each time the playground runs. This ensures all statements execute successfully as you move through this SQLite with Swift tutorial.

Your playground will need somewhere to write SQLite database files on your file system. Run the following command in Terminal to create the data directory for your playground:

Why Should I Choose SQLite?

True, SQLite isn’t the only way to persist data on iOS. Besides Core Data, there are lots of other alternatives for data persistence, including Realm, Couchbase Lite, Firebase, and NSCoding.

Each of these has their own pros and cons — including SQLite itself. There’s no silver bullet for data persistence, and as the developer, it’s up to you to determine which option outweighs the others based on your app’s requirements.

SQLite does have some advantages:

  • Shipped with iOS so it adds no overhead to your app’s bundle
  • Tried and tested; version 1.0 was released in August 2000
  • Open source
  • Familiar query language for database developers and admins
  • Cross-platform

The cons of SQLite can be terribly subjective and opinionated, so we’ll leave the research on that up to you! :]

The C API

This part of the SQLite with Swift tutorial will walk you through the most common and basic SQLite APIs. You’ll soon realize that wrapping the C API in Swift methods would be ideal, but sit tight and work through the C code first; you’ll do some wrapping in the second part of this tutorial.

Opening a Connection

Before doing anything, you’ll first need to create a database connection.

Add the following method under the Getting Started section of the playground:

The above method calls sqlite3_open(), which opens or creates a new database file. If it’s successful, it returns an OpaquePointer; this is a Swift type for C pointers that can’t be represented directly in Swift. When you call this method, you’ll have to capture the returned pointer in order to interact with the database.

Many of the SQLite functions return an Int32 result code. Most of these codes are defined as constants in the SQLite library. For example, SQLITE_OK represents the result code 0. A list of the different result codes can be found on the main SQLite site.

To open the database, add the following line to your playground:

Press the Play button to run the playground and watch the console output. If the console isn’t open, press the button to the left of the play button:

If openDatabase() succeeds, you’ll see some output like the following:

Where username is your Home directory.

Creating a Table

Now that you have a connection to a database file, you can create a table. You’ll work with a very simple table to store contacts.

The table will consist of two columns; Id, which is an INT and a PRIMARY KEY; and Name, which is a CHAR(255).

Add the following string, which contains the SQL statement necessary to create the table:

Note that you’re using Swift 4’s handy multine syntax to write this statement!

Next, add this method that executes the CREATE TABLE SQL statement:

Going over this step-by-step:

  1. First, you create a pointer to reference in the next step.
  2. sqlite3_prepare_v2() compiles the SQL statement into byte code and returns a status code — an important step before executing arbitrary statements against your database. If you’re interested, you can find out more here. You check the returned status code to ensure the statement compiled successfully. If so, the process moves to step 3; otherwise, you print a message noting the statement could not be compiled.
  3. sqlite3_step() runs the compiled statement. In this case, you only “step” once as this statement has a single result. Later in this SQLite with Swift tutorial you’ll see when it’s necessary to step multiple times for a single statement.
  4. You must always call sqlite3_finalize() on your compiled statement to delete it and avoid resource leaks. Once a statement has been finalized, you should never use it again.

Now, add the following method call to the playground:

Run your playground; you should see the following appear in your console output:

Now that you have a table, it’s time to add some data to it. You’re going to add a single row with an Id of 1 and Name of “Ray”.

Inserting Some Data

Add the following SQL statement to the bottom of your playground:

This might look a little strange if you haven’t had much SQL experience. Why are the values represented by question marks?

Remember above when you used sqlite3_prepare_v2() to compile your statement? The ? syntax tells the compiler that you’ll provide real values when you actually execute the statement.

This has performance considerations, and lets you compile statements ahead of time, which can be a performance gain since compilation is a costly operation. The compiled statements can then be re-used over and over with different values.

Next, create the following method in your playground:

Here’s how the above method works:

  1. First, compile the statement and verify that all is well;
  2. Here, you define a value for the ? placeholder. The function’s name — sqlite3_bind_int() — implies you’re binding an Int value to the statement. The first parameter of the function is the statement to bind to, while the second is a non-zero based index for the position of the ? you’re binding to. The third and final parameter is the value itself. This binding call returns a status code, but for now you assume that it succeeds;
  3. Perform the same binding process, but this time for a text value. There are two additional parameters on this call; for the purposes of this tutorial you can simply pass -1 and nil for them. If you’d like, you can read more about binding parameters here;
  4. Use the sqlite3_step() function to execute the statement and verify that it finished;
  5. As always, finalize the statement. If you were going to insert multiple contacts, you’d likely retain the statement and re-use it with different values.

Next, call your new method by adding the following to the playground:

Run your playground and verify that you see the following in your console output:

Challenge: Multiple Inserts

Challenge time! Your task is to update insert() to insert an array of contacts.

As a hint, you’ll need to reset your compiled statement back to its initial state by calling sqlite3_reset() before you execute it again.

Solution Inside: Solution — Insert multiple rows

Querying Contacts

Now that you’ve inserted a row or two, it sure would be nice to verify that they’re really there! :]

Add the following to the playground:

This query simply retrieves all records from the contact table. Using a * means all columns will be returned.

Add the following method to perform the query:

Taking each numbered comment in turn:

  1. Prepare the statement;
  2. Execute the statement. Note that you’re now checking for the status code SQLITE_ROW, which means that you retrieved a row when you stepped through the result;
  3. It’s time to read values from the returned row. Given what you know about the table’s structure and your query, you can access the row’s values column by column. The first column is an Int, so you use sqlite3_column_int() and pass in the statement and a zero-based column index. You assign the returned value to the locally-scoped id constant;
  4. Next, you fetch the text value from the Name column. This is a bit messy due to the C API. First, you capture the value as queryResultCol1 so you can convert it to a proper Swift string on the next line;
  5. Print out the results;
  6. Finalize the statement.

Now, call your new method by adding the following to the bottom of the playground:

Run your playground; you’ll see the following output in your console:

W00t! It looks like your data made it into the database after all!

Challenge: Printing Every Row

Your task is to update query() to print out every contact in the table.

Solution Inside: Solution — Print all contacts

Updating Contacts

The next natural progression is to update an existing row. You should start to see a pattern emerging.

First, create the UPDATE statement:

Here you’re using real values instead of ? placeholders. Usually you’d use the placeholders and perform proper statement binding, but for brevity you can skip it here.

Next, add the following method to the playground:

This is a similar flow to what you’ve seen before: prepare, step, finalize! Add the following to your playground:

This executes your new method, then calls your previously defined query() method so that you can see the results:

Congratulations on updating your first row! How easy was that? :]

Deleting Contacts

The final step on the path to becoming an SQLite ninja is to delete the row you created. Again, you’ll use the familiar pattern of prepare, step, and finalize.

Add the following to the playground:

Now add the following method to execute the statement:

Are you feeling it now? Prepare, step, and finalize! :]

Execute this new method, followed a call to query(), like so:

Now run your playground and you should see the following output in your console:

Note: If you completed the Multiple Inserts challenge above, it’s likely the output will look a little different to that above due to rows still being present in the table.

Handling Errors

Hopefully, you’ve managed to avoid SQLite errors up to this point. But the time will come when you make a call that doesn’t make sense, or simply cannot be compiled.

Handling the error message when these things happen can save you a lot of development time; it also gives you the opportunity to present meaningful error messages to your users.

Add the following statement – which is intentionally malformed – to your playground:

Now add a method to execute this malformed statement:

Here’s how you’re going to force an error:

  1. Prepare the statement, which will fail and should NOT return SQLITE_OK;
  2. Get the error message from the database using sqlite3_errmsg(). This function returns a textual description of the most recent error. You then print the error to the console;
  3. As always, finalize.

Call the method to see the error message:

Run your playground; you should see the following output in your console:

Well, that’s actually helpful — you obviously cannot run a SELECT statement on a table that doesn’t exist!

Closing the Database Connection

When you’re done with a database connection, you’re responsible for closing it. But beware — there are a number of things you must have performed before you can successfully close your database, as described in the SQLite documentation.

Call the close function as shown below:

Run your playground; you should see a status code of 0 in the right side results view of the playground; this represents SQLITE_OK, which means your close call succeeded.

You’ve successfully created a database, added a table, added rows to the table, queried and updated those rows, and even deleted a row — all using the SQLite C APIs from Swift. Great job!

In the next section, you’ll take what you’ve learned and see how to wrap some of these calls in Swift.

SQLite With Swift

As a Swift developer, you’re probably feeling a little uneasy about what happened in the first part of this tutorial. That C API is a bit painful, but the good news is you can take the power of Swift and wrap those C routines to make things easier for yourself.

For this part of the SQLite with Swift tutorial, click the Making it Swift link at the bottom of the playground to open the playground for this section:

Wrapping Errors

Getting at errors from the C API is a bit awkward as a Swift developer. Checking a result code and then calling another method just doesn’t make sense in this brave new world. It would make more sense if methods that can fail throw an error.

Add the following to your playground:

This is a custom Error enum that covers four of the main operations you are using that can fail. Note how each case has an associated value that will hold the error message.

Wrapping the Database Connection

Another not-so-Swifty aspect is the use of those blasted OpaquePointer types.

Wrap up the database connection pointer in its own class, as shown below:

This looks much better. When you need a database connection, you can create a reference to a more meaningful type of SQLiteDatabase rather than OpaquePointer.

Sqlpro Studio

Sqlpro for sqlite

You’ll notice the initializer is fileprivate; that’s because you don’t want your Swift developers passing in that OpaquePointer. Instead, you let them instantiate this class with a path to the database file.

Add the following static method to SQLiteDatabase as follows:

Here’s what happening:

  1. Attempt to open the database at the provided path;
  2. If successful, return a new instance of SQLiteDatabase;
  3. Otherwise, defer closing the database if the status code is anything but SQLITE_OK and throw an error.

Now you can create and open a database connection using much cleaner syntax.

Add the following to your playground:

Ah, much more Swift like. Here, the attempt to open the database is wrapped in a do-try-catch block, and the error message from SQLite is passed to the catch block thanks to that custom enum you added earlier.

Run your playground and watch the console output; you’ll see something like the following:

Now you can use and inspect the db instance as a proper and meaningful type.

Before moving on to writing methods that execute statements, it would be nice if SQLiteDatabase let you easily access SQLite error messages.

Add the following computed property to SQLiteDatabase:

Here you’ve added a computed property that simply returns the most recent error SQLite knows about. If there is no error, it just returns a generic message stating as much.

Wrapping the Prepare Call

Since you do this so often, it makes sense to wrap it like the other methods. As you move forward and add functionality to the SQLiteDatabase class, you’ll make use of class extensions.

Add the following extension, which will be used by your future methods, to invoke sqlite3_prepare_v2() on SQL statements:

Here you declare that prepareStatement(_:) can throw an error, and then use guard to throw that error should sqlite3_prepare_v2() fail. Just like before, you pass the error message from SQLite to the relevant case of your custom enum.

Creating a Contact Struct

In these examples, you’ll use the same Contact table as before, so it makes sense to define a proper struct to represent a contact. Add the following to the playground:

Wrapping the Table Creation

You’ll knock out the same database tasks as before, but this time you’ll use a “Swifter” approach.

To create a table, you need a CREATE TABLE SQL statement. It makes sense for Contact to define its own CREATE TABLE statement.

Create the following protocol for just that purpose:

Now, extend Contact to provide conformance to this new protocol:

Now you’re able to write the following method that accepts types that conform to SQLTable to create a table:

Here’s a breakdown of what’s happening:

  1. prepareStatement() throws, so you must use try. You’re not doing this in a do-try-catch block because this method itself throws, so any error from prepareStatement() will simply be thrown to the caller of createTable();
  2. With the power of defer, you can ensure that your statements are always finalized, regardless of how this method exits its scope;
  3. guard lets you write a more expressive check for the SQLite status codes.

Give your new method a try by adding the following to your playground:

Here you simply attempt to create the Contact, and catch the error if there is one.

Run your playground; you should see the following appear in your console:

Fantastic! Isn’t that a much cleaner API to work with?

Wrapping Insertions

Moving right along, it’s time to insert a row into the Contact table. Add the following method:

Now that you’ve got your SQLegs – see what I did there? :] – this code shouldn’t be too surprising. Given a Contact instance, you prepare a statement, bind the values, execute and finalize. Again, using a potent mix of defer, guard and throw allows you to take advantage of modern Swift language features.

Write the code to call this new method as shown below:

Run your playground; you should see the following in your console:

Wrapping Reads

Wrapping up (sorry, I couldn’t resist!) the section on creating the Swift wrapper is querying the database.

Add the following method to query the database for a contact:

This method simply takes the id of a contact and either returns that contact, or nil if there isn’t a contact with that id. Again, these statements should feel somewhat familiar by now.

Write the code to query the first contact:

Run your playground; you should see the following output in the console:

By now, you’ve probably identified some calls you could create in a generic fashion and apply them to entirely different tables. The point of the above exercise is to show how you can use Swift to wrap low-level C APIs. This is no simple task for SQLite; there are a ton of intricacies to SQLite that were not covered here.

You might be thinking “Hasn’t someone already created a wrapper for this?” – let me answer that for you right now!

Introducing SQLite.swift

Stephen Celis has graciously written a fully-featured Swift wrapper for SQLite named SQLite.swift. I highly recommend that you check it out if you decide that SQLite fits the bill for data storage in your app.

SQLite.swift provides an expressive way to represent tables and lets you get started with SQLite — without worrying about many of the underlying details and idiosyncrasies of SQLite. You may even consider wrapping SQLite.swift itself to create a high-level API for your app’s domain model.

Sqlpro For Mysql

Check out the well-written README.md for SQLite.swift and decide for yourself if it has a place in your personal code toolbox.

Where to Go From Here?

What about those other common tasks that were skipped over in the Swift section? You can download the completed project for this SQLite with Swift tutorial to see implemented updates, deletes, and multiple row handling. There simply wasn’t enough space to outline them all here.

One thing I haven’t covered is debugging. In many cases, you’ll need some kind of database browser to see what’s going on under the hood. There are a number of different apps out there that range from free and open source, to paid closed source with commercial support. Here are a couple to take a look at, but a quick Google search will reveal many more:

  • DB Browser for SQLite – Free
  • SQLPro – $19.99

Sqlpro Viewer For Osx

You can also access your SQLite databases directly from your Terminal by typing sqlite3 file.db. From there you can use the .help command to see a list of commands, or you can simply start executing SQL statements directly at the prompt. More information on the command-line SQLite client can be found on the main SQLite site.

Sql Pro For Sqlite Literacy

I hope you enjoyed this whirlwind introduction to working with SQLite from Swift! If you have any questions or comments, please join the discussion below!

The post SQLite With Swift Tutorial: Getting Started appeared first on Ray Wenderlich.

Sqlpro For Sqlite

SQLite With Swift Tutorial: Getting Started published first on http://ift.tt/2fA8nUr