Search code examples
swiftmariadbfluentvapor

saving an UUID in MariaDB for use with Vapor

I want to use FluentMySQL in Vapor to save data but I cannot read UUIDs properly.

If I use the UUID() function delivered by MariaDB, everything is fine, but if I Use FluentMySQL, the UUID is scrambled: (first record: Usage of UUID(), second record: Vapor)

MariaDB [someDB]> select * from Poll;
+--------------------------------------+-------+---------+---------+--------+--------+
| id                                   | title | option1 | option2 | votes1 | votes2 |
+--------------------------------------+-------+---------+---------+--------+--------+
| 88a18a58-2fcd-11ea-9f62-e283e8014c79 | test  | bla     | bla     |      0 |      0 |
| 7??/.?E??*_P?v                     | bla   | option1 | option2 |      1 |      2 |
+--------------------------------------+-------+---------+---------+--------+--------+
2 rows in set (0.00 sec)

This is my model:

import Foundation
import FluentMySQL
import Vapor

struct Poll: Content, MySQLUUIDModel, Migration {
    typealias ID = UUID
    static let entity: String = "Poll"

    var id: UUID?
    var title: String
    var option1: String
    var option2: String
    var votes1: Int
    var votes2: Int
}

this is my table:

MariaDB [someDB]> describe Poll;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | varchar(191) | YES  |     | NULL    |       |
| title   | varchar(191) | YES  |     | NULL    |       |
| option1 | varchar(191) | YES  |     | NULL    |       |
| option2 | varchar(191) | YES  |     | NULL    |       |
| votes1  | int(10)      | YES  |     | NULL    |       |
| votes2  | int(10)      | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

But if use Vapor to fetch the data, everything seems to be fine. What am I doing wrong?

Output via curl: 

curl http://locurl http://localhost:8080/polls/list
[{"option1":"bla","id":"38386131-3861-3538-2D32-6663642D3131","title":"test","option2":"bla","votes1":0,"votes2":0},{"option1":"option1","id":"373F3F2F-2E3F-453F-3F0E-2A5F503F7607","title":"bla","option2":"option2","votes1":1,"votes2":2}]

Thank you for helping in advance.

Solution

  • If you use Vapor to create a UUID field itself, it creates one with a data type of varbinary(16) not varchar(191). This is consistent with the default format for a UUID field in MariaDB/MySQL.

    I assume this is a legacy application with data being created outside Vapor? It looks like you are storing a string representation of a UUID value rather than the underlying binary value. Storing binary values in a varchar field only causes problems (as you have discovered) if you assume the values are all printable. However, the mix of string and binary format values in the same column is likely to cause problems.