Generate SQL prepared statements from Ecto Query

Questions / Help
1

Is it possible to generate SQL prepared statements with Ecto Query, without using Repo?

I need to query data from one of thousands of SQLite databases, and I am using Sqlitex to do that using raw SQL queries.

i figured I could get a lot of benefits like SQL injection prevention, more maintainable query using Ecto.Query like this:

query =
  from u in "users",
  join: c in "comments",
  on: c.user_id == u.id,
  where: u.active == true,
  group_by: [u.name, u.email],
  select: [u.name, u.email, count(c.id)]

I cannot use Repo because I do not have a specific database to connect to during Phoenix boot.

However, I am not able to generate prepared statements from this query that I could use with Sqlitex.

Anyone has idea on how to generate the prepared statements without using Repo?

Thanks

2

There is Ecto.Adapters.SQL.to_sql/3. Event though, it requires Repo as a second argument - the resulting query should be independent from particular database.

3

Yepp, I have found it is possible to generate query using Repo. But I do not have a Repo object as I do not have a SQLite database to connect to upon application boot. I am explicitly connecting to a SQLite file on each incoming HTTP request. Thanks

4

Ecto must know what kind of a Repo it will need to create a query according to implemented :adapter. I assumed that repo can be declared with adapter but not necessary started in application/connected to DB, and then we could use it.

However, as of now it doesn’t work like that because underneath it uses Ecto.Adapter.Queryable.prepare_query/3 which in its own turn checks for “cached” connection in Ecto.Repo.Registry and fails with the error like could not lookup Ecto repo <RepoName> because it was not started or it does not exist :man_shrugging:

5

Got it. Thanks

I ended up generating a SQLite with same schema to connect during boot, and use it for generating raw sql.