Exqlite - make it work with existing Sqlite db

Questions / Help
,
1

Hi,

I need to do the following tasks for me project using Sqlite3 existing dbs (files):

  • connect to each of the Sqlite db/files in the repository
  • list all the tables in the db.
  • select (filter) some tables of interest
  • for selected tables execute one query to extract some data (a very simple query such as SELECT items FROM table WHERE condition)
  • return a map with all the collected results

So far, I managed (with difficulties) to install and configure Exqlite into the project.
I tried to follow the documentation Readme — Exqlite v0.7.9 and did:

{:ok, conn} = Exqlite.Sqlite3.open("path_to_one_db")
:ok = Exqlite.Sqlite3.execute(conn, "SELECT name FROM sqlite_master WHERE "type"='table'");

…and I got stuck. I don’t know how to use this library further on. The documentation is not very clear. There are no examples/tutorial online.
Next step is to get the list with the tables in the db.

Thanks for your help,
T.

PS: As you might have noted, I am quite new to Elixir. :flushed:

2

I’ve never actually used this lib, but according to the README, you should use the Exqliste.Sqlite3.step function: GitHub - elixir-sqlite/exqlite: An SQLite3 driver for Elixir

3

Thanks but I already try this.
It does nothing:

:ok
4

The README has a decent set of examples of the low-level API:

https://hexdocs.pm/exqlite/readme.html#usage

Looks like executing a query needs a prepare, possibly a bind, and then a loop that repeatedly calls step until it returns :done (or fetch_all).

One comment - the API exposed by Exqlite.Sqlite3 is the lowest-level, closest to the underlying native code that calls into SQLite3. You may find it easier to use a library like db_connection that exposes higher-level functionality.

5

There is a simpler API here Exqlite.Basic

6

Thanks but that README is not so decent for me. Very “cryptic”. It does not work when I replicate it in my case. Some sort of tutorial would be fine, step by step, with real-world examples. Similar to this: SQLite PHP in php.
In general, I find that all of hexdocs are not very helpful for beginners like me, coming from the OOP world. :neutral_face:

7

Thanks, this might be simpler, but none of the functions of the Basic API are documented.

8

Yep, I noticed that too. But I dont think it will be very hard to guess how it works :slight_smile:

{:ok, db} = Exqlite.Basic.open(":memory:")

db
|> Exqlite.Basic.exec("create table books (title, author)")

db
|> Exqlite.Basic.exec("insert into books (title, author) values (?, ?)", ["test", "bob"])

db
|> Exqlite.Basic.exec("select * from books")
|> Exqlite.Basic.rows()

Results in this

{:ok, [["test", "bob"]], ["title", "author"]}
3 Likes
9

It might be worth looking at using exqlite through ecto, which is quite well documented.

2 Likes
10

Yeah, that is a good idea. Will get you some typecasting as well since there are not many types in sqlite.

11

This is it!
Thanks @kwando