Setting up SearchLight for database access

You can get the most out of Genie by pairing it with its seamless ORM layer, SearchLight. As a native Julia ORM, it provides excellent support for working with relational databases. The Genie + SearchLight combo can be used to productively develop CRUD (Create-Read-Update-Delete) apps in which where resources are created, read, updated, and deleted.

Adding a database adapter

Let's begin by adding SearchLight to our Genie app. All Genie apps manage their dependencies in their own Julia environment, through their Project.toml and Manifest.toml files.

Enter pkg> mode first by typing ] in julian mode, ie: julia>]. The cursor should change to (MyGenieApp) pkg>. Next, add SearchLight:

(MyGenieApp) pkg> add SearchLight

Setting up the database connection

SearchLight provides a database agnostic API for working with various backends (at the moment, MySQL, SQLite, Postgres and Oracle). Thus, we also need to add the specific adapter. To keep things simple, let's use SQLite for our app. Hence, we'll need the SearchLightSQLite package:

(MyGenieApp) pkg> add SearchLightSQLite

Genie is designed to seamlessly integrate with SearchLight and provides access to various database oriented generators. First we need to tell Genie/SearchLight how to connect to the database. Let's use them to set up our database support. Run this in the Genie/Julia REPL:

julia> Genie.Generator.db_support()

The command will add a db/ folder within the root of the app. The db/connection.yml file tells SearchLight how to connect to the database.

env: ENV["GENIE_ENV"]

dev:
  adapter: SQLite
  database: db/books.sqlite
  config:

This instructs SearchLight to run in the environment of the current Genie app (by default dev), using SQLite for the adapter (backend) and a database stored at db/books.sqlite. The database will be created automatically if it does not exist. We could pass extra configuration options in the config object, but for now we don't need anything else.

HEADS UP

If you are using a different adapter, make sure that the database configured already exists and that the configured user can successfully access it -- SearchLight will not attempt to create the database.

After configuring the connection, we can ask SearchLight to load it up:

julia> using SearchLight

julia> SearchLight.Configuration.load()
Dict{String,Any} with 4 entries:
  "options"  => Dict{String,String}()
  "config"   => nothing
  "database" => "db/books.sqlite"
  "adapter"  => "SQLite"

Let's just go ahead and try it out by connecting to the DB:

julia> using SearchLightSQLite

julia> SearchLight.Configuration.load() |> SearchLight.connect

This should return a SQLite.DB("db/books.sqlite") object. If that is the case, the connection succeeded and we got back a SQLite database handle.

PRO TIP

Each database adapter exposes a CONNECTIONS collection where we can access the connection:

julia> SearchLightSQLite.CONNECTIONS
1-element Array{SQLite.DB,1}:
 SQLite.DB("db/books.sqlite")

Awesome! If all went well you should have a books.sqlite database in the db/ folder.

shell> tree db
db
├── books.sqlite
├── connection.yml
├── migrations
└── seeds

Auto-loading the DB configuration

Genie comes with a special file called an initializer, which automatically loads the database configuration and sets up SearchLight. Check config/initializers/searchlight.jl to see how this is done.

Heads up!

All the *.jl files placed into the config/initializers/ folder are automatically included by Genie upon starting the Genie app. They are included early (upon initialisation), before the controllers, models, views, are loaded.