An extra-lightweight Ruby gem for working with SQLite3 databases

Overview

Extralite

Extralite is an extra-lightweight SQLite3 wrapper for Ruby. It provides a single class with a minimal set of methods to interact with an SQLite3 database.

Features

  • A variety of methods for different data access patterns: row as hash, row as array, single single row, single column, single value.
  • Iterate over records with a block, or collect records into an array.
  • Parameter binding.
  • Correctly execute strings with multiple semicolon-separated queries (handy for creating/modifying schemas).
  • Get last insert rowid.
  • Get number of rows changed by last query.
  • Loading extensions is autmatically enabled. You can find some useful extensions here: https://github.com/nalgeon/sqlean.

Usage

require 'extralite'

# open a database
db = Extralite::Database.new('/tmp/my.db')

# get query results as array of hashes
db.query('select 1 as foo') #=> [{ :foo => 1 }]
# or:
db.query_hash('select 1 as foo') #=> [{ :foo => 1 }]
# or iterate over results
db.query('select 1 as foo') { |r| p r }
# { :foo => 1 }

# get query results as array of arrays
db.query_ary('select 1, 2, 3') #=> [[1, 2, 3]]
# or iterate over results
db.query_ary('select 1, 2, 3') { |r| p r }
# [1, 2, 3]

# get a single row as a hash
db.query_single_row("select 1 as foo") #=> { :foo => 1 }

# get single column query results as array of values
db.query_single_column('select 42') #=> [42]
# or iterate over results
db.query_single_column('select 42') { |v| p v }
# 42

# get single value from first row of results
db.query_single_value("select 'foo'") #=> "foo"

# parameter binding (works for all query_xxx methods)
db.query_hash('select ? as foo, ? as bar', 1, 2) #=> [{ :foo => 1, :bar => 2 }]

# get last insert rowid
rowid = db.last_insert_id

# get number of rows changed in last query
number_of_rows_affected = db.changes

# get db filename
db.filename #=> "/tmp/my.db"

# load an extension
db.load_extension('/path/to/extension.so')
Comments
  • dyld: lazy symbol binding failed: Symbol not found: _prepare_multi_stmt

    dyld: lazy symbol binding failed: Symbol not found: _prepare_multi_stmt

    Hello! I just helped make an ETL app that used a bunch of threaded queries, not realizing there would be GVL (since it's IO). I read about this gem in this week's Ruby Weekly and was excited for the prospect!

    When trying to use this from my mac, and another developer's mac, we get the following error:

    [2] pry(main)> DB = Sequel.connect('extralite:blog.db')
    dyld: lazy symbol binding failed: Symbol not found: _prepare_multi_stmt
      Referenced from: /Users/ttilberg/.asdf/installs/ruby/3.0.2/lib/ruby/gems/3.0.0/gems/extralite-1.10/lib/extralite_ext.bundle
      Expected in: flat namespace
    
    dyld: Symbol not found: _prepare_multi_stmt
      Referenced from: /Users/ttilberg/.asdf/installs/ruby/3.0.2/lib/ruby/gems/3.0.0/gems/extralite-1.10/lib/extralite_ext.bundle
      Expected in: flat namespace
    
    [1]    62097 abort      pry
    

    Are there some c dependencies we need to make sure are installed? Can we add it to the readme, and better yet, is there a way to present a better error message?

    opened by ttilberg 2
  • CI: Use ruby/setup-ruby@v1

    CI: Use ruby/[email protected]

    This PR is a tiny improvement to the GitHub Actions setup.

    In ruby/[email protected], there is a caching feature, and this commit enables it. That Action is managed and developed by the Ruby community.

    Minor: To avoid 3.0 ever being stringified as "3", we quote the "ruby" matrix element for Ruby 3.0.

    opened by olleolleolle 2
  • Add prepared statements

    Add prepared statements

    Sketch for interface

    db = Extralite::Database.new('/tmp/my.db')
    stmt = db.prepare('select name from users where id = ?')
    stmt.class #=> Extralite::PreparedStatement
    
    # PreparedStatement has more or less the same querying interface as Database
    stmt.query(42) # id=42
    stmt.query_single_value(42)
    # ...etc
    

    In terms of implementation, very little change is needed to the code dealing with running queries. We just need to keep the sqlite3_stmt struct in the PreparedStatement object, instead of throwing it away.

    opened by ciconia 0
  • Add // to uri scheme

    Add // to uri scheme

    The Readme (and the blog post) are missing the // from the scheme delimiter, and as such Sequel was not connecting to the file correctly.

    Can you also update the blog post?

    [1] pry(main)> require 'sequel'
    => true
    [2] pry(main)> db1 = Sequel.connect 'extralite:my.db'
    => #<Sequel::Extralite::Database: "extralite:my.db">
    [3] pry(main)> db1.opts
    => {:max_connections=>1,
     :database=>"",  # <----
     :orig_opts=>{},
     :uri=>"extralite:my.db",
     :adapter=>"extralite",
     :adapter_class=>Sequel::Extralite::Database,
     :single_threaded=>false}
    [4] pry(main)> db2 = Sequel.connect 'extralite://my.db'
    => #<Sequel::Extralite::Database: "extralite://my.db">
    [5] pry(main)> db2.opts
    => {:database=>"my.db",   # <----
     :orig_opts=>{},
     :uri=>"extralite://my.db",
     :adapter=>"extralite",
     :adapter_class=>Sequel::Extralite::Database,
     :single_threaded=>false}
    
    opened by ttilberg 0
  • What happened to the Sequel adapter?

    What happened to the Sequel adapter?

    require 'extralight'
    require 'sequel'
    DB = Sequel.connect 'extralight://db.sqlite3'
    table = DB[:some_table]
    

    ... alas ...

    Sequel::AdapterNotFound: LoadError: cannot load such file -- sequel/adapters/extralight
    

    used to work but now not so anymore, and https://github.com/jeremyevans/sequel/tree/master/lib/sequel/adapters matches that...

    opened by sixtyfive 5
  • `sequel` binary fails when performing migrations

    `sequel` binary fails when performing migrations

    At a cursory glance, the conn instance being called as the receiver of execute is not an instance of Sequel::Extralite::Database, but Extralite::Database.

    (rdbg) ls conn    # outline command
    Extralite::Database#methods: 
      changes    close       closed?              filename          last_insert_rowid   load_extension       prepare  query
      query_ary  query_hash  query_single_column  query_single_row  query_single_value  transaction_active?
    
    ruby 3.0.3p157 (2021-11-24 revision 3fb7d2cadc) [aarch64-linux]
    
    $ sequel -t -m migrate extralite://
    /usr/local/bundle/gems/sequel-5.54.0/lib/sequel/database/logging.rb:76:in `public_send': undefined method `execute' for #<Extralite::Database:0x0000aaaaff614b08> (NoMethodError)
            from /usr/local/bundle/gems/sequel-5.54.0/lib/sequel/database/logging.rb:76:in `block in log_connection_execute'
            from /usr/local/bundle/gems/sequel-5.54.0/lib/sequel/database/logging.rb:38:in `log_connection_yield'
            from /usr/local/bundle/gems/sequel-5.54.0/lib/sequel/database/logging.rb:76:in `log_connection_execute'
            from /usr/local/bundle/gems/sequel-5.54.0/lib/sequel/adapters/shared/sqlite.rb:293:in `begin_new_transaction'
            from /usr/local/bundle/gems/sequel-5.54.0/lib/sequel/database/transactions.rb:360:in `begin_transaction'
            from /usr/local/bundle/gems/sequel-5.54.0/lib/sequel/database/transactions.rb:248:in `_transaction'
            from /usr/local/bundle/gems/sequel-5.54.0/lib/sequel/database/transactions.rb:233:in `block in transaction'
            from /usr/local/bundle/gems/sequel-5.54.0/lib/sequel/connection_pool/threaded.rb:92:in `hold'
            from /usr/local/bundle/gems/sequel-5.54.0/lib/sequel/database/connecting.rb:269:in `synchronize'
            from /usr/local/bundle/gems/sequel-5.54.0/lib/sequel/database/transactions.rb:195:in `transaction'
            from /usr/local/bundle/gems/sequel-5.54.0/lib/sequel/adapters/shared/sqlite.rb:203:in `apply_alter_table'
            from /usr/local/bundle/gems/sequel-5.54.0/lib/sequel/database/schema_methods.rb:437:in `apply_alter_table_generator'
            from /usr/local/bundle/gems/sequel-5.54.0/lib/sequel/database/schema_methods.rb:70:in `alter_table'
            from /usr/local/bundle/gems/sequel-5.54.0/lib/sequel/extensions/migration.rb:628:in `schema_dataset'
            from /usr/local/bundle/gems/sequel-5.54.0/lib/sequel/extensions/migration.rb:462:in `initialize'
            from /usr/local/bundle/gems/sequel-5.54.0/lib/sequel/extensions/migration.rb:526:in `initialize'
            from /usr/local/bundle/gems/sequel-5.54.0/lib/sequel/extensions/migration.rb:409:in `new'
            from /usr/local/bundle/gems/sequel-5.54.0/lib/sequel/extensions/migration.rb:409:in `run'
            from /usr/local/bundle/gems/sequel-5.54.0/lib/sequel/extensions/migration.rb:374:in `apply'
            from /usr/local/bundle/gems/sequel-5.54.0/bin/sequel:163:in `<top (required)>'
            from /usr/local/bundle/bin/sequel:25:in `load'
            from /usr/local/bundle/bin/sequel:25:in `<main>'
    
    $ gem list sequel extralite -l
    
    *** LOCAL GEMS ***
    
    sequel (5.54.0)
    
    *** LOCAL GEMS ***
    
    extralite-bundle (1.14)
    
    opened by adam12 4
Owner
Digital Fabric
Software for a better world
Digital Fabric
An SQLite3 driver for Elixir

Exqlite An Elixir SQLite3 library. If you are looking for the Ecto adapater, take a look at the Ecto SQLite3 library. Documentation: https://hexdocs.p

elixir-sqlite 135 Oct 5, 2022
A lightweight Bedorck Dedicated Server Plugin Loader

LiteLoader 简体中文 A lightweight Bedorck Dedicated Server Plugin Loader Based on BedrockX Install Download LiteLoader from Releases or Actions, unzip it

null 483 Sep 26, 2022
SQLite3++ - C++ wrapper of SQLite3 API

ANNOUNCEMENTS Use files in headeronly_src directory. The files in src are exactly same but in the form of h/cpp files, which you need to compile and l

Wongoo Lee 500 Sep 23, 2022
A ShaderToy Clone for O3DE (Gem + Demo Project)

A ShaderToy Clone for O3DE (Gem + Demo Project)

null 7 Aug 8, 2022
Ludum Dare 50 - Gem's Bond

ldjam50 - Gem's Bond ludum-dare 50 - Delay the inevitable Itch.io ldjam Waves of monsters are coming to destroy your precious magic gem! Build and upg

Thomas Foucault 3 May 2, 2022
NIF interface from Erlang to LMDB databases

Elmdb, an Erlang NIF for LMDB This is an Erlang NIF for OpenLDAP's Lightning Memory-Mapped Database (LMDB) database library. LMDB is a fast, compact k

null 3 Sep 27, 2021
Verneuil is a VFS extension for SQLite that asynchronously replicates databases to S3-compatible blob stores.

Verneuil: streaming replication for sqlite Verneuil1 [vɛʁnœj] is a VFS (OS abstraction layer) for sqlite that accesses local database files like the d

Backtrace Labs 288 Sep 25, 2022
Tntdb is a c++-class-library for easy access to databases

Tntdb is a c++-class-library for easy access to databases

Tommi Mäkitalo 31 Aug 1, 2022
Wgeo, or "wi-fi geolocator", is a cross-platform C/C++ library for wifi-based device geolocation, utilising public wireless access point location databases

wgeo Wgeo, or "wi-fi geolocator", is a cross-platform C/C++ library for wifi-based device geolocation, utilising public wireless access point location

Xavier Maruff 2 Jan 30, 2022
An Sqlite3 Elixir library

Exqlite An SQLite3 library with an Ecto adapter implementation. Caveats When using the Ecto adapter, all prepared statements are cached using an LRU c

Matthew Johnston 132 Sep 10, 2022
An SQLite3 driver for Elixir

Exqlite An Elixir SQLite3 library. If you are looking for the Ecto adapater, take a look at the Ecto SQLite3 library. Documentation: https://hexdocs.p

elixir-sqlite 135 Oct 5, 2022
DOS CMD line build of a current SQLite3

DOSQLite This is a DOS/32 build of the command line tool for SQLite 3 based on sqlite-amalgamation-3340100. It was built using DJGPP like my other pro

null 7 Sep 26, 2021
SQLiteC++ (SQLiteCpp) is a smart and easy to use C++ SQLite3 wrapper.

SQLiteC++ SQLiteC++ (SQLiteCpp) is a smart and easy to use C++ SQLite3 wrapper. About SQLiteC++: SQLiteC++ offers an encapsulation around the native C

Sébastien Rombauts 1.5k Sep 27, 2022
This is a demo for sqlite3.

说明 This is a demo for sqlite3. sqlite3 基础知识 sqlite3 命令分两类 系统命令 以 . 开头的命令 .q 退出sqlite3命令模式 .open 创建一个数据库 .databases 列出数据库 .schema 列出表结构 .tables 列出数据库中的

流浪小兵 1 Nov 24, 2021
VSQLite++ - A welldesigned and portable SQLite3 Wrapper for C++ (C)

VSQLite++ - A welldesigned and portable SQLite3 Wrapper for C++ (C)

Vinzenz 'evilissimo' Feenstra 27 Dec 29, 2021
This is a fork of prboom+ with extra tooling for demo recording and playback, with a focus on speedrunning.

dsda-doom v0.15.1 This is a fork of prboom+ with extra tooling for demo recording and playback, with a focus on speedrunning. Heretic Support (beta) D

Ryan Krafnick 114 Sep 26, 2022
Add extra features to Discord via patches!

DiscordExtras An iOS tweak that lets you apply patches the iOS Discord client. Available on my repo here. Components DiscordExtrasPrefs This includes

Zoey 32 Sep 28, 2022
Rajesh Kumar Sah 1 Nov 20, 2021
Extra Credit Project for CS 411

#Extra Credit Project for CS 411 CS411ExtraCredit Commands to run JOINS with Nation and Region Files *NOTE region.tbl files and nation.tbl files are a

null 1 Nov 27, 2021
Extra CMake Modules for YARP and friends

YCM Extra CMake Modules for YARP and friends Documentation Online documentation is available

Robotology 42 Sep 12, 2022