Near-real-time dashboard with Microsoft SQL backend

[weak topaz]

All, I am new to all things Elixir and was wondering if this scenario is possible. Already posted question here: https://stackoverflow.com/questions/77172492/use-phx-liveview-for-a-near-real-time-web-app-with-sql-backend
much appreciated.

[umbral star]

It's probably possible but the harder question is what would be the best approach? I haven't used Microsoft SQL server for a while, hence not up to date of all the functionality. In Postgres one could use LISTEN/NOTIFY to get updates from Postgres when things in the DB change, seems like the Microsoft equivalent is called Query notifications (https://learn.microsoft.com/en-us/sql/relational-databases/native-client/features/working-with-query-notifications?view=sql-server-2017).

If your Elixir application is the only using this DB, you could also use Phoenix' pubsub system to get notified about updates, skipping the DB functionality. In that case your Liveview should subscribe to updates and another part of the application could publish changes that have been persisted to DB ..

[weak topaz]

Thanks and yes, the Phx web app is the only UI for the data. I would have a bunch of agents sending data to the DB but the UI is just the one.

[whole hawk]

What he meant wasn't about only UI, it was about who's using database. If you have other agents (not written Elixir) sending data to DB, PubSub won't work.
You'd need something from your DB server in this case.

[weak topaz]

Got you. That puts me back at the beginning. Wondering if there are any examples of just plain old connect to SQL from Phx LiveView and display data with pagination? Figure start small-ish and build from there. OLE DB stuff looks very involved.

[whole hawk]

I think even if you want plain old connect to SQL, Ecto is a good start. There's https://github.com/elixir-ecto/tds that you can use standalone, and it's also supported by Ecto: https://hexdocs.pm/ecto_sql/Ecto.Adapters.SQL.html
You can use https://hexdocs.pm/ecto_sql/Ecto.Adapters.SQL.html#query/4 to query database directly without defining any schema.

[tropic geyser]

Where’s the SQL server running? I think azure has some change data capture options

Also why must it be sql server if this is in the research phase? Is it an existing DB?

[rough fossil]

one thing that confused me at first when querying with Tds directly was passing the params as Tds.parameter structs.:

Tds.query!(pid, "INSERT INTO MyTable (MyColumn) VALUES (@my_value)", [%Tds.Parameter{name: "@my_value", value: "My Actual Value"}])

[weak topaz]

It's an on-prem SQL DB not the cloud. It's an existing solution. Just want to play with Phx LiveView/Elixir to see if I can create a new UI that shows live updates when new rows are added. Something aking to what the chat sample apps do when someone creates a new post, etc. This way, multiple users can connect to the UI and see new stuff coming in. I envision even creating another view where I can highlight important changes only based on the entry (identified by name, email, etc.)

[weak topaz]

btw, how can I use the above? If I create a simple Elixir project with --no-ecto can I add this manually after?

[whole hawk]

You can. Should be as simple as adding Ecto and tds dependencies, and following Ecto documentation for installation. I think the main difference would be your repo module, default one is like this

use Ecto.Repo,
    otp_app: :app_name,
    adapter: Ecto.Adapters.Postgres

which you should change to use tds.

[weak topaz]

too many moving pieces for me at this point... 😦 not sure where that module is supposed to be. I have these deps but nothing else configured on the app:
{:tds, "~> 2.3"},
{:ecto, "~> 3.10"},
{:ecto_sql, "~> 3.0"},
Microsoft SQL is running locally on my Windows 11 box (I know...)

[umbral star]

my tip would be to generate a new app (with ecto) and see which parts are missing ?

[weak topaz]

Here is what I did:

1. mix phx.new myportal
2. added tds to list of deps (also found ecto_sql and phoenix_ecto already as deps - not sure why)
3. Ran mix deps.get
4. Updated the repo.ex file and replaced Ecto.Adapters.Postgres with Ecto.Adapters.tds
5. Ran iex.bat -S mix and get the error below:
   == Compilation error in file lib/myportal/repo.ex ==
   ** (UndefinedFunctionError) function Ecto.Adapters.tds/0 is undefined (module Ecto.Adapters is not available)
   Ecto.Adapters.tds()
   lib/myportal/repo.ex:4: (module)

[umbral star]

Yeah, the adapter is call Ecto.Adapters.Tds (tds => Tds) 😉

• Ecto provides repository pattern (so it can be used without DB)
• EctoSql makes the translation to SQL/DB
• PhoenixEcto is probablye the glue between Phoenix and Ecto? Since you use LiveView, you also use Phoenix
  Does that make sense?

[weak topaz]

sure does... now it runs but I see this in big red letters:
[error] Tds.Protocol (#PID<0.431.0>) failed to connect: ** (Tds.Error) Line 1 (Error 18456): Login failed for user 'postgres'.

[umbral star]

Gotcha, sounds like you need to specify the correct credentials (username/password etc) 😉

[weak topaz]

update this on the repo file?
defmodule Myportal.Repo do
use Ecto.Repo,
otp_app: :myportal,
adapter: Ecto.Adapters.Tds
end

[weak topaz]

when I add the below to the config.exs:
config :your_app, :tds_conn,
hostname: "localhost",
username: "test_user",
password: "test_password",
database: "test_db",
port: 1433,
execution_mode: :executesql

I get this error:
error: undefined function config/3 (there is no such import)
lib/myportal/repo.ex:7
== Compilation error in file lib/myportal/repo.ex ==
** (CompileError) lib/myportal/repo.ex: cannot compile file (errors have been logged)

[whole hawk]

Did you add config after import Config in config.exs?

Or you added config to repo file?
Have a look at file config/dev.exs. There's config for repo there, change it as you wish.
There's also config/test.exs and config/prod.exs for other environments, but first fix your dev.

[tropic geyser]

i gotcha 👍🏻 I wonder if you could just have a trigger or something bump a timestamp row in another table whenever there's an update then you just poll that row to know if there's new data, then issue a real query and push out the results

[weak topaz]

ah yes, was able to add creds on dev and it allowed me to connect once with no issues. now to figuring out how to query the db from iex... when I launch the app into iex with iex.bat, I get this error:
[error] driver_select(0x0000015bf47d1410, 948, ERL_DRV_READ, 1) by tcp_inet driver #Port<0.31> stealing control of fd=948 from resource prim_tty:tty

also, when I try this command, the connection is refused (fails to login)
{:ok, pid} = Tds.start_link([hostname: "localhost", username: "Pc_Name", password: "password", database: "DB1", port: 1433])

actual error from the line above:
[error] Tds.Protocol (#PID<0.419.0>) failed to connect: ** (Tds.Error) Line 1 (Error 18456): Login failed for user 'user_name'.

[weak topaz]

SQL has the ability to copy data to another table via queue using service broker (a bit heavy) so this way I can just query the second DB and I guarantee there's only internal service updating the database... not sure if that would help me with phx however...

[tropic geyser]

copying all the data seems like a bit much to me IMO. I was just thinking have a row get updated by a trigger and poll it via an index. so the polling won't have a performance impact

[whole hawk]

I didn't try, but I don't think you need to start Tds yourself. Ecto will do it as part of Repo.
Try something like this

Ecto.Adapters.SQL.query(MyRepo, "SELECT $1::integer + $2", [40, 2])

From https://hexdocs.pm/ecto_sql/Ecto.Adapters.SQL.html#query/4

[weak topaz]

agree... duplicating the data won't be sustainable... data is ingested once for every change on the source. So really just want to show the latest data as soon as possible

[whole hawk]

I think if the credentials in dev.exs are wrong, your app won't start.

Btw, you need to start IEx like iex -S mix

[weak topaz]

does not work on my box and saw online to you the batch file 🙂

[weak topaz]

shocks, will create a new user for SQL just in case

[whole hawk]

I'm not sure I understood. You want any help?

[weak topaz]

was able to create another local account but still get this error:
[error] driver_select(0x0000019a0e909e20, 956, ERL_DRV_READ, 1) by udp_inet driver #Port<0.23> stealing control of fd=956 from resource prim_tty:tty

on other help: Not sure I follow this query format:
Ecto.Adapters.SQL.query(MyRepo, "SELECT $1::integer + $2", [40, 2])

[tropic geyser]

ms sql is a pretty advanced db, there's probably a way to make an index for this too

though that could decrease write performance if it matters

[weak topaz]

yep, pretty easy to create index and queue. the issue is how to read those queues... the only thing I found was some really heavy C++ examples. External activator where it can launch external app is not working for me and is about 20 years old

[whole hawk]

I don't think I can help with your error. I haven't used MSSQL in like 10 years 😅

About the query function format, the first parameter is your repo module. Second one is your query and third is the query parameter. Some frameworks call these "prepared statements" if you will. You can just write your query and ignore third parameter if you want.

[tropic geyser]

why do you need the use a queue at all for this?

based on what you mentioned you just care if there's been updates. so you just need to know when that is and refresh the data, instead of just refreshing the data every time which would increase load without value

[weak topaz]

was just trying to play with LiveView pub/sub stuff to show changes as soon as they arrived but don't think there's much value. showing the latest stuff within a couple of minutes is fine

[weak topaz]

no worries... what about "my repo module" is it found on this?
defmodule Myportal.Repo do
use Ecto.Repo,
otp_app: :myportal,
adapter: Ecto.Adapters.Tds
end

[whole hawk]

It's Myportal.Repo in your case

Also make sure your repo is started in your application.ex
Like there's a list there and Myportal.Repo should be in there.

[weak topaz]

yes it's started but I think it gets shutdown right away

is this the right format: Ecto.Adapters.SQL.query(Myportal.Repo, "SELECT * from MYDB"])

Interactive Elixir (1.15.5) - press Ctrl+C to exit (type h() ENTER for help)
[notice] Application myportal exited: shutdown

[whole hawk]

Might be. To make sure it's not a config issue, you can change your adapter to SQlite and see if it works.
If it works, then it's either a credential problem or a problem with Tds library

[whole hawk]

This means your application doesn't start yes.

[weak topaz]

does not give me logon fail error however

I have a feeling this is the first app targeting SQL ever written in Elixir 🙂

[tropic geyser]

Definitely not a common combination

Microsoft stuff is often weird to connect to from oss though

[weak topaz]

yep!

[weak topaz]

any other thoughts? Looks like I'm stuck with this app...

[tropic geyser]

Where are you stuck now? What happens

Can you check the logs on the db server and see if there’s any hints

[weak topaz]

well, at this point I started over with a new app without ecto, mailer, and tailwind

added deps and now trying to figure out the query format to give it a go

actually trying to add ecto manually... which is a pain

[tropic geyser]

Yeah it’s not bad but for a newbie definitely harder than the Phoenix generator

Should just be adding the deps + making a repo + config, adding the repo to the supervisor tree in application.ex

[rough fossil]

I'm connected to a SQL Server DB right now from a Phoenix app. I found it only slighty less straight forward than Postgres.

To get your SQL Server connection, you might want to play around with Livebook first

setup:

Mix.install(
  [
    {:tds, "~> 2.3"},
    {:kino_db, "~> 0.2"}
  ]
)

opts =
  [
    hostname: "...",
    port: 1433,
    username: "...",
    password: "...",
    database: "...",
    ssl: true,
    ssl_opts: [verify: :verify_none]
  ]

{:ok, conn} = Kino.start_child({Tds, opts})

Tds.query!(
  conn,
  """
  SELECT SOME STUFF FROM SOMEPLACE
  """,
  []
)

[weak topaz]

wow, great to hear... so Livebook? will take a look at that tech.. thanks

[weak topaz]

I remember looking at Livebook before but not sure I understand its use case. Phx LiveView sounds more like what I would want as I can customize the UI and sounds more like a traditional web app? Still would love to see more of your code to see if I can replicate on LiveView project. thanks.

[rough fossil]

Livebook is like a notepad, like Jupyter, not your end application. It's handy for prototyping.

Initially, you can get the connection working in livebook.

[tropic geyser]

i ran the docker container under WSL and ran the examples in the tds library and got connected no issue

hmm running into the same exited error when trying to use it with ecto though

@weak topaz not sure if it's your issue but I think I figured it out. the docs are confusing

config :tdstest, Tdstest.Repo,
  hostname: "localhost",
  username: "sa",
  password: "mypass",
  database: "msdb",
  port: 1433

That's what I needed to have it work. You need to make sure you're configuring your app's repo, not following the directions from the tds readme which is apparently just an example if you were gonna read the config manually when adding it to the supervisor tree which to me makes no sense

if you don't do that you just get a generic exited error

and if for some reason that doesn't work, add show_sensitive_data_on_connection_error: true to that config. then add these config lines:

config :logger,
  handle_otp_reports: true,
  handle_sasl_reports: true

that will get it to show you the actual error. for me initially it was some obscure issue with String.length(nil) not being a thing, so I figured it wasnt able to find the config at all.

[weak topaz]

oh sweet. thanks. I had to use database "master" per the error message and it looks like it's running now... any idea on how to query a db? the code below fails:
Tds.query("Select * from test", [])
error:
** (UndefinedFunctionError) function Tds.query/2 is undefined or private. Did you mean:
* query/3
* query/4
(tds 2.3.4) Tds.query("Select * from test", [])
iex:1: (file)

[tropic geyser]

try Yourapp.Repo.query("SELECT 1")

or Youapp.Repo.query("SELECT * from information_schema.tables")

[tropic geyser]

https://tenor.com/view/welcome-gif-23701526

[weak topaz]

🤣

freaking legend... so** DON'T RTFM!**
iex(1)> Newhope.Repo.query("select * from dbo.users")
[debug] QUERY OK db=17.9ms decode=1.8ms queue=5.3ms idle=1645.4ms
select * from dbo.users []
{:ok,
%Tds.Result{
columns: ["id", "name", "email", "old_value", "new_value", "changedby",
"date"],
rows: [
[1, "John Doe", "[e-mail]", Decimal.new("100.00"),
Decimal.new("150.00"), "Admin", ~D[2023-09-28]],
[2, "Jane Smith", "[e-mail]", Decimal.new("200.00"),
Decimal.new("250.00"), "Admin", ~D[2023-09-28]],
[3, "Alice Johnson", "[e-mail]", Decimal.new("300.00"),
Decimal.new("350.00"), "Admin", ~D[2023-09-28]],
[4, "Bob Williams", "[e-mail]", Decimal.new("400.00"),
Decimal.new("450.00"), "Admin", ~D[2023-09-28]],
[5, "Eve Davis", "[e-mail]", Decimal.new("500.00"),
Decimal.new("550.00"), "Admin", ~D[2023-09-28]]
],
num_rows: 5
}}

[weak topaz]

well, now I hope the manual actually has helpful walkthroughs on how to display this data on a table using Phx LiveView 🙂

[tropic geyser]

So the next thing you’ll probably want to do is make ecto schemas for your tables and use those instead of raw queries

[weak topaz]

Thanks. Sounds like the next step in my journey...

[rough fossil]

have you gone through the guides? https://hexdocs.pm/ecto/getting-started.html

[weak topaz]

have seen it but never tried to implement on an app. Looks like it tries to create a db but since I have one, I can just jump to the create a schema section... will give it a world as soon as I get passed the routes issues I now have..

[weak topaz]

the docs were pretty helpful this time so it works on iex... now need to figure out how to display on a web page... wooohoo... baby steps... 🙂

[weak topaz]

app is stuck here now:
"there are pending migrations for repo: Newhope.Repo. Try running mix ecto.migrate in the command line to migrate it"
I've done ecto.migrate and ecto.drop, create, migrate a few times but something is stuck somewhere... grrr

[tropic geyser]

Maybe you ran the migrations before you were done writing them

Once it’s marked as ran in the db they won’t run again