How to Create Sqlite Database in Ios

SQLite in iOS : How to work with SQLite in iOS with Swift 5

SQLite in iOS : How to work with SQLite in iOS with Swift 5

May 8, 2020 No Comments Development,iOS Development Pushpendra Kumar

In this tutorial, you will learn how to store data into an SQLite database in iOS with a swift 5 programming language. So, In this tutorial, we will learn the basics of the SQLite database. Like how to create a Database with SQLite in iOS with swift 5, how we can use it. So this is a very useful tutorial for you.

SQLite in iOS with Swift 5

SQLite in iOS is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. And SQLite is the most used database engine in the world. So, It is built into all mobile phones and most computers and comes bundled inside countless other applications that people use every day.

SOURCE – https://www.sqlite.org/

Create Database

After that, We will create a database. And our database will be OpaquePointer type. So, Before doing that we will create a Swift class and you can give the nameDBHelper. So inside the DBHelper, we will write the following code. And before writing the code, I would like to tell you that. You can all these work on video tutorial also!

So above is the video, And below is code related to this video!

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

var db : OpaquePointer ? var path : String = "myDataBaseName.sqlite" init ( ) { self . db = createDB ( ) self . createTable ( ) } func createDB ( ) -> OpaquePointer ? { let filePath = try ! FileManager . default . url ( for : . documentDirectory , in : . userDomainMask , appropriateFor : nil , create : false ) . appendingPathExtension ( path ) var db : OpaquePointer ? = nil if sqlite3_open ( filePath . path , & db ) != SQLITE_OK { print ( "There is error in creating DB" ) return nil } else { print ( "Database has been created with path \ ( path )" ) return db } }

Ok Great! Now we will create a table in our database. And Below is the code for creating table!

func createTable ( ) { let query = "CREATE TABLE IF NOT EXISTS your_table_name(id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT, result TEXT, avg INTEGER, list TEXT);" var statement : OpaquePointer ? = nil if sqlite3_prepare_v2 ( self . db , query , - 1 , & statement , nil ) == SQLITE_OK { if sqlite3_step ( statement ) == SQLITE_DONE { print ( "Table creation success" ) } else { print ( "Table creation fail" ) } } else { print ( "Prepration fail" ) } }

Above all is the code for setting the database! Now after that, we will write a function for inserting data into the database as mention below. And below video will helpful for understanding the work of insertion operation and read operation.

Insert and Read operation : Sqlite in iOS

So, below is the code related to this video!

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63

func insert ( name : String , result : String , avg : Int , list : [ Grade ] ) { let query = "INSERT INTO your_table_name (id, name, result, avg, list) VALUES (?, ?, ?, ?, ?);" var statement : OpaquePointer ? = nil var isEmpty = false if read ( avg : avg ) . isEmpty { isEmpty = true } if sqlite3_prepare_v2 ( db , query , - 1 , & statement , nil ) == SQLITE_OK { if isEmpty { sqlite3_bind_int ( statement , 1 , 1 ) } sqlite3_bind_text ( statement , 2 , ( name as NSString ) . utf8String , - 1 , nil ) sqlite3_bind_text ( statement , 3 , ( result as NSString ) . utf8String , - 1 , nil ) sqlite3_bind_int ( statement , 4 , Int32 ( avg ) ) let data = try ! JSONEncoder ( ) . encode ( list ) let listString = String ( data : data , encoding : . utf8 ) sqlite3_bind_text ( statement , 5 , ( listString ! as NSString ) . utf8String , - 1 , nil ) if sqlite3_step ( statement ) == SQLITE_DONE { print ( "Data inserted success" ) } else { print ( "Data is not inserted in table" ) } } else { print ( "Query is not as per requirement" ) } } func read ( avg : Int ) -> [ DBGrade ] { var mainList = [ DBGrade ] ( ) let query = "SELECT * FROM your_table_name;" var statement : OpaquePointer ? = nil if sqlite3_prepare_v2 ( db , query , - 1 , & statement , nil ) == SQLITE_OK { while sqlite3_step ( statement ) == SQLITE_ROW { let id = Int ( sqlite3_column_int ( statement , 0 ) ) let name = String ( describing : String ( cString : sqlite3_column_text ( statement , 1 ) ) ) let result = String ( describing : String ( cString : sqlite3_column_text ( statement , 2 ) ) ) let avg = Int ( sqlite3_column_int ( statement , 3 ) ) let list = String ( describing : String ( cString : sqlite3_column_text ( statement , 4 ) ) ) let model = DBGrade ( ) model . id = id model . name = name model . result = result model . avg = avg let data = try ! JSONDecoder ( ) . decode ( [ Grade ] . self , from : list . data ( using : . utf8 ) ! ) model . list = data mainList . append ( model ) } } return mainList }

Great JOB! 😊😊😊 After that, We have two very useful steps. firstly is Delete the Item or row from the SQLite Database and secondly Update the Item or row in the SQLite database.

Update and Delete operation : Sqlite in iOS

So now, below is the video tutorial regarding update and delete the Data from the Sq-lite database.

And below is the code related to this video!

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

func update ( id : Int , name : String , result : String , avg : Int , list : [ Grade ] ) { let data = try ! JSONEncoder ( ) . encode ( list ) let listString = String ( data : data , encoding : . utf8 ) let query = "UPDATE grade SET name = '\ ( name )', result = '\ ( result )', avg = \ ( avg ), list = '\ ( listString ! )' WHERE id = \ ( id );" var statement : OpaquePointer ? = nil if sqlite3_prepare_v2 ( db , query , - 1 , & statement , nil ) == SQLITE_OK { if sqlite3_step ( statement ) == SQLITE_DONE { print ( "Data updated success" ) } else { print ( "Data is not updated in table" ) } } } func delete ( id : Int ) { let query = "DELETE FROM grade where id = \ ( id )" var statement : OpaquePointer ? = nil if sqlite3_prepare_v2 ( db , query , - 1 , & statement , nil ) == SQLITE_OK { if sqlite3_step ( statement ) == SQLITE_DONE { print ( "Data delete success" ) } else { print ( "Data is not deleted in table" ) } } }

Wonderful dear developers..!! So, I hope you are very clear about the complete SQLite Database and its usage. In Conclusion, I would like to tell you that. If you like my blog then please go to my YouTube Channel  and Subscribe to Pushpendra Saini Youtube  channel. Because on my channel  you will get every day updates with new techniques and new technology. It will good for if you want to boost your skill! 😊😊😊

In Addition, I would like to tell you that, If you did not see the important tutorial then you must read! So, Below the links.

• WKWebView in iOS with swift 5
• Play YouTube video in iOS with swift 5
• Bar Code Scanner | QR Code Scanner in iOS with swift 5
• Loader in iOS with swift 5
• load image from URL in UIImageView with Swift 5
• Extension in iOS
• Integrate Apple Pay in iOS with swift 5

Tags

About The Author

Pushpendra Kumar I am passionate about mobile application development and professional developer at Colour Moon Technologies Pvt Ltd (www.thecolourmoon.com). This website I have made so that I can meet with new challenges and can share here.

How to Create Sqlite Database in Ios

Source: http://www.wepstech.com/sqlite-in-ios-with-swift-5/

Share this post