[VAPOR] FluentMySQL Querying and Saving to DB

This is a question regarding HOW/WHERE

This is what I am trying to accomplish:
Interact with Google Sheets API, fetch the columns and parse as per my needs and provide endpoints so that both Android and iOS clients can use it (all in an attempt to standardize localization via Server Side Swift)

I currently have three models:
Sheet, Language, SheetData (https://gist.github.com/lahariganti/2cc0afe477017fe0293eac8676e7e78c)

Able to successfully fetch the sheet data via:

func getSheet(_ req: Request) throws -> Future<Sheet> {
    guard let endpoint = Endpoint.sheetValues(), let url = endpoint.url else {
        // TODO: throw the appropriate error using Middleware maybe
        throw Abort(.notFound)
    let headers = HTTPHeaders(dictionaryLiteral: ("Content-Type", "application/json"))
    let sheet = try req.client().get(url, headers: headers).map(to: Sheet.self) { response in
        return try response.content.syncDecode(Sheet.self)
    return sheet.save(on: req)

Will be querying Language of the sheet via something like this:

func fetchLocales(_ req: Request) throws -> Future<[Language]> {
    return Language.query(on: req).all()

Where do I save Language objects? Have another function execute pre fetchLocales that would retrieve Sheet objects and save Language objects?

At what point do you get all the languages? Because you should probably save them then!

1 Like

ThefetchLocales function is essentially just going to parse the data obtained from google sheet (which is being saved to db) and provide it as an endpoint as per desired format and I would eventually want to filter out per language for something like /locale/en

Wanted SheetController to only deal with the sheet data and now wondering if I should just have a mechanism (am not saving Language objects to db anywhere as it is pretty much the data obtained from Sheet) which does query.all() instead of Future<[Language]>

func fetchLocales(_ req: Request) throws -> Future<[Language]> {
    return try sheetController.getSheet(req).map(to: [Language].self) { sheet in
        let decodedSheetValues = sheet.values
        var data: [String: [String: Any]] = [:]
        var allLanguages: [Language] = []
        for language in self.languages {
           var sheetData: [String: Any] = [:]
           sheetData["isoCode"] = language
           sheetData["data"] = []

           data[language] = sheetData
        for i in 1..<decodedSheetValues.count {
            let languageKey = decodedSheetValues[i][0]
            var allSheetData = [SheetData]()
            for j in 1..<decodedSheetValues[i].count {
                if data.keys.contains(languageKey) {
                    let sheetData = SheetData(key: decodedSheetValues[0][j],
                    value: decodedSheetValues[i][j],
                    description: decodedSheetValues[decodedSheetValues.count - 2][j],
                    section: decodedSheetValues[decodedSheetValues.count - 1][j])

            data[languageKey]?["data"] = allSheetData
        for (_, value) in data {
            var languageObject  = Language()
            if let isoCode = value["isoCode"] as? String {
                languageObject.isoCode = isoCode
            if let sheetData = value["data"] as? [SheetData] {
                languageObject.data = sheetData
        return allLanguages

You could put this in boot() if it can happen at app startup?

1 Like

Figure it out, thanks for the responses Tim!

For anyone who might be interested:

My use case is a bit weird as all this API does is fetch data from another API and provide the same data in a different format; it needs to check if data in the goole sheet has changed, doesn’t quite make sense to save non Sheet data into the database → but this might provide with other API versioning problems to solve down the line, I guess - idk, may be

@lganti Thank you for sharing this - much appreciated!

1 Like

This topic was automatically closed after 166 days. New replies are no longer allowed.