FazBrowse GitHub Viewer | Trending |
URL:
| Home
Tools: [Download Repo ZIP]   [Original HTTPS Page]

Use :prefix to query attached databases by sbaildon · Pull Request #175 · elixir-sqlite/ecto_sqlite3 · GitHub

/ ecto_sqlite3 Public

Use :prefix to query attached databases - #175

Open
sbaildon wants to merge 5 commits into
elixir-sqlite:mainfrom
sbaildon:prefix
ConversationCommits (5)ChecksFiles changed
Open

Use :prefix to query attached databases#175
sbaildon wants to merge 5 commits into
elixir-sqlite:mainfrom
sbaildon:prefix

Conversation

sbaildon commented Sep 26, 2025 •
edited
Loading

Copy link
Copy Markdown
Contributor

SQLite allows attached databases, and their tables can be referenced by prefixing the schema name

ATTACH DATABASE "/tmp/database.db" AS another;
SELECT * FROM another.things;

I think it makes sense to use ecto's :prefix option to query these schemas.

I'm currently using this patch like...

as = "another"

Repo.checkout(fn ->
    Repo.query("ATTACH DATABASE ? AS ?", ["/tmp/database.db", as])

    Repo.transact(fn ->
        {:ok, service} = Repo.insert(%Service{id: 1})
        Repo.insert(%Registry{id: 1, service_id: service.id}, prefix: as)
    end)

    Repo.query("DETACH DATABASE ?", [as])
end)

Where I'm wrapping a transaction with ATTACH/DETACH to insert rows with transactional guarantees across both databases

warmwaffles commented Sep 26, 2025 •
edited
Loading

Copy link
Copy Markdown
Member

I think prefix was for another_ for table names, not schemas. I'm unsure.

EDIT:
Nope I'm wrong https://elixirforum.com/t/ecto-schema-tables-for-multiple-schemas/55726/3

warmwaffles reviewed Sep 26, 2025
View reviewed changes
Comment thread lib/ecto/adapters/sqlite3/connection.ex Outdated
use :prefix to query attached databases
99678cf 
https://www.sqlite.org/lang_attach.html

the prefixed table name is intentionally not quoted because statements
expect a bare qualifier without quotes like `INSERT INTO
schema-name.table-name`

warmwaffles commented Sep 26, 2025

Copy link
Copy Markdown
Member

Can you do me a favor as well and remove this

ecto_sqlite3/integration_test/test_helper.exs

Line 94 in 2f9a6ff

:prefix,

and let the integration tests run.

warmwaffles commented Sep 26, 2025

Copy link
Copy Markdown
Member

We'll need to fix the assertions encountered with mix test

sbaildon commented Oct 5, 2025 •
edited
Loading

Copy link
Copy Markdown
Contributor Author

I need to understand why the attached database seemingly doesn't exist when running the ecto library tests, but I think we're close.

warmwaffles reviewed Oct 6, 2025
View reviewed changes
Comment thread lib/ecto/adapters/sqlite3/connection.ex
reference_name(ref, table, name),
" REFERENCES ",
quote_table(ref.prefix || table.prefix, ref.table),
quote_table(nil, ref.table),

warmwaffles Oct 6, 2025

Copy link
Copy Markdown
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Choose a reason Spam Abuse Off Topic Outdated Duplicate Resolved Low Quality

Is this right? If someone has a prefix specified on the schema or relationship it should be put here.

sbaildon Oct 7, 2025

Copy link
Copy Markdown
Contributor Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Choose a reason Spam Abuse Off Topic Outdated Duplicate Resolved Low Quality

Ah yes I think that's right

I tried this and got an error:

sqlite> attach ":memory:" as attached_db
sqlite> create table attached_db.things (thing string not null);
sqlite> create table attached_db.refs (thing_id bigint not null references attached_db.things(id));
Parse error: near ".": syntax error
  s (thing_id bigint not null references attached_db.things(id));
                                      error here ---^

but quoting the entire reference works:

sqlite> attach ":memory:" as attached_db
sqlite> create table attached_db.things (thing string not null);
sqlite> create table attached_db.refs (thing_id bigint not null references 'attached_db.things(id)');

warmwaffles Oct 7, 2025

Copy link
Copy Markdown
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Choose a reason Spam Abuse Off Topic Outdated Duplicate Resolved Low Quality

Take a look at how the posgres and mysql adapters do this. I want to try and stay as close to what they do. I haven't looked at it in a long time (like 2 years) and we probably need to make adjustments.

This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode characters
Show hidden characters
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

warmwaffles warmwaffles left review comments

Assignees

No one assigned

Labels

None yet

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

2 participants

Back | FazBrowse Home | New Git URL