Tutorial 34: Working with SQLite and Third-Party Databases in iOS 🚀

Featuring: Fart Button 2: Farts on the Map 🌍💨

Welcome back, master developer! 🧙‍♂️✨ Today we're pushing our skills even further:

SQLite
Third-party database libraries
MapKit integration

All wrapped inside the beautifully absurd Fart Button App, now upgraded to log and map every fart around the world! 🌍🎺💥

📚 What You'll Learn

Setting up SQLite manually
Using a third-party wrapper (like GRDB or SQLite.swift)
Saving and querying custom data
Displaying fart locations on a map 🌎💨

🛠 Project Setup

Create a new Xcode project.
Add MapKit.
Install SQLite.swift (or GRDB.swift) using Swift Package Manager:
- URL: https://github.com/stephencelis/SQLite.swift

💾 Why Use SQLite?

Blazing fast 🚀
Lightweight 📦
Cross-platform 🌎
Great for apps that don't need full Core Data complexity

You can even inspect your database manually with any SQLite viewer!

💨 Designing the Fart Table

We'll store:

Column	Type	Description
id	Int	Primary Key
soundName	String	The fart sound filename
latitude	Double	Fart GPS latitude
longitude	Double	Fart GPS longitude
timestamp	Date	When it happened

Epic. 🌋

✨ Creating the Fart Database

import SQLite
import MapKit

class FartDatabase {
    static let shared = FartDatabase()
    private var db: Connection?

    let farts = Table("farts")
    let id = Expression<Int64>("id")
    let soundName = Expression<String>("soundName")
    let latitude = Expression<Double>("latitude")
    let longitude = Expression<Double>("longitude")
    let timestamp = Expression<Date>("timestamp")

    private init() {
        do {
            let documentDirectory = try FileManager.default.url(
                for: .documentDirectory,
                in: .userDomainMask,
                appropriateFor: nil,
                create: true
            )
            let fileUrl = documentDirectory.appendingPathComponent("farts").appendingPathExtension("sqlite3")
            db = try Connection(fileUrl.path)

            try db?.run(farts.create(ifNotExists: true) { table in
                table.column(id, primaryKey: true)
                table.column(soundName)
                table.column(latitude)
                table.column(longitude)
                table.column(timestamp)
            })
            print("📦 Database ready!")
        } catch {
            print("😱 Database error: \(error)")
        }
    }

    func addFart(sound: String, coordinate: CLLocationCoordinate2D) {
        let insert = farts.insert(
            soundName <- sound,
            latitude <- coordinate.latitude,
            longitude <- coordinate.longitude,
            timestamp <- Date()
        )
        do {
            try db?.run(insert)
            print("💨 Fart saved!")
        } catch {
            print("😱 Failed to insert fart: \(error)")
        }
    }

    func getAllFarts() -> [(sound: String, location: CLLocationCoordinate2D)] {
        var fartList = [(sound: String, location: CLLocationCoordinate2D)]()
        do {
            for fart in try db!.prepare(farts) {
                let location = CLLocationCoordinate2D(
                    latitude: fart[latitude],
                    longitude: fart[longitude]
                )
                fartList.append((sound: fart[soundName], location: location))
            }
        } catch {
            print("😱 Failed to fetch farts: \(error)")
        }
        return fartList
    }
}

🗺 Building the MapKit UI

import SwiftUI
import MapKit

struct ContentView: View {
    @State private var region = MKCoordinateRegion(
        center: CLLocationCoordinate2D(latitude: 37.7749, longitude: -122.4194),
        span: MKCoordinateSpan(latitudeDelta: 0.1, longitudeDelta: 0.1)
    )
    @State private var farts: [(sound: String, location: CLLocationCoordinate2D)] = []

    var body: some View {
        VStack {
            Map(coordinateRegion: $region, annotationItems: farts, annotationContent: { fart in
                MapMarker(coordinate: fart.location, tint: .green)
            })
            .edgesIgnoringSafeArea(.all)

            Button(action: recordFart) {
                Text("💨 Drop a Fart 💨")
                    .font(.title)
                    .padding()
                    .background(Color.green)
                    .foregroundColor(.white)
                    .cornerRadius(10)
            }
            .padding()
        }
        .onAppear(perform: loadFarts)
    }

    private func recordFart() {
        let randomOffset = Double.random(in: -0.02...0.02)
        let newCoordinate = CLLocationCoordinate2D(
            latitude: region.center.latitude + randomOffset,
            longitude: region.center.longitude + randomOffset
        )
        FartDatabase.shared.addFart(sound: "RandomFart.mp3", coordinate: newCoordinate)
        loadFarts()
    }

    private func loadFarts() {
        farts = FartDatabase.shared.getAllFarts()
    }
}

Boom. Now every fart is plotted on a map. Historical. 😂

🧠 Key Concepts You Used

SQLite database setup 📦
Creating and managing tables 🛠
Saving records 💾
Querying data 🧹
MapKit for visualizing 🌍
SwiftUI for fun interactions 🧡

🎯 Challenges to Try

Add fart categories (Epic, Squeaky, Ninja).
Allow fart playback when tapping a pin. 🎧
Show a list of recent farts below the map.
Save user preferences for fart sounds!
Animate fart explosions when a marker is tapped. 💥

You've built the Fart Mapping Revolution! 💨🗺👑

Not only can you save local data with SQLite — you can now map the gaseous trail of glory across the globe. 🌍💚

Keep coding, keep laughing, and keep shipping hilarious apps! 🚀💨

DEV Community