How to connect simple elxiir app to MySQL database and execute query?

Questions / Help
, ,
1

Hi,

I have built a simple elxiir app which receives request like:

curl "http://localhost:8085/pass" -H Content-Type: application/json; charset=utf-8’ -d '{"ticket": 1, "ver": "1", "os": "1"}'

and the output is:

ticket: 1, ver: 0.0.1.0_develop, os: ios

SQL Query = select * from database where ticket= 1 AND ver= 1 AND os= 1

(for the process or steps on how i did it please refer to How to use curl as input in elixir script?)

Anyway, I have a MySQL in remote host (sample ip: 10.10.11.11), a database named fortesting and a table named testingone.

My question is how connect my elixir app to my MySQL database in remote host (ip: 10.10.11.11)? and run the query select * from fortesting where ticket= 1 AND ver= 1 AND os= 1? using for example simple_server.exs and not the interactive elixir?

I’ve tried this: https://www.amberbit.com/blog/2018/6/12/executing_raw_sql_queries_in_elixir/ but did not work for me.

Any ideas, suggestions, links and help are welcome. Thank you.

2

It seems like you’re just getting started with Elixir, so you might want to check out Ecto which is used by most people in the Elixir community for working with SQL databases.

Take a look at the Getting Started guide:
https://hexdocs.pm/ecto/getting-started.html

2 Likes
3

What is the exact error you see? What behaviour did you observe, which did you expect?

4 Likes
4

Hi @wmnnd,

Yes, just starting on Elixir, so far I know just the basics of Phoenix-Elixir-Ecto and I’m still researching and acquiring knowledge on the links and guides provided by hexdocs. :smiley:

But in this case I just want to know if I can or if its possible execute raw query straight using elixir without using the mix ecto.create or without creating a repo.

Thanks and I’ll study the link you gave. :smiley:

5

If you use ecto, then you will need a Repo, as the Repo is your wrapper around the connection pooling.

Of course you can also use postgrex (or any other driver) to directly do the low level dirty work to talk to your database…

2 Likes
6

Since you’re using MySQL, you probably want to use MyXQL.

GitHub: https://github.com/elixir-ecto/myxql
Docs: https://hexdocs.pm/myxql/readme.html

3 Likes
7

Hi @NobbZ,

Actually what works for me is this:

  1. Run iex -S mix on the directory of my app
  2. Run this: {:ok, pid} = Mariaex.start_link(hostname: “10.10.11.11”, username: “username”, password: “password”, database: “fortesting”)
  3. Run this: Mariaex.query!(pid, “select * from testingone”)

And there I got the results of my query.

But what i want to do or to know if I can include the connection to the database and the query straight in an elixir file.

I tried this:

post "/pass" do

  {:ok, body, conn} = read_body(conn)

  body = Poison.decode!(body)

  IO.inspect(body)

  send_resp(conn, 201, "\n ticket: #{get_in(body, ["ticket"])}, ver: #{get_in(body, ["ver"])}, os: #{get_in(body, ["os"])} \n

   SQL Query = select * from database where ticket= '#{get_in(body, ["ticket"])}' AND ver= '#{get_in(body, ["ver"])}' AND os= '#{get_in(body, ["os"])}' \n \n " )

{:ok, pid} = Mariaex.start_link(hostname: "10.10.11.11", username: "root", password: "password", database: "fortesting")

  send_resp(conn, 201, Mariaex.query!(pid, "select * from testingone"))
end

But nothing happens, still the reponse is:

ticket: 1, ver: 0.0.1.0_develop, os: ios

SQL Query = select * from database where ticket= 1 AND ver= 1 AND os= 1

8
  1. You can only send a single response per request
  2. the body that you pass to send_resp needs to be iodata (simplified a string or an arbitrary deeply nested list of strings)

Therefore I assume, that your actually crashes on your second send_resp.

2 Likes
9

:wave:
It is possible to use raw drivers? Yes.
Suppose you want to use on a simple, not phoenix project
creating a project using mix new my_app --sup will create the folders and the implementation of Application (the main entry point) which starts the supervision tree.

defmodule MyApp.Application do
  # See https://hexdocs.pm/elixir/Application.html
  # for more information on OTP Applications
  @moduledoc false

  use Application

  def start(_type, _args) do
    # List all child processes to be supervised
    children = [
      # Starts a worker by calling: MyApp.Worker.start_link(arg)
      # {MyApp.Worker, arg}
    ]

    # See https://hexdocs.pm/elixir/Supervisor.html
    # for other strategies and supported options
    opts = [strategy: :one_for_one, name: MyApp.Supervisor]
    Supervisor.start_link(children, opts)
  end
end

Now the driver is just a “Worker” and we can start under the supervisor.

  • Add {:myxql, "~> 0.2.0"} to your dependencies and run mix deps.get, this is a new driver made for newer versions of mysql
  • Supports MySQL 5.7.10+, 8.0, and MariaDB 10.3
  • Now under the MyApp.Application.start/2 function we have the children list, there we will add the initialization for the driver.
children = [
  {MyXQL, hostname: "10.10.11.11", username: "username", password: "password", database: "fortesting", name: :mydb}
]

Note that we add a key name to the options with value :mydb that is our identifier to use the same driver process in the application without the need to start/stop when a request comes.

Now on your controller action or any where you need, even on iex -S mix :

{:ok, result} = MyXQL.query(:mydb, "select * from database where ticket = ? and ver = ? and os = ?", [ticket_param, ver_param, os_param])

Performing the query with ? on the string allows the driver do the sanatization, preventing SQL Injection.

And thats it. For more infor on what you get from MyXQL.query/3 you can see the docs and the github repo

2 Likes