So what is the problem with sqlite?

ecto
phoenix
#1

I seems like sqlite is kind of a no-go in phoenixworld - why is this the case? Certainly for development or smaller apps sqlite does very well with all kinds of programming languages - so what’s the issue with elixir/phoenix?
Thanks!

2 Likes

How to configure phoenix for sqlite
#2

I think most choose PostgreSQL or MariaDB for production and it is easier to run the same database both in production and in development so you can make use of the DB’s features (like PostgreSQL’s jsonb field).

2 Likes

#3

Thanks for your answer, yes I know mysql and postgresql.
I just wanted to know if there are some known problems with sqlite - I know it is not recommended for production, but still there are many applications out there using sqlite for production (e.g. on android phones).
I guess that concurrency issues may kill sqlite usage?
Or is there anything inherently broken with elixir and sqlite?
Thanks again!

0 Likes

#4

I am interested in this topic as well, and comments from people with experiences. For example, this guy sticks sqlite on top of Erlang/Genserver.

So, I think it should work and maybe a Riak alternative.

0 Likes

#5

They have a very unique use case, so it’s hard to generalize from it. To be a viable option it would need a lot more users and contributors (ActorDB not SQLite).

1 Like

#6

Quite simply, you should use the same database in development and testing that you use in production.

1 Like

#7

I think that for embedded platforms such as phones, Raspberry Pi’s, other microcontrollers etc. it is probably more worthwhile to use ETS (Erlang Term Storage), as it is built-in in the language, and therefore a lot faster to communicate with it. ETS was also built from the get-go with concurrency in mind, and can also be used to create RAM-based memory stores, which makes it a lot faster (of course, at the cost of using more RAM)

On the other hand, ETS takes some getting used to, because it does not use a conventional Structured Query Language syntax, but rather a pattern-match esque syntax.


And while I do not know how good (or bad) SQLite will work with concurrent applications, I do know that it really helps to have the same database locally as on a production server. In the past, we’ve had rounding troubles with our decimals when going to the test and production environments, because SQLite doesn’t care about the precision of DECIMALs, while MySQL and PostgreSQL will. And the same is true for STRING vs TEXT, which is the same in SQLite, which means that MySQL might throw errors in production when suddenly things don’t fit in something you thought could be a string.

2 Likes

#8

Becase SQLite isn’t trendy enough.
I use it everyday for most of my applications because the deployment & configuration cost is null and the performance impact isn’t visible, compared to PostgreSQL

4 Likes

#9

Largely that ETS/DETS/Mnesia are already built in without requiring an external dependency. I can’t think of a use case for SQLite that isn’t addressed by those already (aside from existing familiarity).

1 Like

#10

This is not true :slight_smile:

We don’t pick features based on their trendiness. If you don’t believe me, see our choice of brunch instead of webpack :stuck_out_tongue:. Postgres is the default db adapter for new apps simply because in my experience it’s the defacto choice for prod environments, both for my client’s apps, and what I use personally, along with my peers. That said, the Ecto adapter ecosystem has first class support for other adapters, such as mysql, mongoldb, and sqlite that can be used with a single flag to the generator. The issue on the sqlite end is the ecto sqlite adapter has not yet been updated to Ecto 2.0, and the phoenix-core team can’t be responsible to maintain every db adapter that exists. I encourage those in the community who want the best sqlite experience to become contributors on that project so that you can run mix phoenix.new app --database sqlite and be up and running as you wish.

17 Likes

#11

Some support is on offer from the Ecto team for anyone updating a driver: https://github.com/elixir-ecto/ecto/issues/1215

0 Likes

#12

Do you guys know if there is there any current effort to port sqlite to ecto 2.0 and to maintain the repository?

I’m asking because I’m interested in working on this issue, but I don’t want to cross anyone’s work.

0 Likes

#13

you can check this branch →https://github.com/obmarg/sqlite_ecto/tree/feature/ecto-2.0

0 Likes

#14

The ecto-2.0 branch hasn’t been updated in a long while. Anyone interested still interested in working on this?

1 Like

#15

if you mean “mongodb” thats simply wrong. And such statements maybe the reason why this is not changing. :slight_smile:

1 Like

#16

If any particular database adapter is lacking then it is up to the community to step up and support it. The phoenix and ecto core team cannot be responsible for creating and maintaining every database adapter. If you feel the situation is not changing it is because no one has stepped up to own the sqlite adapter.

2 Likes

#17

that’s sure. I only commented, because you said, that there is first class support for mongodb. And that is simply not true. We are probably moving away from phoenix and elixir, as there is no first class support for mongodb and – as we are new to elixir – we are not able to add the missing parts.

but it was not my intention to imply, that these drivers have to be supplied from the ecto team.

kind regards

1 Like

#18

Hello,
I am trying to learn Elixir by developing a web app and I wanted to use SQLite as my backend database. I don’t expect the users to exceed its limits any time soon so it would be a good starting point!
I landed on this post saying that the driver adapter needs some love to support Ecto 2.0, so I believe it would be an amazing opportunity to learn the language more in-depth and also support my use-case and others wanting to use SQLite.

Is there any procedure or best-practices on these kind of adapters, or should I just dive into one of the two existing repositories and try to make it work with Ecto 2.0 ?

Existing repos:


Any hint, tip and references would be awesome to help me start contributing to Elixir;p

1 Like

#19

One thing I don’t like about the “PG is what you use in prod!!” argument is that not every project is a for-money, “real world” project. Some of us just want to make apps for fun and SQLite is just easier in every way. I love PG but don’t need it for everything. Sometimes I even start a project with a JSON file as my database :slight_smile:

1 Like

#20

@lambrospetrou

2 Likes