(Please consider sponsoring us on Patreon 😄)
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:
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. 😄
Our latest release (3.12.1) for macOS:
If you prefer using Homebrew for macOS, our latest release can be installed via Homebrew Cask:
Download nightly builds for Windows and macOS here:
DB Browser for SQLite works well on Linux.
Arch Linux provides an up to date version
Install with the following command:
Install for Fedora (i386 and x86_64) by issuing the following command:
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
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 are available here:
To add this ppa, type these commands into the terminal:
Then update the cache using:
Install the package using:
On others, compile DB4S using the instructionsin BUILDING.md.
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:
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.
Download the starter project for this SQLite with Swift tutorial and open SQLiteTutorial.xcworkspace. From the Project Navigator open the Tutorial playground.
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
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:
openDatabase() succeeds, you’ll see some output like the following:
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
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:
- First, you create a pointer to reference in the next step.
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.
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.
- 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
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:
- First, compile the statement and verify that all is well;
- Here, you define a value for the
?placeholder. The function’s name —
sqlite3_bind_int()— implies you’re binding an
Intvalue 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;
- 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
nilfor them. If you’d like, you can read more about binding parameters here;
- Use the
sqlite3_step()function to execute the statement and verify that it finished;
- 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|
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:
- Prepare the statement;
- 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;
- 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
- Next, you fetch the text value from the
Namecolumn. This is a bit messy due to the C API. First, you capture the value as
queryResultCol1so you can convert it to a proper Swift string on the next line;
- Print out the results;
- 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|
The next natural progression is to update an existing row. You should start to see a pattern emerging.
First, create the
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? :]
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:
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:
- Prepare the statement, which will fail and should NOT return
- 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;
- 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:
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
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
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:
- Attempt to open the database at the provided path;
- If successful, return a new instance of
- Otherwise, defer closing the database if the status code is anything but
SQLITE_OKand 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
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:
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:
prepareStatement()throws, so you must use
try. You’re not doing this in a
do-try-catchblock because this method itself throws, so any error from
prepareStatement()will simply be thrown to the caller of
- With the power of
defer, you can ensure that your statements are always finalized, regardless of how this method exits its scope;
guardlets 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?
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
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 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!
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