Proposal: align :mode open flags with sqlite3_open_v2

[Woody88]

I would like to know if you are interested in aligning database open modes (:mode on Sqlite3.open, Connection and start_link) with sqlite3_open_v2 instead.

As of now, Exqlite.Sqlite3.open and Exqlite.Connection and Exqlite.start_link map to :mode in a way that mirrors legacy sqlite3_open function (read/write + create bundled), not the finer-grained sqlite3_open_v2 flags.

I'd be happy to implement a change if there's interest; this issue is only to align on direction.

Current behavior

• :mode defaults to :readwrite, which sets SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE.
  

  exqlite/lib/exqlite/sqlite3.ex

  Lines 62 to 64 in 3e45199

  	defp do_flags_from_mode([:readwrite | tail], acc),
  	do: do_flags_from_mode(tail, [:sqlite_open_readwrite, :sqlite_open_create | acc])

• :readonly exists and requires the file to exist.
• Connection.connect always calls mkdir_p on the parent directory before Sqlite3.open/2. -

  exqlite/lib/exqlite/connection.ex

  Lines 580 to 583 in 3e45199

  	defp do_connect(database, options) do
  	with {:ok, directory} <- resolve_directory(database),
  	:ok <- mkdir_p(directory),
  	{:ok, db} <- Sqlite3.open(database, options),

  
  I can see the comment here, but not sure of the reason. I'm asking wheter that should stay unconditional. SQLite does not do this even with URI mode=rwc. -

  exqlite/lib/exqlite/connection.ex

  Lines 771 to 774 in 3e45199

  	# SQLITE_OPEN_CREATE will create the DB file if not existing, but
  	# will not create intermediary directories if they are missing.
  	# So let's preemptively create the intermediate directories here
  	# before trying to open the DB file.

• The project is compiled with SQLITE_USE_URI=1 (Makefile), so URI paths like file:path/to/db?mode=rw / mode=rwc / mode=ro can work, but this is not documented on Connection and is easy to miss when using :mode keywords.

Possible Approaches

Option A - Align :readwrite with SQLite (breaking)

Mode	Flags
:readwrite	READWRITE only
:readwrite_create	READWRITE | CREATE

Option B - Adding a new mode (non-breaking)

Keep :readwrite (READWRITE \| CREATE).

Add a new mode, e.g. :readwrite_only or :readwrite_existing for READWRITE without CREATE.

mkdir_p

Should parent-directory creation remain unconditional on Connection, or become opt-in (e.g. ensure_path: true) for callers who want strict SQLite semantics?

Motivation

I went down this path because of kino_db SQLite smart cell in Livebook. I gave it a typo'd path and it silently created a new empty database instead of erroring, which then showed up as a confusing "no such table." I was expecting a wrong path failure instead of it fabricating the state - I was also not able to test it by setting the modes because it doesn't have a selector for it except for using the URI syntax. However, that is another topic for kino_db maintainers.

Questions

1. Is aligning with sqlite3_open_v2 flag semantics a goal this library would pursue?
2. Preference for Option A vs Option B?
3. Should mkdir_p be part of the same change or handled separately?
4. Any concerns for downstream users? (I'm thinking ecto_sqlite3)

I might be missing context, happy to adjust based on feedback.

[warmwaffles]

This was done as an ergonomic thing. I'm open to the change. Though I am gonna suggest it be [:readwrite, :create]. This is going to be a breaking change, but it's something that can be fixed and needs to be well documented in in the changelog and in the readme. I am also going to suggest to keep the default behaviour with both perms. Any override you provide will be obeyed instead.

If :create is passed, we'll need to make sure the path is created.

[Woody88]

Understood. I'll open a PR shortly for the following:

• Default stays [:readwrite, :create]
• explicit, :mode will follow sqlite3_open_v2 (:readwrite read/write only, [:readwrite, :create] to create the file)
• mkdir_p when :create is in the list
• CHANGELOG and README updates.

[Woody88]

Glad to see #348 picking up the :mode part of this issue.

I reviewed the PR, and the flag work looks good:

• default [:readwrite, :create]
• explicit :readwrite / [:readwrite] no longer creates the file.

A couple of things still seem incomplete relative to what we discussed here:

1. mkdir_p is still unconditional on Connection

So mode: [:readwrite] avoids creating an empty DB file (great), but parent directories can still be created on a bad path, and that was part of my original motivation (typo'd path in Livebook). mkdir_p should run when :create is in the mode list.

2. README
You'd mentioned CHANGELOG + README - CHANGELOG/moduledoc are updated, but README still has no :mode guidance.

3. Error messages on open failure

When open fails, we get :database_open_failed without the why (i.e: missing file or wrong permissions). I verified this while testing mode with [:readwrite] on a non-existent path.

This is more related to the richer open errors discussed in #317. Not sure whether #348 should merge first and better errors as a follow-up, or if #317 should land before? Either way, stricter :mode semantics make clearer errors more important.

I have no strong feelings on merge order, I just wanted to flag that mode work and the error-message work address different parts of the same UX problem.

Happy to open a small follow-up PR or add review comments on #348 if that's easier

[warmwaffles]

I'll merge the more explicit mode change. You are welcome to propose your change in a PR.