I want to use Microsoft SQL Server(MSSQL) as a database

Frappe Framework
1

since Frappe supports two databases, MariaDB and PostgreSQL, is it possible to set up Microsoft SQL Server (MSSQL) with Frappe

2

Hello,

Actually Frappe is design to use MarriaDb (and “experimental” with PgSQL, even if with version 15 Frappe will propably be compatible with PgSQL but may be not ERPNext).

3

Dear Henry, please listen to this joke. ChatGPT suggests to me, ‘Change the database type to MSSQL, the server port, and provide the database name in site_config.json, and it will be connected.’ Henry, I seek clarity on this matter. Some changes in site_config.json will enable MSSQL integration with Frappe. As a senior developer, what are your thoughts on this matter? There are many underlying principles behind the setup of MariaDB and PostgreSQL. Is it truly feasible to change the database type, name, and server port?

4

Hi @Masaud_noor:

Additionally to connection methods, etc … there are hundreds (or thousands if ERPNext is involved …) of queries to review . IMHO … is crazy.

Anyway … do you need all the data in MSSQL? Or just get some data from MSSQL database?
In this case, Virtual Doctypes are your friends :slight_smile:
https://frappeframework.com/docs/user/en/basics/doctypes/virtual-doctype

Hope this helps.

1 Like
5

Please… do not ask this to ChatGPT. It will always try to please you with the simplest (and often wrong) answer.
Or, well, try it for real, and then when, you’ll get lots of error, ask again to ChatGGT to solve your bugs with the errors codes you’ll get, it can be funny, but not productive.

If I ask it how to made a flying car, it will answer me somethings also…

There is somethings with open source project, and you know it, as you are a developer, it’s call documentation (or alternatively directly read the code, more time consuming).

1 Like
6

When we create a new site in Frappe, the default database setup is MariaDB. However, our requirement is to set up MSSQL instead of MariaDB when creating a new site, with no connection to MSSQL

7

Not a developer, but I guess the chances are slim that MSSQL will be offered as an option. ERPNext is an open source program and can be used for free. Using a closed source database that costs a lot of money doesn’t make a lot of sense to work with.

While MSSQL certainly is a good database, if that database is a must have, you might find other solutions better fitting then.

8

The comments are correct, there’s no way you can use SQL Server as your primary Frappe database. The only two options are MariaDB and PostgreSQL (although the latter does still have some glitches).

That’s only the Frappe framework. As for apps like ERPNext that run on Frappe, it depends how they are written. ERPNext is written with thousands of MariaDB-specific queries and lines of code, and cannot run on PostgreSQL.

It’s possible using virtual doctypes that you can connect certain custom documents to another database, but I don’t have any experience with that.

That’s also discussion of moving to a new query interface/object model to make Frappe & ERPNext more database-agnostic, but I’m not sure how much progress has been made in that direction.

The requirement may be to use MSSQL for Frappe/ERPNext, but unfortunately, it’s not a requirement that can be met.

9

Only with a significant investment of time and energy:

  1. First, whoever attempts this should already be an expert in Frappe and ERPNext. That expertise is necessary to even understand what needs to happen next…

  2. Next, write the necessary libraries/adapters to make the framework communicate with MSSQL. Write your own equivalent for building DDL statements, index creation, etc.

  3. Review all the existing Frappe and ERPNext code. Every query, statement, all the ORM code, and anything else that relates to SQL? It may need to be edited.

    While you’re in there, you have to solve some big challenges. For example, the way MSSQL handles nested Commits and Transactions is very different than how MySQL/MariaDB works.

  4. Now solve tertiary challenges: like teaching it how to perform SQL backups/restores.

  5. Then you must test, test again, and test some more.

  6. Finally, when it’s all working, you have to maintain your custom forks forever. Whenever new version of Frappe + ERPNext are released (v16, v17, etc.) you must personally merge and upgrade your custom code.

Can it be done? Yes. Nothing is impossible.
Is this practical? No, not really.

If this was me? I would just run ERPNext using either MySQL or Postgres. And then, if I really needed that data in Microsoft SQL Server? I would write a separate tool to replicate/sync the data between the 2 database platforms. That would be a much, much easier project than editing Frappe/ERPNext directly.

2 Likes
10

Note the comment from CEO, FRAPPE at the end of the blog post

2 Likes
11

Most probably tests will fail. So instead of reviewing the existing code, test will tell the errors.

12

Replicate if you really need MSSQL Server for reporting or other purposes. Being completely open source you can guess why Frappe uses Mariadb instead of Mysql. If you really need to stick with MSSQL Server, there are other Low-code/No-code Model-Driven frameworks out there that works well with Microsoft’s ecosystem like ABP Framework or Devexpress XAF.