Author: Usman Anwer (@uanwer), Developer Ecosystem and Platform Team

How do you ship over 200 Data Access APIs that work across half a dozen different types of devices and have a 100% test coverage, but with only a couple of days of engineering effort? It's simple, you ship SQLite. Over the last year, Windows engineers have worked closely with the amazing SQLite Core Development team led by Richard Hipp to bring SQLite, the popular open source database engine, to Windows 10. SQLite is a unique case: it is an open source, externally developed software that is used by core system components, and our flagship apps like Cortana and Skype. As of our latest release of Windows, it also supported in our developer SDK, which historically has only included Microsoft developed APIs.

Here's the story of how we got here.

It started with One Microsoft

Creating a "One Windows" platform was a central design goal for Windows 10 effort. It meant converging the different operating systems across PC, Phone, and Xbox into a single core that could run across all of these existing devices, and also new ones like IoT, the Surface Hub, and Hololens. The state store is a mission critical part of this OS that caches important package, installation, and state information for all apps.

Up till Windows 8.1, we had a different state store for Desktop, Phone, and Xbox. Moreover, each of these was based on different combinations of database technologies like the registry, the Extensible Storage Engine, and XML files. To have a single state store for all our devices, we needed a unified data layer ideally built on a single database technology. The state store is a read heavy, write low database, and SQLite came out to be the most appropriate out of any of the other options used in previous versions. It also came out to be more memory efficient for the size of data we had to store, making it pretty ideal for resource constrained IoT devices. With its extensive, thorough documentation, and a vibrant mailing list it also seemed great for the aggressive, iterative development cycles we were looking at. The state store was going to use SQLite.

The case to make SQLite a system feature

Once we settled on using SQLite, we did a cursory search of then Windows 10 codebase and found that several other teams were also checking SQLite binaries or source files. Given there is both administrative and engineering overhead to having multiple copies of a single library in the codebase, this was an opportunity to centralize SQLite so it could be managed and consumed efficiently across various Microsoft products.

There were some performance improvements we could get out of this centralization. If Universal apps were using SQLite, and "always running" components like the state store were using SQLite, they could share a copy of it to save some precious runtime memory. For apps that load SQLite at launch, it meant they could launch faster given that the code pages for SQLite would already be in memory for use by the state store by the time the user can launch an app. Having a single binary could also let us fix a security vulnerability or a critical bug promptly, without spending time hunting down copies of the library across the codebase and depending on the owners to fix it.

More or less, it made sense to treat SQLite just like any other feature in the core operating system.

Not made here, but just like our own

Making SQLite a system feature meant we needed to think through all the engineering workflows, policies, and practices we typically use to support system features in context of SQLite's open source and external nature. Some questions we wanted to answer:

• How do we run automation tests on SQLite?
• What do we do if there's a critical bug in SQLite?
• How do we ensure a rapid response to a security vulnerability?
• How frequently do we update our SQLite?
• How do we test a new version for backward compatibility?
• What do we do if a new version regresses existing app?
• How do we contribute code and ideas back to SQLite?

As we went around answering these questions, we learnt that there were many layers to onboarding open source software into a product like Windows. Not all of these were technical. To be successful, the project needed to work for the product and the people. A diagram from Tom and David Kelley's Creative Confidence that maps well to this problem.

To ensure if SQLite was suitable for use in Windows, we had to answer a bunch of business questions: How does this help the product? How cost effective is it? It was pretty clear from the beginning that by banking on SQLite, we could save a whole lot of developer time and resources, and deliver faster without compromising on quality.

We had to answer a bunch of technical questions: How's the performance on Windows? Will we be able to respond to critical bugs fast? How fast? How stable are the APIs? What's the threat model? For SQLite these were easy to answer. The SQLite development team has a fantastic - actually - mindboggling customer support. We rave about it all the time. It also has an exemplar commitment to API backward compatibility, similar to what we strive for in Windows.

We also had to answer some people questions: Are people enthusiastic about using this piece of software and contributing back to it? Are they open and willing to develop the relationships with the community of the project? Are they willing to become a go-to resource for anyone else who wants to use this? SQLite had lots of support across multiple teams. Many engineers wanted it to be in the platform for various reasons. A couple were already dabbling with its internals and developing solutions around it on their own time.

Based on this lens, we put together a three step Assess - Prepare - Onboard framework to give a head start to any team or an engineer that might be looking into adding open source libraries to the areas of the platform they own. We also started a grass roots effort to spread the word about new open source policies, legal and engineering resources across the Windows and Devices Group.

SQLite in the SDK

After shipping SQLite as a system component in July, we wanted to include it in our SDK for November. With more than 20,000 Windows Apps and more than half of our top apps using SQLite, it made sense to just make expose the system SQLite to app developers. We saw many upsides to this for our developer experience and platform health:

• A simplified experience for using SQLite in Windows Apps where developers will just need to reference a header instead of downloading extensions or compiling their own SQLite.
• A reduced AppX package size since developers will not need to package the library with their app.
• Better app launch times given SQLite is likely to already in memory for use by newly launched app if system components and a lot of high usage apps are using it.
• Runtime memory saving if a lot of active apps are using a shared binary.
• Automatic upgrades for developers who can now rely on Windows to update the platform SQLite to more performant versions.

Adding SQLite to the SDK helped us quickly introduce quality APIs for the Universal Windows Platform, while giving us the foundations to build a richer data access story over future releases.

We're just getting started

The SQLite in Windows initiative is just one of many open source initiatives we have going on around the company. In the months ahead, we're looking at strengthening our relationships with open source communities, developing new tooling, informing policies, and ultimately making it super easy to contribute to and use open source in our products whenever it is desirable, feasible, and viable. In doing so, we like to reinforce amongst ourselves, we are not just shipping new features - we are shipping a new mindset, we are creating a new Microsoft.

Would you like to work with Microsoft engineering teams working on interesting challenges like those described above? Here are some roles on the operating systems engineering teams, and you can find information about working at Microsoft or search for other openings here. Come join the team!