iOS中DuckDB-swift的LOAD spatial查询失效，无法用ST_Read加载GPKG

我已将duckdb-swift集成到iOS项目中，尝试使用ST_Read函数加载GPKG文件。执行INSTALL spatial;有响应，但LOAD spatial;返回nil，运行ST_Read查询时抛出错误：

Error creating table: connectionQueryError(reason: Optional("Catalog Error: Table Function with name "st_read" is not in the catalog, but it exists in the spatial extension.\n\nPlease try installing and loading the spatial extension:\nINSTALL spatial;\nLOAD spatial;\n\n"))

同时后续查询表时提示表不存在：

Error querying table: connectionQueryError(reason: Optional("Catalog Error: Table with name federal_lands does not exist!\nDid you mean "pg_depend"?"))

我的代码如下：

// Get the document directory path for the persistent database file
let documentDirectory = FileManager.default.urls(for: .documentDirectory, in: .userDomainMask).first!
let dbURL = documentDirectory.appendingPathComponent("gpkg_test.duckdb")

// Initialize DuckDB with persistent storage
let db = try DuckDB.Database(store: .file(at: dbURL))
let connection = try db.connect()
do {
    let res = try? connection.query("INSTALL spatial;")
    print(res)
    print("Spatial extension installation attempted.")
}catch let error as DuckDB.DatabaseError {
    print("Database error during spatial extension installation: \(error), \(error.localizedDescription)")
} catch {
    print("General error during spatial extension installation: \(error.localizedDescription)")
}
do {
    let res = try? connection.query("LOAD spatial;")
    print(res)
    print("Spatial extension loading attempted.")
}catch let error as DuckDB.DatabaseError {
    print("Database error during spatial extension installation: \(error), \(error.localizedDescription)")
} catch {
    print("General error during spatial extension installation: \(error.localizedDescription)")
}

if FileManager.default.fileExists(atPath: gpkgFilePath) {
    print("GeoPackage file found at path: \(gpkgFilePath)")
    
} else {
    print("GeoPackage file not found at path: \(gpkgFilePath)")
    result(FlutterError(code: "FILE_NOT_FOUND", message: "GeoPackage file not found", details: nil))
    return
}

// Ensure the path is properly sanitized
let sanitizedPath = gpkgFilePath.replacingOccurrences(of: "\\", with: "")

// Attempt to create a table by reading data from the GeoPackage file
do {
    var res = try connection.query("CREATE TABLE federal_lands AS SELECT * FROM ST_Read('\(sanitizedPath)');")
    print("Table 'federal_lands' created successfully.\(res)")
} catch {
    print("Error creating table: \(error)")
    result(FlutterError(code: "QUERY_ERROR", message: "Failed to create table: \(error.localizedDescription)", details: nil))
}

解决方案 #

1. 移除try?，捕获LOAD扩展的真实错误 #

try?会静默丢弃错误，导致无法得知LOAD失败的具体原因。改用try并精准捕获错误：

do {
    let installRes = try connection.query("INSTALL spatial;")
    print("Spatial extension installed: \(installRes)")
} catch let error as DuckDB.DatabaseError {
    print("INSTALL spatial error: \(error)")
}

do {
    let loadRes = try connection.query("LOAD spatial;")
    print("Spatial extension loaded: \(loadRes)")
} catch let error as DuckDB.DatabaseError {
    print("LOAD spatial error: \(error)")
}

2. 配置iOS环境下的扩展路径 #

iOS环境中DuckDB默认无法自动找到扩展文件，需手动配置：

• 预编译适配iOS arm64架构的spatial扩展文件（.duckdb_extension格式）
• 将扩展文件添加到项目的Copy Bundle Resources中
• 初始化数据库时指定扩展目录：

guard let extDir = Bundle.main.url(forResource: "extensions", withExtension: nil) else {
    print("Extension directory not found")
    return
}
let db = try DuckDB.Database(store: .file(at: dbURL), options: [
    "extension_directory": extDir.path
])

3. 使用参数绑定避免路径转义问题 #

直接字符串拼接路径易引发转义错误和SQL注入风险，改用参数绑定传递路径：

do {
    let stmt = try connection.prepare("CREATE TABLE federal_lands AS SELECT * FROM ST_Read(?);")
    let res = try stmt.execute([sanitizedPath])
    print("Table created successfully: \(res)")
} catch {
    print("Error creating table: \(error)")
}

4. 验证扩展加载状态 #

执行ST_Read前，先查询已加载的扩展确认状态：

let extensions = try connection.query("SELECT * FROM duckdb_extensions();")
print("Loaded extensions: \(extensions)")

关键注意事项 #

• iOS上的DuckDB扩展必须针对arm64架构预编译，不能直接使用桌面端扩展文件
• 确保扩展文件被正确打包到APP资源中
• 文档目录是iOS中唯一具备持久化读写权限的路径，无需额外权限申请

内容的提问来源于stack exchange，提问作者Muhammad Umair