imdrasil/jennifer_sqlite3_adapter

SQLite3 adapter for Jennifer ORM

Jennifer SQLite3 adapter

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

2. 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

jennifer_sqlite3_adapter's Issues

Moving the SQLite adapter into the jennifer codebase

Would it be possible to merge the SQLite3 adapter into the main codebase or is there a good reason to keep it outside?

Enabling foreign key support incorrect

jennifer_sqlite3_adapter/src/jennifer_sqlite3_adapter.cr

Lines 51 to 57 in 7670931

	def prepare
	super
	with_connection do |conn|
	conn.exec "PRAGMA foreign_keys = ON"
	end
	end

This code is enabling foreign key support. The problem is that this enabling needs to be done on every connection, not just once. Instead of calling with_connection, you should use the setup_connection callback provided on DB::Database.

To verify that the bug exists, you'd have to do multiple sql statements that rely on foreign key constraints to work with different database connections.

SQLite docs on enabling foreign key support (The 3rd paragraph in that section states that it needs to happen on every connection): https://www.sqlite.org/foreignkeys.html#fk_enable

"Foreign key constraint failed" when dropping a column

When dropping a column, I get the error above.

The code fails in the drop_column method during the attempt to drop the table. (I used puts; is the logger instance available here?)

The migration:

class AddDeletedTagToNote < Jennifer::Migration::Base
  def up
    change_table :notes do |t|
      t.add_column :deleted, :bool, { :default => false }
    end
  end

  def down
    change_table :notes do |t|
      t.drop_column :deleted if column_exists?(:notes, :deleted)
    end
  end
end

The error call stack when attempting a down migration:

lib/jennifer/src/jennifer/adapter/base.cr:74:9 in 'exec'
lib/jennifer/src/jennifer/adapter/schema_processor.cr:87:9 in 'drop_table'
lib/jennifer_sqlite3_adapter/src/sqlite3/schema_processor.cr:195:9 in 'drop_table'
lib/jennifer_sqlite3_adapter/src/sqlite3/schema_processor.cr:28:33 in 'drop_column'
lib/jennifer/src/jennifer/migration/table_builder/change_table.cr:221:36 in 'process'
lib/jennifer/src/jennifer/migration/base.cr:579:9 in 'process_builder'
lib/jennifer/src/jennifer/migration/base.cr:268:9 in 'down'
lib/jennifer/src/jennifer/migration/runner.cr:129:13 in 'process_down_migration'

I tried doing the drop table inside another ignore foreign keys and that did not work either.

def drop_column(table, name)
  ignore_foreign_keys do
    temp_table_name = "#{table}_temp"
    t = find_table(table)
    columns = t.columns.reject(&.name.==(name.to_s))

    # Create new table
    create_table(temp_table_name, columns, t.foreign_keys)

    # Copy data
    copy_data(temp_table_name, table, columns.map(&.name))

    # Drop old table
    puts "Dropping old table #{table}"
    ignore_foreign_keys { drop_table(table) }

    # Rename new table
    puts "Renaming temp table #{temp_table_name} to #{table}"
    rename_table(temp_table_name, table)

    # Add indexes
    create_indexes(t)
  end
end

The output:

Dropping old table notes
exec PRAGMA foreign_keys=OFF
PRAGMA foreign_keys=OFF Result DB::ExecResult(@rows_affected=29, @last_insert_id=60)
lib/jennifer/src/jennifer/adapter/base.cr:74:9 in 'exec'
lib/jennifer/src/jennifer/adapter/schema_processor.cr:87:9 in 'drop_table'
lib/jennifer_sqlite3_adapter/src/sqlite3/schema_processor.cr:195:9 in 'drop_table'
# lines omitted
 FOREIGN KEY constraint failed.
Original query was:
DROP TABLE notes

UNIQUE INDEX is not working

Proposed correction : #2

Problem with dropping an indexed column in a migration

Dropping an indexed column fails with the message:

no such column: column.
Original query was:
CREATE UNIQUE INDEX table_column ON table (column)

Example:

change_table :table do |t|
  t.drop_index :column
  t.drop_column :column
end

The drop_column method in the SQLite3 adapter was just recently added and tries to create all indexes on the new table. However, if the dropped column has an index, this fails. I see two solutions to this:

1. Change the order of execution in a change_table migration block, so that an index is dropped before the columns are dropped (this would be a fix in Jennifer, I think).
2. Tell the Jennifer::SQLite3::SchemaProcessor#drop_column to create each index except the ones that contain the dropped column. However this would automatically drop all indexes related to a dropped column when calling #drop_column. I'm not sure how other ORMs handle this situation but I think this may be too implicit.

Possible workaround:
Use #drop_index in a separate call in the migration like this:

drop_index(:table, :column)
change_table :table do |t|
  t.drop_column :column
end

Environment:

Crystal: 1.1.1
jennifer: 0.11.1
jennifer_sqlite3_adapter: 0.3.2

Timestamp fails casting using preload

I'm getting this one with sqlite when calling "includes":
Column User.created_at can't be casted from String to it's type - (Time | Nil) (Jennifer::DataTypeCasting)

Thats the call:
Skin.all.includes(:creator)

Its a belongs_to relation:
belongs_to :creator, User, foreign: "creator_id"

Seems like the belongs_to relation doesnt know how to cast text-timestamps of sqlite to timestamps.

It is recommended to specify the version of crystal-sqlite3

The new crystal-sqlite3 (version 0.14.0) upgraded crystal-db (version 0.7.0), but is not compatible with this project:

SQLite3::Statement does not support Array(Array(Char) \| Array(Float32) \| Array(Float64) \| Array(Int16) \| Array(Int32) \| Array(Int64) \| Array(String) \| Bool \| Char \| Float32 \| Float64 \| Int16 \| Int32 \| Int64 \| Int8 \| JSON::Any \| PG::Geo::Box \| PG::Geo::Circle \| PG::Geo::Line \| PG::Geo::LineSegment \| PG::Geo::Path \| PG::Geo::Point \| PG::Geo::Polygon \| PG::Numeric \| Slice(UInt8) \| String \| Time \| Time::Span \| UInt32 \| Nil) params.
--
SQLite3::Statement does not support Array(Array(Char) | Array(Float32) | Array(Float64) | Array(Int16) | Array(Int32) | Array(Int64) | Array(String) | Bool | Char | Float32 | Float64 | Int16 | Int32 | Int64 | Int8 | JSON::Any | PG::Geo::Box | PG::Geo::Circle | PG::Geo::Line | PG::Geo::LineSegment | PG::Geo::Path | PG::Geo::Point | PG::Geo::Polygon | PG::Numeric | Slice(UInt8) | String | Time | Time::Span | UInt32 | Nil) params.
Original query was:
SELECT sqlite_master.* FROM sqlite_master WHERE sqlite_master.type = ? AND sqlite_master.tbl_name IN(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)  | ["table", "migration_versions", "admins", "answers", "anti_messages", "appeals", "block_contents", "clean_modes", "error_counts", "format_limits", "froms", "groups", "languages", "max_lengths", "private_menus", "questions", "reports", "subfunctions", "templates", "toggles", "true_indices", "votes"]
lib/jennifer/src/jennifer/query_builder/model_query.cr:78:28 in 'to_a'
lib/jennifer_sqlite3_adapter/src/jennifer_sqlite3_adapter.cr:65:9 in 'tables_column_count'
lib/jennifer/src/jennifer/adapter/base.cr:266:5 in 'prepare'
lib/jennifer/src/jennifer/adapter.cr:51:9 in 'adapter'
lib/jennifer/src/jennifer/adapter.cr:60:7 in 'default_adapter'
lib/jennifer/src/jennifer/migration/runner.cr:92:9 in 'default_adapter'
lib/jennifer/src/jennifer/migration/runner.cr:10:9 in 'migrate'
lib/jennifer/src/jennifer/migration/runner.cr:30:9 in 'migrate'
lib/jennifer/src/jennifer/sam.cr:6:5 in '->'
lib/sam/src/sam/task.cr:255:3 in 'call'
lib/sam/src/sam.cr:43:5 in 'invoke'
lib/sam/src/sam.cr:77:7 in 'process_tasks'
lib/sam/src/sam.cr:56:7 in 'help'
sam.cr:74:1 in '__crystal_main'

I have to manually add the sqlite3 dependency to the fixed version:

sqlite3:
  github: crystal-lang/crystal-sqlite3
  version: ~> 0.13.0

Error dropping foreign key

Create migrations:

class AddForeignKeys < Jennifer::Migration::Base
  def up
    add_foreign_key :matches, :workspaces, column: :workspace_id, primary_key: :id
  end

  def down
    drop_foreign_key :matches, :workspaces
  end
end

Rollback error:

lib/jennifer_sqlite3_adapter/src/sqlite3/schema_processor.cr:204:38 in 'to_table_from_fk_name'
lib/jennifer_sqlite3_adapter/src/sqlite3/schema_processor.cr:91:20 in 'drop_foreign_key'
lib/jennifer/src/jennifer/migration/table_builder/drop_foreign_key.cr:13:11 in 'process'
lib/jennifer/src/jennifer/migration/base.cr:575:9 in 'process_builder'
lib/jennifer/src/jennifer/migration/base.cr:544:9 in 'drop_foreign_key'
db/migrations/20191208163714773_drop_foreign_keys.cr:3:5 in 'up'
lib/jennifer/src/jennifer/migration/runner.cr:106:13 in 'process_up_migration'
lib/jennifer/src/jennifer/migration/runner.cr:18:11 in 'migrate'
lib/jennifer/src/jennifer/migration/runner.cr:27:9 in 'migrate'
lib/jennifer/src/jennifer/sam.cr:6:5 in '->'
lib/sam/src/sam/task.cr:255:3 in 'call'
lib/sam/src/sam/task.cr:70:7 in 'invoke'
lib/sam/src/sam/task.cr:35:27 in 'call'
lib/sam/src/sam.cr:43:5 in 'invoke'
lib/sam/src/sam.cr:77:7 in 'process_tasks'
lib/sam/src/sam.cr:56:7 in 'help'
sam.cr:14:1 in '__crystal_main'
/home/hentioe/.asdf/installs/crystal/0.31.1/share/crystal/src/crystal/main.cr:97:5 in 'main_user_code'
/home/hentioe/.asdf/installs/crystal/0.31.1/share/crystal/src/crystal/main.cr:86:7 in 'main'
/home/hentioe/.asdf/installs/crystal/0.31.1/share/crystal/src/crystal/main.cr:106:3 in 'main'
__libc_start_main
../sysdeps/x86_64/start.S:122:0 in '_start'
???
Bad foreign key name - fk_cr_42847c35e4

Achieve better concurrency through Write Ahead Logging - WAL

Hello,

Before I begin, let me say how much I respect all the work done on the Jennifer ORM as a whole. It's a great achievement and I'm really considering it for my next Amber project. The ActiveRecord pattern and the builtin support for preloading relationships would make development much easier and specially welcoming for people with a Rails background.

I'm certain this adapter delivers great value during development work. What I'm proposing is extending such value to production environments by means of enabling higher concurrency. This could be attained through Write Ahead Logging (WAL), which can be enabled by a simple pragma declaration. To better ilustrate, the Ecto adapter for sqlite in the Elixir ecosystem already uses this technique, as seen
here.

Essentially, WAL enables faster database access because of a different locking mechanism, which allows multiple readers or a single writer. With that in mind, SQLite databases should make a perfect match for read heavy applications in single machine deployments. There's more info on the subject available here.

Last, but not least, simply enabling WAL mode could still leave some concurrency issues. The Elixir/Ecto folks solved the problem by wrapping all database calls inside a GenServer, effectively serializing all access. In Crystal land, a similar approach could be used, but I think some sort of mutex could provide even better results.

Fortunately, there's already a Crystal shard that implements the adequate locking strategy for SQLite in WAL mode.

Do you think such functionality could be added to this Sqlite adapter? I could enable WAL mode and add the synchronization logic inside my own code, but I think such features really belong in a library such as this one. Also, the whole community could benefit from faster database performance. What do you think?

Best regards,

Gabriel.

Unfix sqlite3 version

The dependency on fixed version "0.16.0" for the SQLite binding is preventing my use case because I need to use a feature of the binding that is only on master.

sqlite3: not found

I am attempting to create a sample Kemal application using jennifer and sam and I get the above error when running command make sam db:create.

database.cr:

require "jennifer"
require "jennifer_sqlite3_adapter"

APP_ENV = ENV["APP_ENV"]? || "development"

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

Log.setup "db", :debug, Log::IOBackend.new(formatter: Jennifer::Adapter::DBFormatter)

shard.yml:

version: 0.1.0

authors:
  - your-name-here <your-email-here>

targets:
  travels_api:
    main: src/travels_api.cr

dependencies:
  kemal:
    github: kemalcr/kemal
  jennifer:
    github: imdrasil/jennifer.cr
    version: "~> 0.12.0"
  sqlite3:
    github: crystal-lang/crystal-sqlite3
    version: "0.18.0"
  jennifer_sqlite3_adapter:
    github: imdrasil/jennifer_sqlite3_adapter
    version: "~> 0.4.0"
  sam:
    github: imdrasil/sam.cr
    version: 0.4.2


crystal: 1.8.2

license: MIT

Comand result:

lib/jennifer/src/jennifer/adapter/command_shell/i_command_shell.cr:19:9 in 'invoke'
lib/jennifer/src/jennifer/adapter/command_shell/bash.cr:14:9 in 'execute'
lib/jennifer/src/jennifer/adapter/db_command_interface.cr:24:9 in 'execute'
lib/jennifer_sqlite3_adapter/src/sqlite3/command_interface.cr:12:9 in 'create_database'
lib/jennifer/src/jennifer/adapter/base.cr:216:9 in 'create_database'
lib/jennifer/src/jennifer/migration/runner.cr:36:11 in 'create'
lib/jennifer/src/jennifer/sam.cr:36:5 in '->'
lib/sam/src/sam/task.cr:56:39 in 'call'
lib/sam/src/sam/execution.cr:19:7 in 'invoke'
lib/sam/src/sam.cr:37:5 in 'invoke'
lib/sam/src/sam.cr:55:7 in 'process_tasks'
lib/sam/src/sam.cr:43:5 in 'help'
sam.cr:13:1 in '__crystal_main'
/usr/share/crystal/src/crystal/main.cr:115:5 in 'main_user_code'
/usr/share/crystal/src/crystal/main.cr:101:7 in 'main'
/usr/share/crystal/src/crystal/main.cr:127:3 in 'main'
/lib/x86_64-linux-gnu/libc.so.6 in '??'
/lib/x86_64-linux-gnu/libc.so.6 in '__libc_start_main'
/root/.cache/crystal/crystal-run-sam.tmp in '_start'
???
DB command interface exit code 127: --: 1: sqlite3: not found

make: *** [Makefile:7: sam] Error 1

Jennifer 0.8.0 support

Jennifer 0.8.0 has breaking changes therefore adapter should release a new version supporting them

Error: Can't VACUUM in migration

Can I access a connection outside a transaction within a migration?

Code:

def up
  exec "PRAGMA page_size=8192;"
  exec "VACUUM;"

Error:

Unhandled exception: cannot VACUUM from within a transaction.
Original query was:
VACUUM; (Jennifer::BadQuery)

INSERT OR IGNORE

I need to insert large volumes of occasionally duplicate (< .00001%) values.
I'm currently using Table.all.insert(fields, array_of_values). Is there a way to use INSERT OR IGNORE or UPSERT without destroying data?

I'm ok with a 1 off custom query but I do have a BLOB field that needs quoting.

Error when dropping a reference

When dropping a reference (using drop_reference :account) I received the following error:

unknown column "account_id" in foreign key definition.

I created a small repo to reproduce the error (see https://github.com/richardboehme/jennifer-drop-reference-bug).

The error occurred because Jennifer tried to drop the column account_id while a foreign key on this column is still active. When dropping a column with the sqlite3 adapter, a temporary table is created that will still contain the foreign key to account_id but not the column itself. This results in the error.

I took a look at Jennifer::SQLite3::SchemaProcessor#drop_column and noticed that normally foreign keys should be ignored. However, I guess this does not apply when trying to create a foreign key for a column that does not exist.

I looked up sqlite3 documentation and there seems to be support for ALTER TABLE DROP COLUMN. Is there a reason that this is not used in the sqlite3 adapter (in contrast to other Jennifer adapters)?

Even then I guess removing the column before removing the foreign key will not work as well, so maybe this is a general Jennifer error? Maybe the ideal way of solving this issue would be to drop foreign keys first, before removing the column?

I'd be happy to help fixing this bug, I just need some hints what the ideal solution would look like.

A workaround would be to first drop the foreign key and than drop the column:

change_table(:pictures) do |t|
  t.drop_foreign_key to_table: :accounts
end
change_table(:pictures) do |t|
  t.drop_reference :account
end

Environment:

• crystal 0.36.1
• jennifer (master, Commit: af34b2c5f906e65148b7101f5473b866de6e6a32)
• jennifer_sqlite3_adapter (master, Commit: 9f4eb94)