Fails to CreateTableUsers when used with GenieAuthentication: `ERROR: MethodError: no method matching query(::String)`

[Krastanov]

Describe the bug

Following the setup instructions for GenieAuthentication, the moment I run the SearchLight migration, I get an error.

Error stacktrace

julia> SearchLight.Migration.up("CreateTableUsers")
ERROR: MethodError: no method matching query(::String)
Stacktrace:
 [1] upped_migrations()
   @ SearchLight.Migration ~/.julia/packages/SearchLight/B9d2o/src/Migration.jl:337
 [2] run_migration(migration::SearchLight.Migration.DatabaseMigration, direction::Symbol; force::Bool)
   @ SearchLight.Migration ~/.julia/packages/SearchLight/B9d2o/src/Migration.jl:279
 [3] #up#15
   @ ~/.julia/packages/SearchLight/B9d2o/src/Migration.jl:187 [inlined]
 [4] up(migration_module_name::String)
   @ SearchLight.Migration ~/.julia/packages/SearchLight/B9d2o/src/Migration.jl:186
 [5] top-level scope
   @ REPL[7]:1

To reproduce

Genie.newapp_mvc("something") # choose sqlite
GenieAuthentication.install(@__DIR__)
SearchLight.Migration.up("CreateTableUsers")

This happens independently of whether I restart julia between invocations of each command. I am using sqlite

Expected behavior

It should run without error.

Additional context

julia> versioninfo()
Julia Version 1.7.0-rc2
Commit f23fc0d27a (2021-10-20 12:45 UTC)
Platform Info:
  OS: Linux (x86_64-pc-linux-gnu)
  CPU: DO-Premium-AMD
  WORD_SIZE: 64
  LIBM: libopenlibm
  LLVM: libLLVM-12.0.1 (ORCJIT, znver2)
Environment:
  JULIA_REVISE = auto

(Moonshot) pkg> st
     Project ...
      Status `~/.../Project.toml`
  [c43c736e] Genie v4.5.0
  [e115e502] GenieAuthentication v1.1.0
  [6d011eab] Inflector v1.0.1
  [e6f89c97] LoggingExtras v0.4.7
  [739be429] MbedTLS v1.0.3
  [340e8cb6] SearchLight v2.0.1
  [21a827c4] SearchLightSQLite v2.0.0
  [ade2ca70] Dates
  [56ddb016] Logging

All of this on a brand new julia environment.

[Krastanov]

SearchLight.Migration.status() and last_up() cause the same error

SearchLight.Migration.create_migrations_table has zero methods

This happens on 1.7.0 as well.

[Krastanov]

downgrading Searchlight to 1.0.2 does not help either...

[Krastanov]

Ah... Selecting SQLite in the wizard does not actually set up an sqlite file.

I attempted to clarify the Readme to avoid similar confusions in the future: https://github.com/GenieFramework/GenieAuthentication.jl/pull/10

[essenciary]

Selecting that just installs the dependencies.

Valid point about also setting up the db, but I'm not sure it's doable - how do we know what name to give it, how to connect to MySQL/Postgres, etc?

[Krastanov]

Maybe a warning log message (not an info log message) along the lines of:

To complete the SQLite installation be sure to update the db/config file and to run the migration creation command

To complete the Postgres installation be sure to install and configure a postgres server (independent from Julia) and update the db/config file and to run the migration creation command

[essenciary]

@Krastanov good ideas! For interactive setups it would be nice even ask for the data at the REPL (location for sqlite file, postgres host/user/pass etc) and populate the config file.

[UniqueTokens]

@Krastanov we can do this with Julia e.g. via SearchLightPostgreSQL ahead / without Genie app scaffolding:

Firstly, we add connection data for postgres and database databases:

using SearchLight, SearchLightPostgreSQL
using YAML

path = SearchLight.DB_PATH

database = "postgres"

filename = "$(database)_$(SearchLight.SEARCHLIGHT_DB_CONFIG_FILE_NAME)"

SearchLight.Generator.newconfig(path; filename)

filepath = joinpath(path, filename)
db_conn_data = YAML.load(open(filepath))

host  = ENV["POSTGRES_HOST"]
port = ENV["POSTGRES_PORT"]
username = ENV["POSTGRES_USERNAME"]
password = ENV["POSTGRES_PASSWORD"]

env = "dev"

db_conn_data[env]["host"] = host
db_conn_data[env]["port"] = port
db_conn_data[env]["database"] = database
db_conn_data[env]["username"] = username
db_conn_data[env]["password"] = password

YAML.write_file(filepath, db_conn_data)

database = ENV["POSTGRES_DATABASE"]

db_conn_data[env]["database"] = database

filename = "$(database)_$(SearchLight.SEARCHLIGHT_DB_CONFIG_FILE_NAME)"
filepath = joinpath(path, filename)

YAML.write_file(filepath, db_conn_data)

Then, we create the database database:

using SearchLight, SearchLightPostgreSQL

path = SearchLight.DB_PATH

database = "postgres"

filename = "$(database)_$(SearchLight.SEARCHLIGHT_DB_CONFIG_FILE_NAME)"
filepath = joinpath(path, filename)

postgres_conn_data = SearchLight.Configuration.load(filepath)
postgres_db_handle = SearchLight.connect(postgres_conn_data)

database = ENV["POSTGRES_DATABASE"]
owner = "postgres"

SearchLight.query("CREATE DATABASE $database OWNER $owner")
SearchLight.query("GRANT all ON DATABASE $database TO $owner")

data_frame = SearchLight.query("SELECT 1 FROM pg_database WHERE datname = '$database'")
println(data_frame[!, 1]) # Union{Missing, Int32}[1]

SearchLight.disconnect(postgres_db_handle)

Connect to and use the database database with Julia via SearchLight or scaffold your Genie app...

Finally, we can drop the database database:

using SearchLight, SearchLightPostgreSQL

path = SearchLight.DB_PATH

database = "postgres"

filename = "$(database)_$(SearchLight.SEARCHLIGHT_DB_CONFIG_FILE_NAME)"
filepath = joinpath(path, filename)

postgres_conn_data = SearchLight.Configuration.load(filepath)
postgres_db_handle = SearchLight.connect(postgres_conn_data)

database = ENV["POSTGRES_DATABASE"]

SearchLight.query("DROP DATABASE IF EXISTS $database")

data_frame = SearchLight.query("SELECT 1 FROM pg_database WHERE datname = '$database'")
println(data_frame[!, 1]) # Union{Missing, Int32}[]

SearchLight.disconnect(postgres_db_handle)

@essenciary Would it make sense to adapt this to SQLite and MySQL and integrate it with SearchLight?

Related https://github.com/GenieFramework/SearchLight.jl/issues/46#issuecomment-1026509333, https://github.com/GenieFramework/SearchLight.jl/issues/18#issue-467680103