[kratos] SQLite Woes

[shine]

The documentation mentions kratos supporting SQLite. I have a reference application that I want kratos to integrate with.

I ran the default docker image, built from source but couldn’t get kratos to start. It kept complaining Unable to connect to database, retrying. error="unsupported dialect 'sqlite3'".

Then I went to the Github Releases page which indicated that there were indeed 2 assets with one being for sqlite. I tried with that binary and kratos at least started ( I don’t want to rant about the amount of configuration changes that I had to do between the 2 versions - the released binary and my built from source binary; I understand that this is alpha / sandbox software ). The binary specified with sqlite starts up with the right URI configurations, but doesn’t honor the DSN value from the configuration ( No database file created. I can’t skip the configuration either ). The other “normal” (pre-)release binary doesn’t work either.

My questions are as follows :

1. Why are there 2 separate binaries?
2. Why one specific for sqlite and not the other database drivers?
3. Did I miss seeing some documentation somewhere?
   I couldn’t find any topics here, issues on Github or any mention in the documentation about this caveat which I have already spent many hours trying to figure out.
4. Why is the normal application trying to use the sqlite3 ‘dialect’?
   I tried replacing the only valid reference I could find ( though it didn’t seem that was where the error message originated from ).
5. Why does the sqlite binary get it right?
6. Can the 2 binaries be merged?
7. If I wanted to build the sqlite binary from source ( for the sake of the settings_ui and profile_ui ), how would I go about achieving it?

[hackerman]

SQLite needs CGO which is why we ship it as a separate binary because it requires libmusl or libc on the system which isn’t the case in some docker environments. It’s also extremely difficult to get cross-compiling working.

Without SQLite, you can compile ORY Kratos for arm64 or whatever you need. There is currently no SQLite library for Go that does not need CGO.

Apart from that difference, the two binaries are exactly the same - so one supports SQLite and the other doesn’t. Both support all other SQL databases.

If you build this from source you need to use Go tags -tags sqlite. You can find an example of that here: https://github.com/ory/kratos/#short-tests

@shine

If I wanted to build the sqlite binary from source ( for the sake of the settings_ui and profile_ui ), how would I go about achieving it?

Unless you’re checking out an existing release (git tag) and are not planning on just running the tests, just don’t do it. master is not stable because the examples are not up to date. The best way of getting started is not to compile this yourself and start from scratch but use the very well documented quickstart guide that we spent a ton of time on writing

Quickstart | ORY Kratos

[shine]

@hackerman

The best way of getting started is not to compile this yourself and start from scratch but use the very well documented quickstart guide that we spent a ton of time on writing

The quickstart is where I started, but it doesn’t have a database right? My boilerplate app is not as complicated as the quickstart - with the mailserver, reverse proxy et al. It just has a simple backend connected to the SQLite database and the SecureApp for the frontend. My intention was to get the authentication happening with the bare minimum.

I built from source after docker-compose failed on me. I thought I was doing something wrong with docker ( because I have a very weird and dumb docker environment ).

I didn’t think of the release binaries until after the “built from source” binary ( I mean, building go apps from source is such a breeze oneliner - go get github.com/<org>/<repo> ) also failed on me. And that’s where I saw the separate sqlite binaries.

Your explanation clarified the scene for me. Thank you for the explanation. And it makes sense. Especially, in the docker environment. I don’t know if it is my place to suggest that this point needs to be documented somewhere so that people don’t waste time trying to achieve this edge case scenario.

Also, aside, it would be a lot appreciated if the configuration document could indicate the required and optional fields. I figured them out only by trial and error. I started with an empty file and started adding the fields as kratos threw errors at me.

[hackerman]

@shine

The quickstart is where I started, but it doesn’t have a database right? My boilerplate app is not as complicated as the quickstart - with the mailserver, reverse proxy et al. It just has a simple backend connected to the SQLite database and the SecureApp for the frontend. My intention was to get the authentication happening with the bare minimum.

The next release will greatly reduce the complexity here. It’s still has a lot of work left to be done but it will make this much easier!

@shine

I built from source after docker-compose failed on me. I thought I was doing something wrong with docker ( because I have a very weird and dumb docker environment ).

Did it fail you with an error or due to complexity or because it’s not using PostgreSQL but instead SQLite?

In both cases the new patches will help because we will ship different quickstart configs for all databases and also reduce complexity of the deployment by removing Oathkeeper.

@shine

I didn’t think of the release binaries until after the “built from source” binary ( I mean, building go apps from source is such a breeze oneliner - go get github.com/<org>/<repo> ) also failed on me. And that’s where I saw the separate sqlite binaries.

Yeah unfortunately, like with many bigger projects, this isn’t that straight forward. There are two things required - one is packing assets (e.g. JSON Schemas) into the binary using packr2 und then installing the binary go install -tags sqlite .. I think we have a makefile for this make install but I’m not sure if it adds the sqlite tag or not.

@shine

Also, aside, it would be a lot appreciated if the configuration document could indicate the required and optional fields. I figured them out only by trial and error. I started with an empty file and started adding the fields as kratos threw errors at me.

Yes, this has been requested before and makes absolute sense. It looks like our code-generation for the docs doesn’t solve this yet but that should definitely be added. We’re also trying to figure out how to reduce the boilerplate config needed - especially around the different UIs because it’s quite annoying to set all of the URLs.

[shine]

@hackerman

Did it fail you with an error or due to complexity or because it’s not using PostgreSQL but instead SQLite?

I guess it was because of the incompatible databases. I got the same error - Unable to connect to database, retrying. error="unsupported dialect 'sqlite3'" from both the docker container and the binary build.

The complexity was something I decided to work around. It’s good to know that you’re working towards simplifying it.

@hackerman

Yeah unfortunately, like with many bigger projects, this isn’t that straight forward. There are two things required - one is packing assets (e.g. JSON Schemas) into the binary using packr2 und then installing the binary go install -tags sqlite . . I think we have a makefile for this make install but I’m not sure if it adds the sqlite tag or not.

I did take a look at the Makefile, but since I got a binary out with just go install, I just arrogantly used it. Though I did notice that it didn’t have the version information ( even the release binaries don’t have it; is that intentional because it is still in alpha? ) unlike the other ory products.
No, make install also doesn’t add the -tags sqlite

@hackerman

We’re also trying to figure out how to reduce the boilerplate config needed - especially around the different UIs because it’s quite annoying to set all of the URLs.

True, I agree. Even I kind of felt frustrated that I had to set URLs for UIs that I didn’t have like mfa_ui. Also, is it mandatory to have an SMTP courier for the basic usage? Does that seem a bit too far-fetched? Maybe, it could be required only if we set up the account recovery flow?

[hackerman]

@shine

True, I agree. Even I kind of felt frustrated that I had to set URLs for UIs that I didn’t have like mfa_ui . Also, is it mandatory to have an SMTP courier for the basic usage? Does that seem a bit too far-fetched? Maybe, it could be required only if we set up the account recovery flow?

No, most of those things are not required to get a simple login working. We’ll improve the validation as the project matures and also add default endpoints (e.g. for mfa ui) that point to the documentation so that it’s easier to get started.

As with all projects, we’re first trying to solve the hard things and improving the frustrating parts based on feedback like this

[hackerman]

@shine

I did take a look at the Makefile , but since I got a binary out with just go install , I just arrogantly used it. Though I did notice that it didn’t have the version information ( even the release binaries don’t have it; is that intentional because it is still in alpha? ) unlike the other ory products.
No, make install also doesn’t add the -tags sqlite

Fixed on master