How to statically link SQLite extension?

Help & Support
1

Hello again,

I’ve been developing on my Mac, which has SQLite version 3.32.3 2020-06-18 14:16:19 installed. Using the crystal-sqlite3 shard has worked wonderfully.

Now, I’m trying to move build and use my program on my Ubuntu 20.04 server. It doesn’t even have sqlite3 installed. Yet, when I crystal build --release --static it still seems to work? Pulled in from the shard somehow? I’m not sure.

Anyways, what doesn’t work is the sqlite3 uuid extension:

select uuid();
unknown function: uuid() (SQLite3::Exception)

I’m a bit in over my head here… I’m not used to compiling things, and don’t truly know what dynamic/static linking does.

How can I get the right bits of sqlite into my binary to make uuid() work? :upside_down_face:

2

Try to compile using alpine container and then deploy the generated binary on Ubuntu 20.04

CMDS="
apk add --update --no-cache --force-overwrite \
    sqlite-dev sqlite-static
shards install --ignore-crystal-version --production
shards build --static --release --stats --time
"

docker run --rm -it -v $PWD:/workspace -w /workspace crystallang/crystal:1.0.0-alpine /bin/sh -c "$CMDS"
1 Like
3

@aravindavk thanks for your suggestion!

I hop onto my Ubuntu machine to try and build as you suggested. (First as is, then just into the container to try it manually) same results either way.

After a long wait of stuff doing work:

/workspace # crystal build --release --static --verbose src/hcb.cr 
Killed

Hmm, I don’t know what or why - but something isn’t happy.

Update:

So I tried the above again locally, in macOS and it did manage to build.

However, when I transferred the binary back to my Ubuntu server, it started up fine until I tried to use something that called the uuid function again and get the same:

unknown function: uuid() (SQLite3::Exception)

So… back to start. How do I enable this sqlite extension? Was sqlite-dev sqlite-static supposed to include it?

I’ve created an example repo here.

4

This seems more like an SQLite question than a Crystal question. UUID seems to be an extension. You probably need to install the extension separately, it doesn’t seem to be provided in the system packages. Instructions: GitHub - benwebber/sqlite3-uuid: SQLite extension for generating UUIDs
The extension is probably installed on your Mac (either it’s included by default or you have installed it before, IDK).

(Disclaimer: I’m not familiar with SQLite, so this is just a hunch)

1 Like
5

@straight-shoota thanks, and I think you are right. In fact, this is more a me problem. Crystal is my first time really using a compiled language, and I’m not really sure what I’m doing :crazy_face:

So you think, if I manage to compile/build Sqlite3 with extensions in Linux, then when I link it… it should just work? It’s all magic to me.

1 Like
7

Here are my notes from the last time I needed a SQLite feature that hadn’t landed in a release yet. I don’t know what you need to do to enable modules, but hopefully this will be helpfull in getting started.

On Ubuntu you will need build-essential installed at a minimum.

sudo apt update
sudo apt install build-essential

You can get the SQLite source code from SQLite Download Page

Build SQLite

Download and build SQLite

(this creates the sqlite command)


wget https://sqlite.org/snapshot/sqlite-snapshot-202010201440.tar.gz

tar xvfz sqlite-snapshot-202010201440.tar.gz

cd sqlite-snapshot-202010201440

./configure

make

Build sqlite command


gcc shell.c sqlite3.c -lpthread -ldl

Build libsqlite


gcc -lpthread -ldl -shared -o libsqlite3.so.0 -fPIC sqlite3.c

Link crystal program agains specific libsqlite version


ls libsqlite3.so.0

crystal build --link-flags -L./ --link-flags -lsqlite3 src/db_test.cr

List links


ldd db_test

Specify library path at runtime


LD_LIBRARY_PATH=./ ./db_test

See sqlite version used


DB_URI = "sqlite3://./db_test.db"

DB.open DB_URI do |db|

db_version = db.scalar "select sqlite_version();"

puts "SQLite #{db_version}"

end

Better way?


# Crystal runs this to determine lib path

pkg-config --libs sqlite3

# Alter pkg-config path to include custom path

PKG_CONFIG_PATH="/usr/local/opt/sqlite/lib/pkgconfig" pkg-config --libs sqlite3`

-L/usr/local/Cellar/sqlite/3.33.0/lib -lsqlite3

PKG_CONFIG_PATH="/usr/local/opt/sqlite/lib/pkgconfig" crystal build src/db_test.cr
2 Likes
8

You should be able to pass just --link-flags ./libsqlite3.so. No need to hassle with pkg-config.

2 Likes