1. Sqlpro For Sqlite
2. Sqlpro For Windows 10

SQLPro for SQLite is the top SQLite editor for OS X. Automatic data reloading when the database gets modified externally. Backwards compatibility with SQLite 2. SQL OnLine - (Test DB): SQLite, MariaDB / MySQL, PostgreSQL, MS SQL Server. User-friendly interface for data science. No registration for start, No DownLoad, No Install.

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.

Getting Started

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
• 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.

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.

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:

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.

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.

Sqlpro For Sqlite

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: Unitcom laptops & desktops driver download.

Sqlpro For Windows 10

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.

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

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.

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.

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

Full MySQL Support

Sequel Pro is a fast, easy-to-use Mac database management application for working with MySQL databases.

Perfect Web Development Companion

Whether you are a Mac Web Developer, Programmer or Software Developer your workflow will be streamlined with a native Mac OS X Application!

Flexible Connectivity

Sequel Pro gives you direct access to your MySQL Databases on local and remote servers.

Easy Installation

Simply download, and connect to your database. Use these guides to get started:

Get Involved

Sequel Pro is open source and built by people like you. We’d love your input – whether you’ve found a bug, have a suggestion or want to contribute some code.

Get Started

New to Sequel Pro and need some help getting started? No problem.