What are my options for simple and direct SQL database access in Elixir?

Questions / Help
, ,
1

I have a simple relational database structure I want to do simple CRUD operations on.

Open the database, create a new record, open a result set, scan for those I want, update or delete them. If anyone has used the ADODB in PHP they will understand what I want. I have taken a look at Ecto, but what I have seen seems too complicated for what I need, or it may because the examples don’t focus on my immediate use case.

I have seen something that seems to fit the bill - https://github.com/robconery/moebius - which seems to fit the bill but only works for Postgres. If there is something similar which deals with multiple databases that will be better, as Postgres is overkill for what I want to accomplish. Something like moebius that handles SQLite will be better.

Is there some guide to ecto that teaches how to do the simple actions above on an existing database? It is a small database which is only expected to handle a few 1000 records, so if I am using ecto I am hoping ecto can inspect the schema and create its own schema code from it, like Django’s South.

2

Per the Ecto docs, you can use the Ecto.Adapter.SQL module to run SQL directly:

iex> Ecto.Adapters.SQL.query(MyRepo, "SELECT $1::integer + $2", [40, 2])
{:ok, %{rows: [{42}], num_rows: 1}}

In addition to moebius, there’s also this project that looks interesting to me: https://github.com/fazibear/defql. I’ve gone so far as to install it but I’m not using it for anything so I can’t give much of a review of it.

1 Like
3

Also ecto does not require schema’s, that is only for having compile-time checks, schema-less is fine. :slight_smile:

The only thing ecto cannot really do is do queries cross-prefix’s, which is a major stumbling block in some cases.

But worst case, it has an execute function for executing the raw SQL.