Phoenix with just raw SQL

Hi Alchemists,

we’re investigating whether the migration of our (simple) Rails API to Phoenix is a viable step for us.
As we can’t migrate the Rails database schema to ecto, I’m interested in how to use Phoenix just with raw SQL queries. Is it recommended to drop ecto and setup the database connection pooling etc. from scratch, or do you recommend to keep ecto because of all the great things like connection pooling etc.?

Long story short:
How to use Phoenix just with raw SQL queries? Are there any examples about that subject?

Many thanks


This Stackoverflow thread might be of interested and quite simple.


@railsmechanic, you will loose a lot of cool things without ecto.
Check this video:

1 Like

If you opt for stored procedures as opposed to using raw queries you will actually gain a lot of flexibility compared to using ORM.


I know, but a complete migration to ecto is not possible at the moment.

1 Like

It’s also worth noting that Phoenix has nothing to do with this at all, it is merely a web framework.

What prevents you from migrating the rails schema to ecto?


I know that Phoenix is “just” a web framework, but I don’t want to reinvent the wheel by writing my own database connection pool and stuff etc. Before doing this, I just wanted to know whether a default Phoenix respectively ecto installation is able to execute raw SQL queries without the need to setup models/schemas.

The reason for all this is, as we cannot migrate the rails schema to ecto at the moment, because our customer is using the underlying database from other systems which don’t belong to us. Otherwise we’ve already migrated the service. :slight_smile:

As someone who hasn’t really used Rails / ORM before, I don’t understand the benefits. Whenever I see the convoluted Ecto code, I just think “isn’t it easier to just write the SQL?”. I don’t buy the portability argument - if you want to change from mySQL to postgres, you’re in for some pain, no matter the abstraction.

I’m admittedly naive to the pros of this, given the lack of experience with the tools.

Curious to hear how one would utilise raw SQL.


1 Like

You can look at the drivers: and - they are built on top of db_connection
so they have pooling, transactions support, etc. For postgres you could
also look at for postgres that is
closer to SQL.

That said, you don’t need to change anything in your database to be able to
use ecto. You can simply define your schema modules and skip migrations.
I’ve heard couple stories of ecto working fine with a rails database.

1 Like

I agree that the argument of changing the database is completely
nonsensical. I have yet to see a system that does not use any
database-specific queries.

I see the strength of ecto in different areas - changesets are extremely
powerful and convenient way to work with data in general, not necessarily
database-originating data. Custom type definitions and automatic casting
make it a breeze to build systems. There are also some additional features
around handling associations, properly parametrizing queries, etc. Making
queries composeable is also a powerful idea that simplifies code

While I could definitely live without queries and write raw SQL,
abstractions such as changesets, multi and types are definitely a huge


Many thanks, moebius looks surprising…:+1:

So, Ecto / Phoenix do not support using raw SQL queries natively?

It does:

1 Like

Phoenix doesn’t as it’s just the webframework, but ecto and it’s db drivers surely do. But besides the ever present few edge-cases it’s questionable why one would use them over ecto queries, which can work with changeset or schemas. With schemaless queries you could even have the safety of ecto types/changesets without needing schemas.

The phoenix generators set you up with ecto by default (unless you pass --no-ecto), but otherwise phoenix doesn’t care about the database/ecto (although Phoenix.Html.Form can use changesets out of the box).

With ecto you can pass raw queries, though it ultimately comes down to the database adapter. You can pass raw sql strings or use the query builder. It’s entirely schema agnostic, though there are a few places that nudge you in a certain direction by providing sensible defaults. In these cases you are always able to override the defaults though.