[ent/SQLite3] insert nodes to table "users": near "RETURNING": syntax error

Problem

If you use ent with sqlite3 driver in your Golang project, you may encounter the following error when you try to insert a new record to the table.

insert nodes to table "users": near "RETURNING": syntax error

Analysis

The error message indicates that the sqlite3 driver does not support the RETURNING clause.

If you are using Golang sqlite3 package github.com/mattn/go-sqlite3 in your project. The issue may be caused by the version of the package.

In the go.mod file, check the version of the package. If it is v2.0+, you are likely to encounter the same problem.

1go 1.20
2
3require (
4	entgo.io/ent v0.12.3
5	github.com/mattn/go-sqlite3 v2.0.1+incompatible
6)

If your package version is also v2.0+, you may encounter the same issue.

Solution

To resolve the issue, downgrade the go-sqlite3 package to v1.14+. For example, use version v1.14.11 in your project. Execute the following command in the directory containing your go.mod file:

go get github.com/mattn/go-sqlite3@v1.14.11

Don’t worry about using an older version, as the latest stable version released by the author is v1.14.16 as of October 2022. Therefore, it is safe to use version v1.14.11 or later.