jennifer_sqlite3_adapter v0.4.0

SQLite3 adapter for Jennifer ORM

Jennifer SQLite3 adapter Latest Release Docs

SQLite3 adapter for Jennifer.

Installation

  1. Add the dependencies to your shard.yml:
dependencies:
  sqlite3:
    github: crystal-lang/crystal-sqlite3
    version: "0.18.0"
  jennifer_sqlite3_adapter:
    github: imdrasil/jennifer_sqlite3_adapter
    version: "~> 0.4.0"

Current adapter version is tested with 0.18.0 sqlite3 driver but other versions may also work

  1. Run shards install

Usage

This shard supports Jennifer 0.12.0 and above. It is tested with SQLite 3.31.1 version. Some of features may not work on older versions.

require "jennifer"
require "jennifer_sqlite3_adapter"

Jennifer::Config.configure do |conf|
  conf.adapter = "sqlite3"
  conf.host = "."
  conf.db = "test.db"
end

user and password configurations are not required as SQLite doesn't provide user authentication and they are ignored. host options is used to specify database file folder path, db - file name.

Supported data types

Method SQLite3 Crystal type
#bool INTEGER Bool
#integer INTEGER Int32
#bigint INTEGER Int32
#short INTEGER Int32
#tinyint INTEGER Int32
#float REAL Float64
#double REAL Float64
#real REAL Float64
#text TEXT String
#string TEXT String
#varchar TEXT String
#date_time TEXT Time
#timestamp TEXT Time
#json JSON JSON::Any

JSON support

SQLite added native support for JSON data type starting from 3.38.0 (experimental support was added in 3.37.2). This means that you need to ensure you use correct version if you need access to this feature.

Here is the list of extra JSON functions provided by the adapter:

  • json_extract
Jennifer::Query["users"].where { json_extract(_interests, "$.likes[1]") == "reading" }
  • json_array_length
Jennifer::Query["users"].where { json_array_length(_interests, "$.likes") > 10 }

Altering table

SQLite has no mechanism to alter existing table (except renaming it). Therefore operations, listed below, do multiple steps to achieve altering table: coping table schema to new temp table, coping data to new table, extending new table with required options, dropping old table and renaming temp table to original name:

  • dropping column
  • changing column
  • adding foreign key
  • dropping foreign key

It is necessary to notice that all those operations keep all data and indexes.

Foreign key

At a startup adapter automatically enable foreign keys executing PRAGMA foreign_keys = ON. But be aware using migrations - as described above, some table manipulations will recreate a table. To avoid breaking of any foreign key, adapter will try to disable them until the end of a manipulation, but this is possible only outside of a transaction. Therefore in a such kind of migrations add with_transaction false at the class body to disable automatic transaction around migration methods.

Contributing

  1. Fork it (https://github.com/imdrasil/jennifer_sqlite3_adapter/fork)
  2. Create your feature branch (git checkout -b my-new-feature)
  3. Commit your changes (git commit -am 'Add some feature')
  4. Push to the branch (git push origin my-new-feature)
  5. Create a new Pull Request

Testing

To run common test suite simply run crystal spec. To include JSON-related tests - specify a json version that supports JSON - SQLITE_VERSION=3389999 crystal spec. Same for migration - SQLITE_VERSION=3389999 crystal sam.cr db:create @ db:migrate

Repository

jennifer_sqlite3_adapter

Owner
Statistic
  • 13
  • 7
  • 5
  • 12
  • 4
  • 6 months ago
  • January 11, 2019
License

MIT License

Links
Synced at

Wed, 25 Dec 2024 05:54:55 GMT

Languages