Accessing an SQLite Database in Swift
While you should probably use one of the many SQLite wrappers, if you wanted to know how to call the SQLite library yourself, you would:
Configure your Swift project to handle SQLite C calls. If using Xcode 9 or later, you can simply do:
import SQLite3
Create/open database.
let fileURL = try! FileManager.default
.url(for: .applicationSupportDirectory, in: .userDomainMask, appropriateFor: nil, create: true)
.appendingPathComponent("test.sqlite")
// open database
var db: OpaquePointer?
guard sqlite3_open(fileURL.path, &db) == SQLITE_OK else {
print("error opening database")
sqlite3_close(db)
db = nil
return
}Note, I know it seems weird to close the database upon failure to open, but the
sqlite3_open
documentation makes it explicit that we must do so to avoid leaking memory:Whether or not an error occurs when it is opened, resources associated with the database connection handle should be released by passing it to
sqlite3_close()
when it is no longer required.Use
sqlite3_exec
to perform SQL (e.g. create table).if sqlite3_exec(db, "create table if not exists test (id integer primary key autoincrement, name text)", nil, nil, nil) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db)!)
print("error creating table: \(errmsg)")
}Use
sqlite3_prepare_v2
to prepare SQL with?
placeholder to which we'll bind value.var statement: OpaquePointer?
if sqlite3_prepare_v2(db, "insert into test (name) values (?)", -1, &statement, nil) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db)!)
print("error preparing insert: \(errmsg)")
}
if sqlite3_bind_text(statement, 1, "foo", -1, SQLITE_TRANSIENT) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db)!)
print("failure binding foo: \(errmsg)")
}
if sqlite3_step(statement) != SQLITE_DONE {
let errmsg = String(cString: sqlite3_errmsg(db)!)
print("failure inserting foo: \(errmsg)")
}Note, that uses the
SQLITE_TRANSIENT
constant which can be implemented as follows:internal let SQLITE_STATIC = unsafeBitCast(0, to: sqlite3_destructor_type.self)
internal let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)Reset SQL to insert another value. In this example, I'll insert a
NULL
value:if sqlite3_reset(statement) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db)!)
print("error resetting prepared statement: \(errmsg)")
}
if sqlite3_bind_null(statement, 1) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db)!)
print("failure binding null: \(errmsg)")
}
if sqlite3_step(statement) != SQLITE_DONE {
let errmsg = String(cString: sqlite3_errmsg(db)!)
print("failure inserting null: \(errmsg)")
}Finalize prepared statement to recover memory associated with that prepared statement:
if sqlite3_finalize(statement) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db)!)
print("error finalizing prepared statement: \(errmsg)")
}
statement = nilPrepare new statement for selecting values from table and loop through retrieving the values:
if sqlite3_prepare_v2(db, "select id, name from test", -1, &statement, nil) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db)!)
print("error preparing select: \(errmsg)")
}
while sqlite3_step(statement) == SQLITE_ROW {
let id = sqlite3_column_int64(statement, 0)
print("id = \(id); ", terminator: "")
if let cString = sqlite3_column_text(statement, 1) {
let name = String(cString: cString)
print("name = \(name)")
} else {
print("name not found")
}
}
if sqlite3_finalize(statement) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db)!)
print("error finalizing prepared statement: \(errmsg)")
}
statement = nilClose database:
if sqlite3_close(db) != SQLITE_OK {
print("error closing database")
}
db = nil
For Swift 2 and older versions of Xcode, see previous revisions of this answer.
Connecting SQLite 3 Database to Swift
This is because all applications in iOS are sandboxed. There is no way to avoid that. You can not save the full path. It will change every time you launch your App. What you need is to save only your file name and its parent directory. Use them to contruct a new fileURL and/or path every time you need to access them.
edit/update:
If your file is located inside your App Bundle:
let databaseURL = Bundle.main.url(forResource: "Database", withExtension: "db")!
Note: If you need the database path you have to get your fileURL path property not the absoluteString. Last but not least the Bundle is read only. You need to move/copy your database to another directory (application support) to be able to modify it.
let databasePath = databaseURL.path
How to bundle and read an SQLite database into Xcode 11.1?
It looks like SQLite.swift
will get the job done for you. From the documentation on the page you linked it looks like you use Connection(pathToDB)
to start using the library.
Since you're bundling the database and don't plan on modifying it you should be able to use Bundle.main.path(forResource:, ofType:)
to get the path to your bundled database. If you want to modify it, you'll probably want to copy it to the documents directory and then reference that copy.
Another good answer on getting paths to various kinds of bundle resources if you need more help:
How to get path of image form Resource of main bundle
Swift is connected to the SQLite DB, but I can not find the file
Most likely the default folder is one created by Xcode, something like /Users/"your username"/Library/Developer/Xcode/DerivedData/<Project name>...
To check you could add the following where you open the database
let current = FileManager.default.currentDirectoryPath
print(current)
If you look at the tutorial you notice they did use a specific folder.
Use and Access Existing SQLite Database on iOS
SQLite database interaction can be made simple and clean by using FMDB Framework
. FMDB is an Objective-C wrapper for the SQLite C interface.
Reference worth reading:
FMDB Framework Docs
Sample Project With Storyboard
Initial Setup
Add the SQLite DB
like any other file in your application's bundle then copy the database to documents directory using the following code then use the database from the documents directory
- First download the FMDB framework
- Extract the framework now copy all the file from
src/fmdb
folder (not thesrc/sample
orsrc/extra
folders). - Click your project in the left column of Xcode.
- Click the main target in the middle column.
- Click the “Build Phases” tab.
- Expand the arrow next to “Link Binary With Libraries”.
- Click the “+” button.
- Search for libsqlite3.0.dylib and double click it.
Copying your existing database
into app's document
in didFinishLaunchingWithOptions:
and maintain the database path through out the application.
In your AppDelegate add the following code.
AppDelegate.m
#import "AppDelegate.h"
@implementation AppDelegate
// Application Start
- (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions {
// Function called to create a copy of the database if needed.
[self createCopyOfDatabaseIfNeeded];
return YES;
}
#pragma mark - Defined Functions
// Function to Create a writable copy of the bundled default database in the application Documents directory.
- (void)createCopyOfDatabaseIfNeeded {
// First, test for existence.
BOOL success;
NSFileManager *fileManager = [NSFileManager defaultManager];
NSError *error;
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
// Database filename can have extension db/sqlite.
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *appDBPath = [documentsDirectory stringByAppendingPathComponent:@"database-name.sqlite"];
success = [fileManager fileExistsAtPath:appDBPath];
if (success) {
return;
}
// The writable database does not exist, so copy the default to the appropriate location.
NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"database-name.sqlite"];
success = [fileManager copyItemAtPath:defaultDBPath toPath:appDBPath error:&error];
NSAssert(success, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
}
YourViewController.m
Select Query
#import "FMDatabase.h"
- (void)getAllData {
// Getting the database path.
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *docsPath = [paths objectAtIndex:0];
NSString *dbPath = [docsPath stringByAppendingPathComponent:@"database-name.sqlite"];
FMDatabase *database = [FMDatabase databaseWithPath:dbPath];
[database open];
NSString *sqlSelectQuery = @"SELECT * FROM tablename";
// Query result
FMResultSet *resultsWithNameLocation = [database executeQuery:sqlSelectQuery];
while([resultsWithNameLocation next]) {
NSString *strID = [NSString stringWithFormat:@"%d",[resultsWithNameLocation intForColumn:@"ID"]];
NSString *strName = [NSString stringWithFormat:@"%@",[resultsWithNameLocation stringForColumn:@"Name"]];
NSString *strLoc = [NSString stringWithFormat:@"%@",[resultsWithNameLocation stringForColumn:@"Location"]];
// loading your data into the array, dictionaries.
NSLog(@"ID = %d, Name = %@, Location = %@",strID, strName, strLoc);
}
[database close];
}
Insert Query
#import "FMDatabase.h"
- (void)insertData {
// Getting the database path.
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *docsPath = [paths objectAtIndex:0];
NSString *dbPath = [docsPath stringByAppendingPathComponent:@"database-name.sqlite"];
FMDatabase *database = [FMDatabase databaseWithPath:dbPath];
[database open];
NSString *insertQuery = [NSString stringWithFormat:@"INSERT INTO user VALUES ('%@', %d)", @"Jobin Kurian", 25];
[database executeUpdate:insertQuery];
[database close];
}
Update Query
- (void)updateDate {
// Getting the database path.
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *docsPath = [paths objectAtIndex:0];
NSString *dbPath = [docsPath stringByAppendingPathComponent:@"fmdb-sample.sqlite"];
FMDatabase *database = [FMDatabase databaseWithPath:dbPath];
[database open];
NSString *insertQuery = [NSString stringWithFormat:@"UPDATE users SET age = '%@' WHERE username = '%@'", @"23", @"colin" ];
[database executeUpdate:insertQuery];
[database close];
}
Delete Query
#import "FMDatabase.h"
- (void)deleteData {
// Getting the database path.
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *docsPath = [paths objectAtIndex:0];
NSString *dbPath = [docsPath stringByAppendingPathComponent:@"database-name.sqlite"];
FMDatabase *database = [FMDatabase databaseWithPath:dbPath];
[database open];
NSString *deleteQuery = @"DELETE FROM user WHERE age = 25";
[database executeUpdate:deleteQuery];
[database close];
}
Addition Functionality
Getting the row count
Make sure to include the FMDatabaseAdditions.h
file to use intForQuery:
.
#import "FMDatabase.h"
#import "FMDatabaseAdditions.h"
- (void)gettingRowCount {
// Getting the database path.
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *docsPath = [paths objectAtIndex:0];
NSString *dbPath = [docsPath stringByAppendingPathComponent:@"database-name.sqlite"];
FMDatabase *database = [FMDatabase databaseWithPath:dbPath];
[database open];
NSUInteger count = [database intForQuery:@"SELECT COUNT(field_name) FROM table_name"];
[database close];
}
Multiple access to sqlite database using sqlite swift iOS
With sqlite.swift it was not possible to create concurrent connections.
So I moved my master data to another db and create a separate connection for it.
I also added
do{
// Start transaction
// code
// Commit transaction
}
catch {
}
to handle errors if user logout or closes the app, Transaction will help to maintain partial data saving also
Thanks to all who helped.
Related Topics
How to Do an Update Statement With Join in SQL Server
Using Column Alias in Where Clause of MySQL Query Produces an Error
How to Create a Calendar Table For 100 Years in Sql
SQL Server 2008 Management Studio Not Checking the Syntax of My Query
Create Unique Constraint With Null Columns
MySQL Foreign Key Constraint Is Incorrectly Formed Error
Count(*) Vs. Count(1) Vs. Count(Pk): Which Is Better
SQL Select Where Field Contains Words
How to Do 'Insert If Not Exists' in MySQL
Simple Way to Transpose Columns and Rows in Sql
How to Concatenate Strings of a String Field in a Postgresql 'Group By' Query
How to Import an SQL File Using the Command Line in MySQL
MySQL Error:: 'Access Denied For User 'Root'@'Localhost'