`(Exqlite.Error) near "RETURNING": syntax error` when creating new record

[gzzengwei]

First of all thank you for writing this lib

I follow the README on ecto_sqlite3 and I run into this error (Exqlite.Error) near "RETURNING": syntax error when I want to create record with Ecto.

sqlite3 version: SQLite version 3.35.3 2021-03-26 12:12:52 phoenix version: 1.5.8

mix phx.new my_app
mix phx.gen.html Accounts User users name:string age:integer

and modify the mix.exs with following

      {:phoenix, "~> 1.5.8"},
      {:phoenix_ecto, "~> 4.1"},
      {:ecto_sqlite3, "~> 0.5.2"},

and run the mix ecto.create/mix ecto.migrate/mix compile without issues

> iex -S mix phx.server
> alias MyApp.Accounts.User 
> alias MyApp.Repo
> attrs = %{name: "john", age: 18}
> %User{} |> User.changeset(attrs) |> Repo.insert 

[debug] QUERY ERROR db=0.8ms idle=1918.2ms
INSERT INTO users (age,name,inserted_at,updated_at) VALUES (?,?,?,?) RETURNING id [18, "john", "2021-04-02T08:13:46", "2021-04-02T08:13:46"]
** (Exqlite.Error) near "RETURNING": syntax error
    (ecto_sql 3.5.4) lib/ecto/adapters/sql.ex:751: Ecto.Adapters.SQL.raise_sql_call_error/1
    (ecto 3.5.8) lib/ecto/repo/schema.ex:649: Ecto.Repo.Schema.apply/4
    (ecto 3.5.8) lib/ecto/repo/schema.ex:262: anonymous fn/15 in Ecto.Repo.Schema.do_insert/4

and I hv no issue to query the db

Repo.all(User)  # => []

I can see in the INSERT state got an extra RETURNING id that causing the exception.

Cheers

Follow up: If I manually created a record first, then use the Ecto.update without issue

user = Accounts.get_user!(id)
attrs = %{age: 24}
user |> User.changeset(attrs) |> Repo.update

as the PR bump amalgamation to 3.35.0 (also the official docs )mention, the RETURNING is only for DELETE, INSERT, and UPDATE statements., not including CREATE

[RickCarlino]

Yesterday I did an exploratory attempt to add this library to an existing project that uses the legacy Elixir SQLite libs and hit a similar error. I can pull up specific information if it is helpful.

[kevinlang]

Was able to repro this with the steps you gave - thanks for the detailed report!

The statement itself has no problems, as I'm able to use it locally with sqlite3 which is above 3.35.0.

If I look at my mix.lock file I see:

  "ecto_sqlite3": {:hex, :ecto_sqlite3, "0.5.3", "af1fc6b89a7b4eb7ab83469e5c4b1788e05023c17de1e1c0d0622feb89212e3e", [:mix], [{:decimal, "~> 1.6 or ~> 2.0", [hex: :decimal, repo: "hexpm", optional: false]}, {:ecto, "~> 3.5", [hex: :ecto, repo: "hexpm", optional: false]}, {:ecto_sql, "~> 3.5", [hex: :ecto_sql, repo: "hexpm", optional: false]}, {:exqlite, "~> 0.5", [hex: :exqlite, repo: "hexpm", optional: false]}], "hexpm", "02c99f693062938a0afe9696b032da616d00272eb56b8c0687c435f40948590c"},
  "exqlite": {:hex, :exqlite, "0.5.5", "cca84b8330edc38cc8dcca468d697022384c9def22897843268d1499d7f0eb42", [:make, :mix], [{:db_connection, "~> 2.1", [hex: :db_connection, repo: "hexpm", optional: false]}, {:elixir_make, "~> 0.6", [hex: :elixir_make, repo: "hexpm", optional: false]}], "hexpm", "fdfdb09b09e7e3cdef44e1e48e7436c64971cc9bce14770d8333303eaee00a12"},

Which is the latest of both dependencies. HOWEVER, if I look into deps/ folder I see under deps/exqlite/ that the amalgamation is... 3.34.1! What gives??

[kevinlang]

Looks like this PR was incorrect:

https://github.com/elixir-sqlite/exqlite/pull/132/files

When reviewing I did not catch it actually lowered the amalgamation to 3.34.1.

I'll bump it up back to 3.35.3 and issue a new release. That should fix the issue

[kevinlang]

Published 0.5.6 which has amalgamation correctly at 3.35.3, see #134

I can confirmed in my local phoenix app I created with the repo steps above, if I run mix deps.update exqlite and re-run the above, everything works as expected now:

iex(2)> alias MyApp.Accounts.User 
MyApp.Accounts.User
iex(3)> alias MyApp.Repo
MyApp.Repo
iex(4)> attrs = %{name: "john", age: 18}
%{age: 18, name: "john"}
iex(5)> %User{} |> User.changeset(attrs) |> Repo.insert 
[debug] QUERY OK db=16.2ms decode=1.8ms queue=0.1ms idle=962.9ms
INSERT INTO users (age,name,inserted_at,updated_at) VALUES (?,?,?,?) RETURNING id [18, "john", "2021-04-02T14:30:00", "2021-04-02T14:30:00"]
{:ok,
 %MyApp.Accounts.User{
   __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
   age: 18, 
   id: 1,
   inserted_at: ~N[2021-04-02 14:30:00],
   name: "john",
   updated_at: ~N[2021-04-02 14:30:00]
 }}