Support other sql dialects

[simolus3]

Current status of other dialects: We have initial support for them, but drift puts its main focus on sqlite3 and some features may not fully work with other database systems.

You can use drift with postgres or MariaDB by adding a git dependency to the respective packages. These packages aren't stable, but all kind of feedback is much appreciated!

---

I am looking for a way to have my database running on a server and use moor to not deal with plain sql. For my purposes, I do not need my the auto updating part. I believe it is currently not possible to connect to remote dbs, do you plan to make this possible in the future?

Originally posted by @jwallat in https://github.com/simolus3/moor/issues/32#issuecomment-512991753

Connecting to a server DBMS should be fairly straightforward by implementing a custom QueryExecutor. However, the generated queries are designed to work with sqlite, so we might have to modify that code to work with other database engines. @jwallat is there a specific database engine you have in mind? I'd take a look at that first then.

[jwallat]

Thanks for the fast response. I am currently using MySQL. I guess I could switch to a sqlite db, but from a short search it seems not to be recommend to remotely connect to sqlite dbs. Will do some more research if a switch would be possible in the next few days.

---

From: Simon Binder notifications@github.com Sent: Friday, July 19, 2019 12:16:02 PM To: simolus3/moor moor@noreply.github.com Cc: Vheyh jwallat@hotmail.de; Mention mention@noreply.github.com Subject: [simolus3/moor] Support other databases (#79)

I am looking for a way to have my database running on a server and use moor to not deal with plain sql. For my purposes, I do not need my the auto updating part. I believe it is currently not possible to connect to remote dbs, do you plan to make this possible in the future?

Originally posted by @jwallathttps://github.com/jwallat in #32 (comment)https://github.com/simolus3/moor/issues/32#issuecomment-512991753

Connecting to a server DBMS should be fairly straightforward by implementing a custom QueryExecutor. However, the generated queries are designed to work with sqlite, so we might have to modify that code to work with other database engines. @jwallathttps://github.com/jwallat is there a specific database engine you have in mind? I'd take a look at that first then.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/simolus3/moor/issues/79?email_source=notifications&email_token=AF4H6ZQMLD2453NIRRCE3NDQAGO7FA5CNFSM4IFE7MF2YY3PNVWWK3TUL52HS4DFUVEXG43VMWVGG33NNVSW45C7NFSM4HAHZP2A, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AF4H6ZVG2ODKTK6GVQTYYLLQAGO7FANCNFSM4IFE7MFQ.

[simolus3]

Yeah, sqlite on a server doesn't really scale, MariaDb/MySQL will work much better.

I just managed to connect moor to a MySql server here, but it already throws at the first statement because of a syntax error. The generated statements are designed to work with sqlite at the moment, so that was expected :D I'll try to add proper support for MySQL soon.

[Sacchid]

PostgreSQL might be more easy as PostgreSQL has a dart package (library) to connect and query PostgreSQL database

[simolus3]

Good to know, thanks. I'll take a look at it. We use sqljockey for MySQL, which seems to work good so far. The main problem is that database engines all have a slightly different interpretation of sql, so we need to adapt the generation code to reflect that. Swapping out the underlying driver is rather easy.

[Sacchid]

Ok, I just shared that as Aqueduct.io has PostgreSQL based Statically-typed ORM and Database Migration and as you might know is a very stable open source REST framework written in dart.

[jaumard]

I would be interested in PostgreSQL support :) Any tips (steps) on how to start to provide such feature ?

[simolus3]

Any tips (steps) on how to start to provide such feature ?

There are different aspects to this:

1. Moor needs to be able to send statements to a database. For this, you can implement a DatabaseDelegate (defined in package:moor/backends.dart). It has methods like runInsert, runUpdate, runSelect, etc. Those will be called by moor to support the database. A good starting point for this could be the implementation of the FlutterQueryExecutor here. If you know a good Postgres library for Dart, writing a DatabaseDelegate for moor hopefully comes down to just wrapping that library.
2. Support it when generating queries from the Dart DSL: Technically, it should be possible to use moor with postgres after a DatabaseDelegate has been written. In practice, that's probably not going to work if there are differences between PostgresSQL and sqlite syntax. Those need to be handled in moor.

I'm not sure if and how we should support different dialects in .moor files yet. Maybe we can rewrite them to work on different engines, but I didn't put a lot of thought into that so far.

The majority of moor users use Flutter or some other client-side Dart, so supporting different engines isn't a big priority for me. But if you're interested in working on a DatabaseDelegate for Postgres I'll support you as best as I can. If you run into problems because of different syntax in Postgres and sqlite, I'll gladly help resolve them with changes to moor.

[westito]

I would be interested in PostgreSQL support :) Any tips (steps) on how to start to provide such feature ?

Postgres PR: https://github.com/simolus3/moor/pull/1507

[PlugFox]

We need a good ORM for postgres on dart) Thanks you @westito

[gisborne]

FYI SQLite works just fine on a server, . So you might want to explore support for BedrockDB.

Presumably, it would be fairly easy.

Although I’m pretty excited about Postgres support.

[westito]

According to Bedrock documentation it is compatible with MySQL. A mysql dialect may fit for this RDBM. However, the current implementation of Drift makes hard to create dialects (this Postgres dialect also has many hacks to get it work). Drift is under refactor right now to make it more generic and handle dialects more easily. Be patient.

[odunboye]

Is there MySQL support?

[vanlooverenkoen]

I am using SQLite on server side at the moment. But as it was already mentioned. This will not scale very good. But it does work if for certain applications.

[gisborne]

Just offering some advice: SQLite is absolutely suitable for a lot of quite heavy-use sites.

Expensify even built a traditional database server based on SQLite, supporting redundancy and failover and all sorts of goodies.

I would still prefer Postgres because of other features it has. But SQLite is efficient even under heavy load.

[vanlooverenkoen]

What would you consider heavy load?

[gisborne]

Expensify is a very popular, heavy-use app, and it has scaled just fine with SQLite.

SQLite can handle just about any load; it's a question of whether its other features suit the application.

Traditionally embedded SQLite doesn't support failover and such, and requires pretty much one application server that holds the database. That is a limitation.

But either sharding or Bedrock DB or some plugins that are available get around that.

I would much rather use SQLite than MySQL or SQL Server. I would somewhat prefer to use Postgres because it has the best SQL features, but SQLite is pretty good.

[egyleader]

+1 voting for postgres support

[f-person]

@egyleader, it's been already implemented in https://github.com/simolus3/drift/pull/1507.

@simolus3 i wonder if this issue should be closed? Or can the initial comment on the issue be updated to track the progress/plans for any other future DB servers?

[simolus3]

I think the issue should stay open since we don't have stable support for other dialects, but I've updated the original issue to mention the current state of the ongoing work.