Timex.Ecto.DateTimeWithTimezone with SQLite please?

[serialhex]

I know in the documentation you explicitly say that only Postgres is supported, I thought I might try using timestamps w/ timezone in SQLite. Unfortunately this is a no-go, as I get cool errors[1].

Now, my thinking was that SQLite isn't very type-safe, AFAIK the types mostly just there to help figure out what's going on, and defaults to string. So I was hoping that my field :timestamp, Timex.Ecto.DateTimeWithTimezone would stringify to "#DateTime<2018-04-30 15:34:50.897000-04:00 EDT America/New_York>" or "2018-04-30T15:35:43.300000-04:00" or something... Not too surprised that this didn't work, but I was wondering what would it take to make it work?

OTOH, maybe this isn't a problem with Timex.Ecto, but possibly Ecto itself, or (more likely) my code. It seems like SQLite gets the short-end of the dev-time stick, but IMHO, it's way easier to work with than Postgres, even in production. (Granted, I'm not doing anything web-scale, but then I wouldn't be using Postgres anyway :stuck_out_tongue_closed_eyes: [2])

[1] Cool Error:

%Event{
  operator_id: (Operator |> where([op], op.initials == "JP") |> select([op], op.id) |> Repo.one),

  filename: "TE5T_B1AD3_L3I_JP-SIS8161 150MM_JOB 123456.cdb",
  timestamp: Timex.now,
  customer: "L3I",
  equipment: "SIS8161 150MM",
  job: "123456",
  operation: "cdb",
  part: "B1AD3",
  serial: "TE5T"
} |> Repo.insert!

14:45:58.958 [debug] QUERY ERROR db=0.0ms
INSERT INTO "events" ("customer","equipment","filename","job","operation","operator_id","part","serial","timestamp") VALUES (?1,?2,?3,?4,?5,?6,?7,?8,?9) ;--RETURNING ON INSERT "events","id" ["L3I", "SIS8161 150MM", "TE5T_B1AD3_L3I_JP-SIS8161 150MM_JOB 123456.cdb", "123456", "cdb", 1, "B1AD3", "TE5T", {{{2018, 4, 30}, {14, 45, 58, 954000}}, "America/New_York"}]
** (CaseClauseError) no case clause matching: {:error, :wrong_type}
    (sqlite_ecto2) lib/sqlite_db_connection/protocol.ex:151: Sqlite.DbConnection.Protocol.run_stmt/3
    (sqlite_ecto2) lib/sqlite_db_connection/protocol.ex:131: Sqlite.DbConnection.Protocol.handle_execute/5
    (db_connection) lib/db_connection.ex:958: DBConnection.handle/4
    (db_connection) lib/db_connection.ex:1078: DBConnection.describe_run/5
    (db_connection) lib/db_connection.ex:1142: anonymous fn/4 in DBConnection.run_meter/5
    (db_connection) lib/db_connection.ex:584: DBConnection.prepare_execute/4
    (sqlite_ecto2) lib/sqlite_ecto/connection.ex:35: Sqlite.Ecto2.Connection.execute/4
    (ecto) lib/ecto/adapters/sql.ex:256: Ecto.Adapters.SQL.sql_call/6

14:45:58.958 [error] GenServer #PID<0.416.0> terminating
** (DBConnection.ConnectionError) client #PID<0.447.0> stopped: ** (CaseClauseError) no case clause matching: {:error, :wrong_type}
    (sqlite_ecto2) lib/sqlite_db_connection/protocol.ex:151: Sqlite.DbConnection.Protocol.run_stmt/3
    (sqlite_ecto2) lib/sqlite_db_connection/protocol.ex:131: Sqlite.DbConnection.Protocol.handle_execute/5
    (db_connection) lib/db_connection.ex:958: DBConnection.handle/4
    (db_connection) lib/db_connection.ex:1078: DBConnection.describe_run/5
    (db_connection) lib/db_connection.ex:1142: anonymous fn/4 in DBConnection.run_meter/5
    (db_connection) lib/db_connection.ex:584: DBConnection.prepare_execute/4
    (sqlite_ecto2) lib/sqlite_ecto/connection.ex:35: Sqlite.Ecto2.Connection.execute/4
    (ecto) lib/ecto/adapters/sql.ex:256: Ecto.Adapters.SQL.sql_call/6

    (db_connection) lib/db_connection/connection.ex:243: DBConnection.Connection.handle_cast/2
    (connection) lib/connection.ex:488: Connection.handle_call/3
    (stdlib) gen_server.erl:636: :gen_server.try_handle_call/4
    (stdlib) gen_server.erl:665: :gen_server.handle_msg/6
    (stdlib) proc_lib.erl:247: :proc_lib.init_p_do_apply/3
Last message (from #PID<0.541.0>): {:stop, #Reference<0.2461348511.4105175042.180494>, %DBConnection.ConnectionError{message: "client #PID<0.447.0> stopped: ** (CaseClauseError) no case clause matching: {:error, :wrong_type}
    (sqlite_ecto2) lib/sqlite_db_connection/protocol.ex:151: Sqlite.DbConnection.Protocol.run_stmt/3
        (sqlite_ecto2) lib/sqlite_db_connection/protocol.ex:131: Sqlite.DbConnection.Protocol.handle_execute/5
    (db_connection) lib/db_connection.ex:958: DBConnection.handle/4
        (db_connection) lib/db_connection.ex:1078: DBConnection.describe_run/5
            (db_connection)
 lib/db_connection.ex:1142: anonymous fn/4 in DBConnection.run_meter/5
     (db_connection) lib/db_connection.ex:584: DBConnection.prepare_execute/4
         (sqlite_ecto2) lib/sqlite_ecto/connection.ex:35: Sqlite.Ecto2.Connection.execute/4
    (ecto) lib/ecto/adapters/sql.ex:256: Ecto.Adapters.SQL.sql_call/6
    "}, %Sqlite.DbConnection.Protocol{checked_out?: true, db: #PID<0.426.0>, path: "db.sl3"}}
State: {Sqlite.DbConnection.Protocol, %Sqlite.DbConnection.Protocol{checked_out?: true, db: #PID<0.426.0>, path: "db.sl3"}}
Client #PID<0.541.0> is alive
    (stdlib) gen.erl:169: :gen.do_call/4
    (stdlib) gen_server.erl:210: :gen_server.call/3
    (db_connection) lib/db_connection/connection.ex:428: DBConnection.Connection.sync_stop/5

[2] https://www.youtube.com/watch?v=b2F-DItXtZs

[bitwalker]

The problem is that the underlying datastore has to expose the types being used. I'm not actually quite sure how to make the types defined in this project work with other databases, as they mostly piggyback on Ecto types. You would probably need to conditionally change the behaviour of the type based on what adapter is being used, I think, since what you want is for the representation of that data type to change based on whether the database natively supports that type.

So my recommendation would be to define a custom Ecto type which you can use for DateTimes when running on SQLite, then swap back to the native Ecto types, or the Timex.Ecto types, when using Postgres.

I'm a big fan of SQLite myself, but haven't really found use for it in Elixir yet. My maintenance burden is already pretty well peaked, so I can't really afford to support another database in this project unfortunately :(